AWS Database Blog

Validate database objects after migrating from SAP ASE to Amazon RDS for MySQL, Amazon RDS for MariaDB, or Amazon Aurora MySQL

In this post, we focus on database object validation for the heterogenous migration from SAP ASE to Amazon Relational Database Service (Amazon RDS) for MySQL, Amazon RDS for MariaDB, or Amazon Aurora MySQL-Compatible Edition.

For schema conversion and migration, you can use AWS Schema Conversion Tool (AWS SCT). AWS SCT helps convert your database schema from the source database into a format compatible with your preferred target database. If the schema from your source database can’t be converted automatically by any means, AWS SCT provides helpful guidance on how to address the challenges to create an equivalent schema for your target.

Because this is a semi-automated process, post-migration schema validation plays a vital role in ensuring that all database objects are migrated successfully as intended.

Prerequisites

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

  • A SAP ASE source database.
  • An RDS for MySQL, RDS for MariaDB, or Aurora MySQL 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 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
  • A database user with read privileges to the target database. The user must have SELECT permissions on the INFORMATION_SCHEMA tables.

Database object identification and validation

After you complete the database schema migration from the source schema objects of SAP ASE to their equivalent MySQL schema objects, you need to validate the schema objects to reduce issues during the final stages of migration.

To perform these validations, you first need to understand the different types of database objects that need to be considered. You can compare the following database objects between SAP ASE and the corresponding RDS for MySQL or MariaDB database:

  • Schemas
  • Tables
  • Functions
  • Views
  • Stored procedures
  • Indexes
  • Triggers
  • Constraints
    • Primary key constraints
    • Foreign key constraints
    • Check constraints
    • Default 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.

You can use the AWS SCT assessment report or its log file to identify the reason for failure if you find differences between any of the database objects. Then convert and create the respective objects to the target database equivalent manually. You need to manually correct the SQL scripts to replace the target equivalent syntax for table partitions before running them on the target database.

The queries we use in these sections exclude system schemas in the source and target databases. We cover summary-level as well as detail-level validations, wherever applicable. You can further modify these queries to include more scrutiny as required.

Schemas

In SAP ASE, the default scheme is called dbo. If any user-defined schema is present, it’s the same as the user name of the specific user who is authorized to access objects in that schema. All database objects in SAP ASE have an owner; this object owner is effectively the same thing as a schema in MySQL.

You can validate the schema at the source (SAP ASE) and target (MySQL or MariaDB) 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 MySQL or MariaDB.

select schema_name SchemaName
from information_schema.schemata
where schema_name not in ('information_schema', 'performance_schema', 'sys', 'mysql')
      and schema_name not like 'aws_sap%'
order by schema_name

You get the following output.

Verify the schema result for any possible differences between the source and target databases using the aforementioned 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; no issues were detected, so you can move to the next level of validation.

Extension packs

When you convert your database or data warehouse schema using AWS SCT, it may add additional schemas to your target database. This additional schema, known as an extension pack, implements SQL system functions of the source database that are required when writing your converted schema to your target database. When migrating your database from SAP ASE to Amazon RDS for MySQL, Amazon RDS for MariaDB, or Aurora MySQL, AWS SCT creates an extension pack named aws_sapase_ext, as seen in the following example.

The following SQL query is for MySQL or MariaDB.

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

You get the following output.

AWS SCT can automatically apply an extension pack when you apply a target database script. You can do this manually as well. 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.

AWS SCT converts source SAP ASE tables to the equivalent MySQL or MariaDB target tables with appropriate data types and relative table definitions 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 sample output in the following screenshot.

The following is a similar query for MySQL or MariaDB.

select table_schema SchemaName,
       count(table_name) TableCount
from information_schema.tables
where table_type = 'BASE TABLE'
      and table_schema not in ('information_schema', 'performance_schema', 'sys', 'mysql')
      and table_schema not like 'aws_sap%'
group by table_schema
order by table_schema

You get the following output.

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

Detailed information for each table

