Validate database objects after migrating from SAP ASE to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL

As part of this blog series, we previously covered database object validation for migrations from SAP ASE (previously known as Sybase ASE) as source to target databases like Amazon Relational Database Service(Amazon RDS) for SQL Server, Amazon RDS for MySQL, Amazon RDS for MariaDB, or Amazon Aurora MySQL-Compatible Edition. In the final post of this series, we focus on validating the migration from SAP ASE to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition.

For this schema migration, you can use AWS Schema Conversion Tool (AWS SCT) as we did for the previous post except change the target database to Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible.

Prerequisites

The following prerequisites are required to perform the post migration validation provided in this guide:

• A SAP ASE source database
• An Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-Compatible target database post migration
• An Amazon Elastic Compute Cloud (Amazon EC2) instance or a SQL client with necessary permissions to connect to both source and target databases and run the validation SQLs.
• A database user with public access to the primary database on the source SAP ASE database.
• The database user must have SELECT permissions on the following system tables:
  • [source_db].dbo.sysobjects
  • [source_db].dbo.syscolumns
  • [source_db].dbo.sysconstraints
  • [source_db].dbo.syspartitionkeys
  • [source_db].dbo.sysindexes
  • [source_db].dbo.sysreferences
  • [source_db].dbo.sysusers
  • master.dbo.spt_values
• The database user with read privileges to the target database. The user must have permissions on the following schema:
  • information_schema
  • pg_catalog

Database object identification and validation

To perform database object validation, you need to understand the different types of database objects to consider.

The following sections include database objects you can compare between SAP ASE and the corresponding PostgreSQL database. Schema validation also reduces issues during the last stages of migration.

• Schemas
• Tables
• Views
• Functions
• Stored procedures
• Indexes
• Triggers
• Constraints
  • Primary key constraints
  • Foreign key constraints
  • Check constraints

In the following sections, we look into each of these object types and validate them using their corresponding SQL queries to help us identify any mismatch in the migrated schema objects.

Schemas

You can validate the schema at the source (SAP ASE) and target (PostgreSQL) using the following SQL queries.

Use the following SQL query for SAP ASE:

select distinct su.name as SchemaName
from sysusers su
inner join sysobjects so
	on su.uid = so.uid
order by su.name;

The following screenshot is an example of the SAP ASE output.

The following is a similar query for PostgreSQL:

select schema_name as "SchemaName"
from information_schema.schemata
where schema_name not in ('pg_catalog','public', 'information_schema','aws_sapase_ext')
      and (schema_name not like 'pg_toast%'
      and schema_name not like 'pg_temp%'
      and schema_name not like '%pg_toast_temp%')
order by schema_name;

You get the following output.

You can also use the following alternate query for PostgreSQL:

select nspname as "SchemaName"
from pg_catalog.pg_namespace
where nspname not in ('pg_catalog','public','information_schema','aws_sapase_ext')
      and (nspname not like 'pg_toast%'
      and nspname not like 'pg_temp%'
      and nspname not like '%pg_toast_temp%')
order by nspname;

Verify the schema results for any possible differences between the source and target database using these queries. If you find any differences, identify the root cause or take guidance from the AWS SCT logs and fix them accordingly. In this example, the output is as expected.

Extension packs

When you convert your database schema using AWS SCT, it may add additional schemas to your target database, known as extension packs. When migrating your database from SAP ASE to Amazon RDS for PostgreSQL or Aurora PostgreSQL, AWS SCT creates an extension pack named aws_sapase_ext, as seen in the following example.

The following SQL query is for PostgreSQL:

select schema_name
from INFORMATION_SCHEMA.schemata
where SCHEMA_NAME = 'aws_sapase_ext'
order by schema_name;

You get the following output.

We have excluded the extension pack schema in all other validation queries because it’s captured in this section.

Tables

In this section, we discuss tables under each schema (or owner) and detailed information for each table.

Tables under each owner

The migration tool (in this case AWS SCT) converts the source SAP ASE tables to the equivalent PostgreSQL target tables with the same or equivalent data types as per the target database syntax. To ensure that all the tables were migrated successfully, you can use the following SQL queries, which return the schema and count of all the underlying tables.

Use the following SQL query for SAP ASE:

select user_name(uid) as SchemaName,
count(name) TableCount
from sysobjects
where type='U'
group by uid
order by user_name(uid);

You can see the sample output in the following screenshot.

The following is a similar query for PostgreSQL:

select schemaname as "SchemaName",count(tablename) as "TableCount"
from pg_tables
where schemaname not in ('aws_sapase_ext','public','information_schema','pg_catalog','pg_toast','pg_temp')
group by schemaname;

You get the following output.

You can also use the following alternate query for PostgreSQL:

select table_schema as "SchemaName", Count(table_name) AS "TableCount"
from information_schema.tables
where table_type ='BASE TABLE'
      and table_catalog = 'pubs2' -- add your database name
      and table_schema not in ('aws_sapase_ext','public','information_schema','pg_catalog')
group by table_schema;

Verify the table count for any differences between the source and target database using these queries. If you find any differences, identify the root cause or take guidance from the AWS SCT logs and fix them accordingly before moving ahead.

Detailed information for each table

The previous SQL just verifies if all the tables present in the source database are migrated to the target database. However, it doesn’t ensure the integrity of the data types and other table definitions. The following query helps verify a few key features of the tables for the presence of LOB columns, computed columns, partitions, and column count. You can further enhance this query to add any other table definition as per your requirement.

Use the following SQL query for SAP ASE:

select user_name(so.uid) SchemaName,so.name TableName,count(sc.name) ColumnCount,
       case when max(sc.computedcol) is not null then 'Y' else 'N' end ComputedColumn,
       case when max(sp.id) is not null then 'Y' else 'N' end PartitionTable
from sysobjects so 
inner join syscolumns sc 
      on so.id = sc.id -- to get count
left join syspartitionkeys sp 
     on so.id = sp.id -- check partitions
where so.type = 'U'
group by so.uid,so.name
order by user_name(so.uid),so.name;

You can see the sample output in the following screenshot.

The following is a similar query for PostgreSQL:

select t.table_schema as "SchemaName",t.table_name as "TableName",
        count(c.column_name) as "ColumnCount",
        max(case when c.is_generated = 'ALWAYS' then 'Y' else 'N' end) "ComputedColumn",
        max(case when pc.relkind = 'p' then 'Y' else 'N' end) "PartitionTable"
from information_schema.tables t
inner join information_schema.columns c
      on t.table_catalog = c.table_catalog
      and t.table_schema = c.table_schema
      and t.table_name = c.table_name
inner join pg_class pc
      on pc.relname = t.table_name
where t.table_type ='BASE TABLE' and t.table_catalog = 'pubs2' -- add your database name
      and t.table_schema not in ('aws_sapase_ext','public','information_schema','pg_catalog')
      and not pc.relispartition
group by t.table_schema,t.table_name
order by 1,2;

You get the following output.

Verify the table details for any possible differences between the source and target database using these queries. If you find any differences, identify the root cause or take guidance from the AWS SCT logs and fix them accordingly.

Default Column Values

The following queries give you the details of Default Column Values for both the SAP ASE and PostgreSQL databases.

Use the following SQL query for SAP ASE:

select user_name(o.uid) SchemaName,object_name(c.id) TableName,c.name ColumnName
from syscolumns c
inner join sysobjects o
      on  c.cdefault = o.id
inner join syscomments m
      on o.id = m.id
order by object_name(c.id),o.name,c.name;

You get the following output.

The following is a similar SQL query for PostgreSQL:

select col.table_schema as "SchemaName",col.table_name as "TableName",
       col.column_name as "ColumnName"
from information_schema.COLUMNS col
where col.column_default is not null
order by col.table_name,col.column_name;

You get the following output.