Validating the count of the tables under the schema or object owner is not enough to conclude that all tables are migrated properly. The following query helps verify a few key features of the tables involved in the migration, such as the presence of LOB columns, computed columns, partitions, and column count.

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 (case when sc.type in (34,35,174) then 1 else null end) is not null then 'Y' else 'N' end  LOBColumn,
       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

The following screenshots show our output.

The following is a similar query for MySQL or MariaDB.

select  t.table_schema SchemaName,
        t.table_name TableName,
        count(distinct c.column_name) ColumnCount,
        max(if((c.generation_expression IS NULL or c.generation_expression = ''),'N','Y')) ComputedColumn,
        max(if((c.column_type in ('blob', 'mediumblob', 'longblob','text', 'mediumtext','longtext') ),'Y','N')) LOBColumn,
        if((partition_name IS NOT NULL),'Y','N') PartitionTable
from information_schema.tables t
left join information_schema.columns c
     on c.table_name = t.table_name
left join information_schema.partitions p
     on t.table_name = p.table_name
where t.table_type = 'BASE TABLE'
      and t.table_schema not in ('information_schema', 'performance_schema', 'sys', 'mysql')
      and t.table_schema not like 'aws_sap%'
group by t.table_schema,t.table_name

You get the following output.

Verify the table details for any possible differences between the source and target databases 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

A view is a logical table created by a query joining one or more tables. You can validate the views by using the following queries on the source and target 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 query for MySQL or MariaDB.

select table_schema SchemaName,
       table_name ViewName
from information_schema.VIEWS
where table_schema not in ('information_schema', 'performance_schema', 'sys', 'mysql')
      and table_schema not like 'aws_sap%'
order by table_schema,table_name

You get the following output.

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

Functions

Functions implement specific business or functional logic based on the given input and return a predefined format of output. The following SQL queries give you the details of functions for both the source and target 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

You get the following output.

The following is a SQL query for MySQL or MariaDB.

select routine_schema SchemaName,
       routine_name FunctionName
from information_schema.ROUTINES
where routine_type = 'FUNCTION'
      and routine_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')
      and routine_schema not like 'aws_sap%'
order by routine_schema, routine_name

The following screenshot shows the query output.

Verify the function results for any possible differences between the source and target databases 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

A stored procedure is a collection of pre-compiled SQL statements stored in a database memory for quick access from an application program. The following SQL queries give you the count and 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 SQL query for MySQL or MariaDB.

select routine_schema AS SchemaName,
       routine_name as ProcedureName
from information_schema.ROUTINES
where routine_type = 'PROCEDURE'
      and routine_schema not in ('information_schema','sys','performance_schema','mysql')
      and routine_schema not like 'aws_sap%'
order by routine_schema,routine_name

The following screenshot shows the output.

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

Indexes

Indexes in database systems play an important role in overall system performance. Although indexes can improve performance of read operations, they also hinder write operations. Choosing the right indexing strategy is key.

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

Please note, primary keys aren’t included in the following SQL query because we validate it later in this post as a constraint.

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 MySQL or MariaDB.

select cons.table_schema  SchemaName,
       cons.table_name TableName,
       cons.constraint_name  IndexName,
       col_use.column_name ColumnName,
       'UNIQUE'  IndexType
from information_schema.table_constraints cons
join information_schema.key_column_usage col_use
     on cons.constraint_schema=col_use.constraint_schema
     and cons.constraint_name=col_use.constraint_name
     and cons.table_schema=col_use.table_schema
     and cons.table_name=col_use.table_name
where cons.constraint_type in ('UNIQUE')
     and cons.table_schema not in ('information_schema', 'sys', 'performance_schema','mysql')
     and cons.table_schema not like 'aws_sap%'
union
select idx.table_schema SchemaName,
       idx.table_name TableName,
       idx.index_name IndexName,
       idx.column_name ColumnName,
       'NON-UNIQUE'  IndexType
from information_schema.statistics idx
where idx.table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')
      and idx.table_schema not like 'aws_sap%'
      and idx.index_name not in ('PRIMARY')
      and idx.non_unique = 1
      and index_name not like '%_fk'
order by SchemaName,TableName,IndexName,ColumnName