Verify the default value details for any possible differences between the source and target database using these queries. If you find any differences, identify the root cause or take guidance from the AWS SCT logs and fix them accordingly.

Views

The following queries give you the details of views for both the SAP ASE and PostgreSQL databases.

Use the following SQL query for SAP ASE:

select user_name(uid) SchemaName,name ViewName
from sysobjects
where type='V' and name not like 'sys%' -- to filter system views
order by user_name(uid),name;

You get the following output.

The following is a similar SQL query for PostgreSQL:

select schemaname as "SchemaName",viewname as "ViewName"
from pg_views
where schemaname not in ('information_schema','pg_catalog','public','aws_sapase_ext')
order by schemaname,viewname;

You get the following output.

You can also use the following alternate query for PostgreSQL:

select table_schema as "SchemaName",table_name as "ViewName"
from information_schema.TABLES
where table_type ='VIEW'
      and table_catalog = 'pubs2' -- add your database name
      and table_schema not in ('information_schema','pg_catalog','public','aws_sapase_ext')
order by table_schema,table_name;

Verify the results for differences in the view details with these queries. If you find any differences, identify the root cause or take guidance from the AWS SCT logs and fix them accordingly.

Functions

The following queries give you the details of functions for both the SAP ASE and PostgreSQL databases.

Use the following SQL query for SAP ASE:

select user_name(so.uid) SchemaName, so.name FunctionName
from sysobjects so
where so.type='SF' or so.type='F'
order by user_name(so.uid),so.name;

The following screenshot shows the query output.

The following is a similar SQL query for PostgreSQL:

select n.nspname AS "SchemaName", p.proname AS "FunctionName"
from pg_proc p
join pg_namespace n
     on p.pronamespace = n.oid
where n.nspname not in ('pg_catalog','information_schema','aws_sapase_ext','public')
      and p.prokind = 'f';

The following screenshot shows the query output.

Verify the function results for any possible differences between the source and target database using these queries. If you find any differences, identify the root cause or take guidance from the AWS SCT logs and fix them accordingly.

Stored procedures

The following SQL queries gives the details of stored procedures for both the source and target databases.

Use the following SQL query for SAP ASE:

select user_name(uid) SchemaName,so.name ProcedureName
from sysobjects so
where type='P' and name not like 'sys%'
order by user_name(uid),so.name;

You get the following output.

The following is a similar SQL query for PostgreSQL:

select n.nspname AS "SchemaName",p.proname AS "ProcedureName"
from pg_proc p
join pg_namespace n
     on p.pronamespace = n.oid
where n.nspname not in ('pg_catalog','information_schema','aws_sapase_ext')
      and p.prokind = 'p';

The following screenshot shows the output.

Verify procedure information for any differences with these queries. If any are present, identify the root cause or take guidance from the AWS SCT logs and fix them accordingly.

Indexes

With the following queries, you can get the list of indexes and their types in both SAP ASE and PostgreSQL databases.

Primary keys are not included in the following SQL because we validate them in the constraints section.

Use the following SQL query for SAP ASE:

select user_name(so.uid) SchemaName, so.name TableName, i.name IndexName,
       index_col(so.name, i.indid, c.colid, so.uid) ColumnName,
       case when i.status & uq.number = uq.number then 'UNIQUE' else 'NON-UNIQUE' end IndexType,
       case when i.indid = 1 and i.status & uq.number = uq.number then 'CLUSTERED,UNIQUE'
            when i.indid = 1 then 'CLUSTERED, NON-UNIQUE'
            when i.indid > 1 and i.status2 & 512 = 512 and i.status & uq.number = uq.number then 'CLUSTERED,UNIQUE' --for DOL Tables
            when i.indid > 1 and i.status2 & 512 = 512 then 'CLUSTERED, NON-UNIQUE ' -- for DOL Tables
            when i.indid > 1 and i.status & uq.number = uq.number then 'NON-CLUSTERED, UNIQUE'
            when i.indid > 1 then 'NON-CLUSTERED, NON-UNIQUE'
        end IndexDescription