The following screenshot shows the output.

The SAP ASE result has an extra column (IndexDescription) categorizing the indexes as clustered or non-clustered, whereas the equivalent can’t be done in MySQL because the indexes other than clustered (synonymous with primary key or unique index) are known as secondary indexes. For this verification, the details until the IndexType column should suffice.

Verify the index details for any possible differences between the source and target databases 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

Triggers are a special type of stored procedure that defines a set of actions performed in response to an insert, update, or delete operation on a specified table. The following queries give you the details of triggers for both the SAP ASE and MySQL 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.

Use the following SQL query for MySQL or MariaDB.

select tgr.event_object_schema TableSchema,
       trigger_schema TriggerSchema,
       tgr.event_object_table TableName,
       tgr.trigger_name TriggerName
from information_schema.triggers tgr
where tgr.trigger_schema not in ('sys','performance_schema','mysql')
      and tgr.trigger_schema not like 'aws_sap%'
order by tgr.event_object_table

You get the following output.

Verify the trigger details for any possible differences between the source and target databases using these queries. If you find any differences, identify the root cause or take guidance from the AWS SCT logs and fix them accordingly. If the source database has multiple trigger events (insert, update, delete) in a single trigger, it needs to be created as multiple separate triggers in MySQL, because it doesn’t support them all in a single trigger. In this case, the trigger count and the trigger name details may not match with the source.

Constraints

Constraints offer the advantage of defining integrity controls in one step during table creation 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 constraint

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.status2 & 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.

Use following SQL query for MySQL or MariaDB.

select  cons.constraint_schema as SchemaName,
        cons.table_name TableName,
        cons.constraint_name  as PK_ConstraintName,
        col_use.column_name ColumnName
from information_schema.table_constraints cons
join information_schema.key_column_usage col_use
     on cons.constraint_schema=col_use.constraint_schema
     and cons.constraint_name=col_use.constraint_name
     and cons.table_schema=col_use.table_schema
     and cons.table_name=col_use.table_name
where cons.constraint_type in ('PRIMARY KEY')
     and cons.table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')
     and cons.table_schema not like 'aws_sap%'
order by cons.constraint_schema,cons.table_name,cons.constraint_name

The following screenshot shows the output.

Foreign key constraint

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 MySQL or MariaDB.

select table_name TableName,
       constraint_name FK_Name,
       referenced_table_name Ref_TableName,
       referenced_column_name Ref_ColumnName
from information_schema.key_column_usage
where table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')
      and table_schema not like 'aws_sap%'
      and referenced_table_schema is not null
order by table_name,constraint_name,referenced_table_name

You get the following output.

Check constraint

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 query for MySQL or MariaDB.

select constraint_schema SchemaName,
       table_name TableName,
       count(constraint_name ) CheckConstraintCount
from information_schema.table_constraints
where constraint_type in ('CHECK')
      and table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')
      and table_schema not like 'aws_sap%'
group by constraint_schema,table_name
order by constraint_schema,table_name,constraint_name

You get the following output.

Default constraint

Use the following SQL query for SAP ASE.

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

You get the following output.

The following is a similar query for MySQL or MariaDB.

select table_schema SchemaName,
       table_name TableName,
       column_name ColumnName
from information_schema.columns
where column_default is not null
      and table_schema not in ('information_schema', 'sys','performance_schema','mysql')
      and table_schema not like 'aws_sap%'
order by column_name

You get the following output.

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

Conclusion

In this post, we showed how to verify if your SAP ASE database has been migrated correctly to Amazon RDS for MySQL, Amazon RDS for MariaDB, or Aurora MySQL. Use this post if you’re experiencing issues in object validation or as a reference guide while migrating from an SAP ASE database to an RDS for MySQL, RDS for MariaDB, or Aurora MySQL database.

We recommend building these validation steps into your workflow for a smoother migration experience. Note that the SQLs were tested against SAP ASE 15.7, MySQL 8.0.28, and MariaDB 10.6.7; the results may vary on other versions.

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

About the authors

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.

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

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 homogenous and heterogenous database migrations and optimization in AWS cloud.