from sysobjects so
inner join sysindexes i
      on so.id = i.id
inner join syscolumns c
      on c.id = so.id
inner join master.dbo.spt_values uq -- Unique index value
      on uq.type = 'I' and uq.number = 2
where so.type='U' and i.indid > 0 
      and i.status & 2048 != 2048 -- to make sure not to include declarative PK
      and index_col(so.name,i.indid,c.colid,so.uid) is not null
order by user_name(so.uid),so.name,i.name;

The following screenshot shows the output.

You can use the following SQL query to fetch index information for PostgreSQL:

select pn.nspname "SchemaName", pct.relname "TableName", 
       pci.relname "IndexName",pa.attname as "ColumnName",
       case when pi.indisunique = true then 'UNIQUE'
            else 'NON-UNIQUE'
       end "IndexType"
from pg_index pi
inner join pg_class pct
      on pct.oid = pi.indrelid
inner join pg_class pci
      on pci.oid = pi.indexrelid
inner join pg_attribute pa
      on pa.attrelid = pct.oid
      and pa.attnum = any(pi.indkey)
inner join pg_namespace pn
      on pn.oid = pct.relnamespace
where pn.nspname NOT IN ('pg_catalog','aws_sapase_ext','pg_toast')
      and pct.relkind in ('r','p') -- select ordinary tables
      and pi.indisprimary = false -- filter out primary keys
order by pct.relname,pci.relname;

You get the following output.

The SAP ASE result has an extra column (IndexDescription) categorizing the indexes as clustered or non-clustered, whereas the equivalent couldn’t be done in PostgreSQL because only non-clustered indexes get created. For this verification, the details up to the IndexType column should suffice.

Note that in PostgreSQL, the index name needs to be unique across the database, so the names in the PostgreSQL output have different index names than SAP ASE because they had to be renamed.

Verify the index details for any possible differences between the source and target database using these queries. If you find any differences, identify the root cause or take guidance from the AWS SCT logs and fix them accordingly.

Triggers

The following queries give you the details of triggers for both the SAP ASE and PostgreSQL databases.

Use the following SQL query for SAP ASE:

select user_name(so.uid) TableSchema,user_name(tr.uid) TriggerSchema,
       so.name TableName,tr.name TriggerName
from sysobjects so
inner join sysobjects tr 
      on (tr.id = so.deltrig or tr.id = so.instrig or tr.id=so.updtrig)
where so.type = 'U'
order by user_name(so.uid),so.name;

You get the following output.

The following is a similar SQL query for PostgreSQL:

select event_object_schema as "TableSchema",trigger_schema as "TriggerSchema",
       event_object_table as "TableName",trigger_name as "TriggerName"
from information_schema.TRIGGERS
where trigger_schema not in ('aws_sapase_ext','pg_catalog','public')
group by 1,2,3,4
order by event_object_table;

You get the following output.

Verify the trigger details for any possible differences between the source and target database using these queries. If you find any differences, identify the root cause or take guidance from the AWS SCT logs and fix them accordingly.

Constraints

Constraints offer the advantages of defining integrity controls in one step during the table creation process and simplifying the creation of those integrity controls. This ensures the accuracy and reliability of the data in the table. However, integrity constraints are more limited in scope and less comprehensive than indexes and triggers.

Primary key constraints

Use the following SQL query for SAP ASE:

select user_name(so.uid) SchemaName, so.name TableName,
       i.name PK_ConstraintName,index_col(so.name,i.indid,c.colid,so.uid) as ColumnName
from sysobjects so
inner join sysindexes i
      on so.id = i.id
inner join syscolumns c
      on c.id = so.id
where so.type = 'U' and i.indid > 0 and i.status & 2 = 2 -- for declarative constraints
      and i.status & 2048 = 2048 -- for primary Key
      and index_col(so.name,i.indid, c.colid,so.uid) is not null
order by user_name(so.uid),so.name,i.name;

You get the following output.

The following is a similar SQL query for PostgreSQL:

select n.nspname as "SchemaName", c.conrelid :: regclass :: VARCHAR as "TableName",
       c.conname as "PK_ConstraintName", pa.attname as "ColumnName"
from pg_constraint c
join pg_namespace n
     on n.oid = c.connamespace
inner join pg_index pi
      on c.conrelid = pi.indrelid
inner join pg_attribute pa
      on pa.attrelid = c.conrelid and pa.attnum = any(pi.indkey)
where contype = 'p' AND n.nspname not in ('aws_sapase_ext','pg_catalog','pg_toast')
      and pi.indisprimary = true and pi.indexrelid not in (select inhrelid from pg_inherits)
      and pa.attrelid not in (select inhrelid from pg_inherits)
order by n.nspname,c.conrelid,c.conname;

You get the following output.

Foreign key constraints

Use the following SQL query for SAP ASE:

select object_name(sr.tableid) TableName,
       object_name(constrid) FK_Name,object_name(sr.reftabid) Ref_TableName,
       index_col(so.name,sr.indexid,sc.colid,so.uid) as Ref_ColumnName
from sysreferences sr
inner join syscolumns sc
      on sr.reftabid = sc.id
inner join sysobjects so
      on so.id = sc.id
where index_col(so.name,sr.indexid,sc.colid,so.uid) is not null
order by sr.tableid,object_name(constrid),object_name(sr.reftabid);

You get the following output.

The following is a similar SQL query for PostgreSQL:

select tc.table_name as "TableName", tc.constraint_name as "FK_Name",
       ccu.table_name as "Ref_TableName",ccu.column_name as "Ref_ColumnName"
from information_schema.table_constraints AS tc
join information_schema.constraint_column_usage as ccu 
     on ccu.constraint_name = tc.constraint_name
right join pg_attribute att 
      on att.attname = ccu.column_name
where tc.constraint_type = 'FOREIGN KEY' and tc.constraint_catalog = 'pubs2' -- add your database name
      and att.attrelid not in (select inhrelid from pg_inherits)
group by 1,2,3,4
order by 1,2,3;

You get the following output.

Check constraints

Use the following SQL query to get a summary of check constraints on the SAP ASE database:

select user_name(o.uid) SchemaName, object_name(c.tableid) TableName,
       count(o.name) CheckConstraintCount
from sysconstraints c
inner join sysobjects o
      on c.constrid = o.id
where (o.sysstat & 15 = 7)
group by o.uid,c.tableid
order by user_name(o.uid),object_name(c.tableid),count(o.name);

You get the following output.

The following is a similar SQL query for PostgreSQL:

select tc.constraint_schema as "SchemaName",tc.table_name as "TableName",
        count(tc.constraint_name) as "CheckConstraintCount"
from information_schema.table_constraints as tc
join information_schema.constraint_column_usage as ccu
     on ccu.constraint_name = tc.constraint_name
where tc.constraint_type = 'CHECK' and tc.constraint_catalog = 'pubs2' -- add your database name
group by tc.constraint_schema, tc.table_name
order by 1,2,3;

You get the following output.

Conclusion

In this post, we walked through validation of different database objects after migration from SAP ASE 15.7+ to PostgreSQL. You can use this post as a reference guide to perform object validation while migrating from an SAP ASE database to a PostgreSQL database.

If you have any comments or questions, leave them in the comments section.

About the Author

Amit Kumar is a Database Consultant with AWS Professional Services based out of Hyderabad, India. With good knowledge on relational databases adding hands-on in homogeneous and heterogeneous database migrations and optimization in AWS cloud.

Nishad Mankar is a Database Consultant with AWS Professional Services. He helps customers to migrate and modernize their databases on AWS Cloud.

Pavithra Balasubramanian is a Database Consultant with Amazon Web Services in India. She is passionate about helping customers in their cloud adoption journey with a focus on legacy database migrations to AWS cloud.