AWS Database Blog

Validate database objects after migrating from IBM Db2 z/OS to Amazon RDS for MySQL or Amazon Aurora MySQL

Migrating your database from IBM Db2 z/OS to Amazon Relational Database Service (Amazon RDS) for MySQL or Amazon Aurora MySQL-Compatible Edition is a multistage process, which usually includes assessment, database schema conversion, data migration, functional testing, performance tuning, and many other steps spanning across the stages.

You can use the AWS Schema Conversion Tool (AWS SCT) to convert your database schema into a format compatible with your target database. AWS Database Migration Service (AWS DMS) supports many of the most popular source and target database engines to help you migrate databases to AWS quickly and securely.

Schema conversion with AWS SCT is a semi-automated process, so there might be a chance of missing database objects or key features in the target database. Therefore, schema validation is a crucial milestone that prevents missing database objects during schema conversion and certifies that everything intended for migration has been migrated successfully.

In this post, we walk you through how to validate the database schema objects migrated from Db2 z/OS to Amazon RDS for MySQL or Amazon Aurora MySQL-Compatible Edition. We have covered a similar validation between Db2 z/OS to Amazon Aurora PostgreSQL-Compatible Edition in an earlier post.

Validating database objects

You should perform schema validation right after you successfully convert the source schema objects from Db2 z/OS to their equivalent MySQL schema objects. To perform the validations, we first need to understand the different types of Db2 z/OS database objects and their equivalent MySQL database object type.

The following list shows database objects that you can compare between Db2 z/OS (source) and the corresponding MySQL database (target). We should validate these objects thoroughly to reduce issues during later stages of database migration.

  • Schemas
  • Tables
  • Views
  • Primary keys
  • Foreign keys
  • Indexes
  • Triggers
  • Procedures
  • Functions

In following sections, we deep dive into each of these object types and validate using SQL queries to help us identify any missing migrated schema objects.

If you find differences for any of the schema objects, identify the reason of failure from the AWS SCT logs, convert the objects to the target database equivalent manually, and create the objects on the target database. For example, the SQL syntax to create data partition tables in Db2 z/OS is different than MySQL. As a result, these tables aren’t created on the target database. 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 both summary-level as well as detail-level validations wherever applicable. You can further modify these queries to include more scrutiny as required.

Note: Examples used in the next sections show matching counts. These queries may have negative performance impact on instance with large number of objects (depending on the engine versions) so run them during non-business hours or on a clone instance.

Migration considerations

Reviewing the converted data types helps you determine if the existing indexes and query plans need adjustments for optimal performance in MySQL. It allows you to consider factors such as indexing on appropriate data types, column lengths, and collation settings.

Similarly, check your indexes as different database systems have variations in their indexing mechanisms. During the conversion process, AWS SCT attempts to map the indexes from the source database to MySQL. However, the index structures, syntax, and optimization techniques may differ. Reviewing the converted indexes allows you to ensure that the indexing strategy in MySQL aligns with best practices and takes advantage of MySQL’s indexing capabilities.

Schemas

Schemas are used to represent a collection of database objects that serve a related functionality in an application or microservice. You should validate the schemas at the source and target databases using the following SQL queries:

Db2 for z/OS Query MySQL Query 
SELECT DISTINCT CREATOR AS SCHEMA_NAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR NOT LIKE 'SYS%' ORDER BY SCHEMA_NAME;
 
select schema_name
from INFORMATION_SCHEMA.schemata
where SCHEMA_NAME not in ('information_schema', 
'performance_schema', 'sys', 'mysql')
and SCHEMA_NAME not like 'aws_db2z/OS%'
order by schema_name;

Extension packs

When you convert your database or data warehouse schema, AWS SCT may add additional schemas to your target database. These schemas implement SQL system functions of the source database that are required when writing your converted schema to your target database. These additional schemas are called extension packs.

When migrating your database from Db2 z/OS to Amazon RDS for MySQL or Amazon Aurora MySQL-Compatible Edition, AWS SCT creates two extension packs: aws_db2z/OS_ext and aws_db2z/OS_ext_data, as shown in the following example code:

MySQL Query 
select schema_name
from INFORMATION_SCHEMA.schemata
where SCHEMA_NAME in ('aws_db2z/OS_ext','aws_db2z/OS_ext_data')
order by schema_name;

     

You can directly deploy these extension pack schemas to the target database after verifying the native equivalent options in both the source and target databases.

Tables

AWS SCT converts source Db2 z/OS tables to the equivalent MySQL target tables with appropriate data types and relative table definitions using the default or custom mapping rules. The following SQL queries return the counts and detail-level information for all the tables, assuming the source database doesn’t have any partitioned tables:

Db2 for z/OS Query MySQL Query 
SELECT TAB.CREATOR AS SCHEMA_NAME,
COUNT(TAB.NAME) AS TABLE_COUNT FROM SYSIBM.SYSTABLES TAB
WHERE TAB.TYPE = 'T'
AND TAB.CREATOR NOT LIKE 'SYS%'
GROUP BY TAB.CREATOR
ORDER BY TAB.CREATOR;
 
SELECT table_schema AS Schema_name,
Count(table_name) AS Tables_Count
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_db2z/OS%'
GROUP BY table_schema
ORDER BY table_schema;
    
   

For detail-level information, use the following queries:

Db2 for z/OS Query MySQL Query 
SELECT TAB.CREATOR AS SCHEMA_NAME,
TAB.NAME AS TABLE_NAME FROM SYSIBM.SYSTABLES TAB
WHERE TAB.TYPE = 'T'
AND TAB.CREATOR NOT LIKE 'SYS%'
ORDER BY TAB.CREATOR,TAB.NAME;
 
SELECT table_schema AS Schema_name,
table_name AS Table_Name
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_db2z/OS%'
ORDER BY table_schema,table_name;
  
  

Views

A view is the result set of a stored query, which can be queried in the same manner as a persistent database collection object like table. You can validate the views count converted by AWS SCT by using the following queries on the source and target databases:

Db2 for z/OS Query MySQL Query 
SELECT TAB.CREATOR AS SCHEMA_NAME,
COUNT(TAB.NAME) AS VIEW_COUNT
FROM SYSIBM.SYSTABLES TAB
WHERE TAB.TYPE = 'V'
AND TAB.CREATOR NOT LIKE 'SYS%'
GROUP BY TAB.CREATOR
ORDER BY TAB.CREATOR;
 
SELECT TABLE_SCHEMA AS Schema_Name,
count(TABLE_NAME) AS View_Count
FROM information_schema.VIEWS
where table_schema not in ('information_schema', 
'performance_schema', 'sys', 'mysql')
and table_schema not like 'aws_db2z/OS%'
group by TABLE_SCHEMA
order by TABLE_SCHEMA;
 
 

For detail-level information, use the following queries:

Db2 for z/OS Query MySQL Query 
SELECT TAB.CREATOR AS SCHEMA_NAME,
TAB.NAME AS VIEW_NAME
FROM SYSIBM.SYSTABLES TAB
WHERE TAB.TYPE = 'V'
AND TAB.CREATOR NOT LIKE 'SYS%'
ORDER BY TAB.CREATOR, TAB.NAME;
 
SELECT TABLE_SCHEMA AS Schema_Name,
TABLE_NAME AS View_Name
FROM   information_schema.VIEWS
where  table_schema not in ('information_schema', 
'performance_schema', 'sys', 'mysql')
and    table_schema not like 'aws_db2z/OS%'
order by TABLE_SCHEMA,TABLE_NAME;
   

Primary keys

A primary key is a column or group of columns whose values uniquely identify every row in the table. The following queries help you extract the counts and details of the primary keys in the source and target databases:

Db2 for z/OS Query MySQL Query 
SELECT TBCREATOR AS
SCHEMA_NAME,COUNT(CONSTNAME) AS PK_COUNT
FROM SYSIBM.SYSTABCONST
WHERE TBCREATOR NOT LIKE 'SYS%'
AND TYPE='P'
GROUP BY TBCREATOR
Order by TBCREATOR;
 
SELECT cons.CONSTRAINT_SCHEMA as SCHEMA_NAME
, count(cons.CONSTRAINT_NAME) as PK_Count
FROM information_schema.TABLE_CONSTRAINTS cons
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_db2z/OS%'
group by cons.CONSTRAINT_SCHEMA
order by cons.CONSTRAINT_SCHEMA;
 
   

To verify details including the column names in the constraint along with their ordinal position, you can use the following queries:

Db2 for z/OS Query MySQL Query 
SELECT TBCREATOR AS TABLE_SCHEMA, TBNAME AS TABLE_NAME,
CONSTNAME AS CONSTRTAINT_NAME,
COLNAME AS COLUMN_NAME,
COLSEQ AS POSITION
FROM SYSIBM.SYSKEYCOLUSE
WHERE TBCREATOR NOT LIKE 'SYS%'
ORDER BY TBCREATOR,TBNAME, COLNAME,COLSEQ;
 
SELECT cons.CONSTRAINT_SCHEMA as SCHEMA_NAME
, col_use.TABLE_NAME
, cons.CONSTRAINT_NAME
, col_use.COLUMN_NAME
, col_use.ORDINAL_POSITION as POSITION
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_db2z/OS%'
order by cons.CONSTRAINT_SCHEMA, col_use.TABLE_NAME,col_use.COLUMN_NAME
,col_use.ORDINAL_POSITION;

Foreign keys

Foreign keys link data in a parent table to the data in another child table. Foreign key constraints help maintain referential integrity between tables. You can use the following queries to get the counts and detail-level information about the foreign keys in both the source and target databases:

 

Db2 for z/OS Query MySQL Query 
SELECT FK.CREATOR AS SCHEMA_NAME,COUNT(*) AS FK_COUNT
FROM SYSIBM.SYSFOREIGNKEYS FK 
WHERE 
FK.CREATOR NOT LIKE 'SYS%'
GROUP BY FK.CREATOR
ORDER BY FK.CREATOR;
 
 		 
            
           

               
            
SELECT TABLE_SCHEMA as SCHEMA_NAME,
count(*) as FK_COUNT
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA not in ('information_schema', 
'sys', 'performance_schema', 'mysql')
and TABLE_SCHEMA not like 'aws_db2z/OS%'
and REFERENCED_TABLE_SCHEMA IS NOT NULL
group by TABLE_SCHEMA
order by TABLE_SCHEMA;
 
           
 		 
         
 
         
 
              
           
           

                
             
           
 		 
         
 
         
       
 
       
For detailed information, use the following queries:
 
       
 
         
         
 
          Db2 for z/OS Query 
          MySQL Query 
         
 
         
 
           
           
 
            
SELECT FK.CREATOR AS SCHEMA_NAME,
REL.TBNAME AS TABLE_NAME,
FK.COLNAME AS COLUMN_NAME,
REL.RELNAME AS FK_CONST_NAME,
REL.REFTBNAME AS FOREIGN_TABLE_NAME
FROM SYSIBM.SYSFOREIGNKEYS FK
LEFT OUTER JOIN SYSIBM.SYSRELS REL
ON FK.CREATOR = REL.REFTBCREATOR AND FK.TBNAME = REL.TBNAME
WHERE
FK.CREATOR NOT LIKE 'SYS%'
ORDER BY FK.CREATOR, REL.TBNAME, REL.RELNAME, REL.REFTBNAME, FK.COLNAME;
 
           
 		 
           
           
 
            
SELECT TABLE_SCHEMA as schema_name,
TABLE_NAME as table_name,
COLUMN_NAME ,
CONSTRAINT_NAME as fk_constraint_name,
REFERENCED_TABLE_NAME as foreign_table_name,
REFERENCED_COLUMN_NAME as foreign_column_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA not in ('information_schema', 
'sys', 'performance_schema', 'mysql')
and TABLE_SCHEMA not like 'aws_db2z/OS%'
and REFERENCED_TABLE_SCHEMA IS NOT NULL
order by TABLE_SCHEMA, TABLE_NAME, 
CONSTRAINT_NAME, REFERENCED_TABLE_NAME, COLUMN_NAME;
 
           
 		 
         
 
         
 
           
           

                  
             
           
 		 
           
           

                 
             
           
 		 
         
 
         
       
 
       
Indexes
 
       
Indexes play a key role in improving query performance. Because tuning methodologies differ from database to database, the number of indexes and their types vary between Db2 z/OS and MySQL databases.
 
       
With the following queries, you can get the counts of indexes and their types in both Db2 z/OS and MySQL databases.
 
       
Unique indexes
 
       
Use the following queries for unique indexes:
 
       
 
         
         
 
          Db2 for z/OS Query 
          MySQL Query 
         
 
         
 
           
           
 
            
SELECT IND.CREATOR AS SCHEMA_NAME,
COUNT(*) AS UNIQUE_COUNT
FROM SYSIBM.SYSINDEXES IND
INNER JOIN SYSIBM.SYSTABLES T ON IND.CREATOR=T.CREATOR AND IND.TBNAME=T.NAME
WHERE IND.CREATOR NOT LIKE 'SYS%' AND IND.UNIQUERULE in ('U')
AND T.TYPE='T'
GROUP BY IND.CREATOR
ORDER BY IND.CREATOR;
 
           
 		 
           
           
 
            
SELECT cons.table_schema as SCHEMA_NAME
, count(distinct cons.table_name) as unique_count
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_db2z/OS%'
group by cons.table_schema
order by cons.table_schema;
 
           
 		 
         
 
         
 
           
           

                
             
           
 		 
           
           

                
             
           
 		 
         
 
         
       
 
       
For detailed information, use the following queries:
 
       
 
         
         
 
          Db2 for z/OS Query 
          MySQL Query 
         
 
         
 
           
           
 
            
SELECT IND.CREATOR AS SCHEMA_NAME,
IND.TBNAME AS TABLE_NAME,
IND.NAME AS CONTRAINT_NAME,
'Unique Index' AS CONSTRAINT_TYPE,
COL.COLNAME AS COLUMN_NAME
FROM SYSIBM.SYSINDEXES IND
LEFT OUTER JOIN SYSIBM.SYSKEYS COL ON IND.CREATOR = COL.IXCREATOR 
AND IND.NAME = COL.IXNAME
WHERE IND.CREATOR NOT LIKE 'SYS%' AND IND.UNIQUERULE IN ('U')
ORDER BY IND.CREATOR, IND.TBNAME,IND.NAME, COL.COLNAME;
 
           
 		 
           
           
 
            
SELECT cons.table_schema as SCHEMA_NAME
, cons.TABLE_NAME
, cons.CONSTRAINT_NAME as CONSTRAINT_NAME
,'Unique Index' as constraint_type
, col_use.COLUMN_NAME
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_db2z/OS%'
order by cons.table_schema, cons.TABLE_NAME, 
cons.CONSTRAINT_NAME, col_use.COLUMN_NAME;
 
           
 		 
         
 
         
 
           
           

                 
             
           
 		 
           
           

                 
             
           
 		 
         
 
         
       
 
       
Non-unique indexes
 
       
MySQL creates implicit indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. The MySQL database queries used in this section exclude such indexes so that you can perform the validations against the source without any mismatch:
 
       
 
         
         
 
          Db2 for z/OS Query 
          MySQL Query 
         
 
         
 
           
           
 
            
SELECT IND.CREATOR AS SCHEMA_NAME,
COUNT(*) AS NON_UNIQUE_COUNT
FROM SYSIBM.SYSINDEXES IND
WHERE IND.CREATOR NOT LIKE 'SYS%' AND IND.UNIQUERULE in ('D')
GROUP BY IND.CREATOR
ORDER BY IND.CREATOR;
 
           
 		 
           
           
 
            
SELECT idx.table_schema
,count(idx.index_name) as index_count
FROM INFORMATION_SCHEMA.STATISTICS idx
WHERE idx.TABLE_SCHEMA not in ('information_schema', 
'sys', 'performance_schema', 'mysql', 'DB2INST1')
and idx.table_schema not like 'aws_db2z/OS%'
and idx.index_name not in ('PRIMARY')
and idx.non_unique = 1
and index_name not like 'FK_%'
group by idx.table_schema
order by idx.table_schema;
 
           
 		 
         
 
         
 
           
           

                  
             
           
 		 
           
           

                 
             
           
 		 
         
 
         
       
 
       
For detailed information, use the following queries:
 
       
 
         
         
 
          Db2 for z/OS Query 
          MySQL Query 
         
 
         
 
           
           
 
            
SELECT IND.CREATOR AS SCHEMA_NAME,
IND.TBNAME AS TABLE_NAME,
IND.NAME AS CONTRAINT_NAME,
'Non-Unique Index' AS CONSTRAINT_TYPE,
COL.COLNAME AS COLUMN_NAME
FROM SYSIBM.SYSINDEXES IND
LEFT OUTER JOIN SYSIBM.SYSKEYS COL ON IND.CREATOR = COL.IXCREATOR 
AND IND.NAME = COL.IXNAME
WHERE IND.CREATOR NOT LIKE 'SYS%' AND IND.UNIQUERULE IN ('D')
ORDER BY IND.CREATOR, IND.TBNAME,IND.NAME, COL.COLNAME;
 
           
 		 
           
           
 
            
SELECT idx.table_schema AS SCHEMA_NAME,
idx.table_name,
idx.index_name AS CONTRAINT_NAME,
'Non-Unique Index' as constraint_type,
idx.column_name
FROM INFORMATION_SCHEMA.STATISTICS idx
WHERE idx.TABLE_SCHEMA not in ('information_schema', 
'sys', 'performance_schema', 'mysql', 'DB2INST1')
and idx.table_schema not like 'aws_db2z/OS%'
and idx.index_name not in ('PRIMARY')
and idx.non_unique = 1
and index_name not like 'FK_%'
order by idx.table_schema
, idx.table_name
, idx.index_name
, idx.column_name;
 
           
 		 
         
 
         
 
           
           

                 
             
           
 		 
           
           

                 
             
           
 		 
         
 
         
       
 
       
Triggers
 
       
Triggers define a set of actions that are performed in response to an insert, update, or delete rows in the associated table. The following queries give you the count and details of triggers for both the source and target databases:
 
       
 
         
         
 
          Db2 for z/OS Query 
          MySQL Query 
         
 
         
 
           
           
 
            
SELECT SCHEMA AS SCHEMA_NAME,
COUNT(*) AS TRIGGER_COUNT
FROM SYSIBM.SYSTRIGGERS
WHERE SCHEMA NOT LIKE 'SYS%'
GROUP BY SCHEMA
ORDER BY SCHEMA;
 
           
 		 
           
           
 
            
SELECT tgr.EVENT_OBJECT_SCHEMA as TABLE_SCHEMA
, count(tgr.TRIGGER_NAME) as trigger_count
FROM information_schema.triggers tgr
where tgr.TRIGGER_SCHEMA not in ('sys','performance_schema','mysql')
and tgr.TRIGGER_SCHEMA not like 'aws_db2z/OS%'
group by tgr.EVENT_OBJECT_SCHEMA
order by tgr.EVENT_OBJECT_SCHEMA;
 
           
 		 
         
 
         
 
           
           
 
             
           
 		 
           
           

                 
             
           
 		 
         
 
         
       
 
       
For detail-level information, use the following queries:
 
       
 
         
         
 
          Db2 for z/OS Query 
          MySQL Query 
         
 
         
 
           
           
 
            
SELECT SCHEMA AS SCHEMA_NAME,
NAME AS TRIGGER_NAME,
TBNAME AS TABLE_NAME,
CASE TRIGTIME
WHEN 'B' THEN 'BEFORE'
WHEN 'A' THEN 'AFTER'
WHEN 'I' THEN 'INSTEAD OF'
END AS ACTIVATION,
RTRIM(CASE WHEN TRIGEVENT ='U' THEN 'UPDATE ' ELSE '' END
||
CASE WHEN TRIGEVENT ='D' THEN 'DELETE ' ELSE '' END
||
CASE WHEN TRIGEVENT ='I' THEN 'INSERT ' ELSE '' END) AS EVENT
FROM SYSIBM.SYSTRIGGERS
WHERE SCHEMA NOT LIKE 'SYS%'
ORDER BY SCHEMA, NAME, TBNAME;
 
           
 		 
           
           
 
            
SELECT tgr.EVENT_OBJECT_SCHEMA as TABLE_SCHEMA
,tgr.TRIGGER_NAME
,tgr.EVENT_OBJECT_TABLE as Table_name
,tgr.ACTION_TIMING as activation
,tgr.EVENT_MANIPULATION as event
FROM information_schema.triggers tgr
where tgr.TRIGGER_SCHEMA not in ('sys', 'performance_schema', 'mysql')
and tgr.TRIGGER_SCHEMA not like 'aws_db2z/OS%'
order by tgr.EVENT_OBJECT_SCHEMA, tgr.TRIGGER_NAME, tgr.EVENT_OBJECT_TABLE;
 
           
 		 
         
 
         
 
           
           

                 
             
           
 		 
           
           

                  
             
           
 		 
         
 
         
       
 
       
Procedures
 
       
A stored procedure is a collection of precompiled SQL statements stored in a database that can be called from an application program. Stored procedures improve productivity because similar SQL statements or the business logic are consolidated and reused across applications or other programs. The following queries give you the count and details of stored procedures for both the source and target databases:
 
       
 
         
         
 
          Db2 for z/OS Query 
          MySQL Query 
         
 
         
 
           
           
 
            
SELECT SCHEMA AS SCHEMA_NAME,
COUNT(*) AS PROC_COUNT
FROM SYSIBM.SYSROUTINES
WHERE ROUTINETYPE = 'P'
AND SCHEMA NOT LIKE 'SYS%'
GROUP BY SCHEMA
ORDER BY SCHEMA;
 
           
 		 
           
           
 
            
SELECT rtn.ROUTINE_SCHEMA as SCHEMA_NAME
,count(*) as proc_count
FROM information_schema.ROUTINES rtn
where rtn.ROUTINE_TYPE = 'PROCEDURE'
and rtn.ROUTINE_SCHEMA not in ('information_schema','sys', 
'performance_schema', 'mysql')
and rtn.ROUTINE_SCHEMA not like 'aws_db2z/OS%'
group by rtn.ROUTINE_TYPE
order by rtn.ROUTINE_TYPE;
 
           
 		 
         
 
         
 
           
           
 
             
           
 		 
           
           

                 
             
           
 		 
         
 
         
       
 
       
For detail-level information, use the following query:
 
       
 
         
         
 
          Db2 for z/OS Query 
          MySQL Query 
         
 
         
 
           
           
 
            
SELECT SCHEMA AS SCHEMA_NAME,
NAME AS PROCEDURE_NAME
FROM SYSIBM.SYSROUTINES
WHERE ROUTINETYPE = 'P'
AND SCHEMA NOT LIKE 'SYS%'
ORDER BY SCHEMA,NAME;
 
           
 		 
           
           
 
            
SELECT rtn.ROUTINE_SCHEMA as SCHEMA_NAME
,rtn.ROUTINE_NAME as PROCEDURE_NAME
FROM information_schema.ROUTINES rtn
where rtn.ROUTINE_TYPE = 'PROCEDURE'
and rtn.ROUTINE_SCHEMA not in ('information_schema','sys', 
'performance_schema', 'mysql')
and rtn.ROUTINE_SCHEMA not like 'aws_db2z/OS%'
ORDER BY SCHEMA_NAME, PROCEDURE_NAME;
 
           
 		 
         
 
         
 
           
           
 
             
           
 		 
           
           

                
             
           
 		 
         
 
         
       
 
       
Functions
 
       
Functions implement specific business or functional logic based on the given input and return a predefined output. The following queries give you the count and details of functions for both the source and target databases:
 
       
 
         
         
 
          Db2 for z/OS Query 
          MySQL Query 
         
 
         
 
           
           
 
            
SELECT SCHEMA AS SCHEMA_NAME,
COUNT(*) AS PROC_COUNT
FROM SYSIBM.SYSROUTINES
WHERE ROUTINETYPE = 'F'
AND SCHEMA NOT LIKE 'SYS%'
GROUP BY SCHEMA
ORDER BY SCHEMA;
 
           
 		 
           
           
 
            
SELECT rtn.ROUTINE_SCHEMA as SCHEMA_NAME
,count(*) as func_count
FROM information_schema.ROUTINES rtn
where rtn.ROUTINE_TYPE = 'FUNCTION'
and rtn.ROUTINE_SCHEMA not in ('information_schema', 
'sys', 'performance_schema', 'mysql')
and rtn.ROUTINE_SCHEMA not like 'aws_db2z/OS%'
group by rtn.ROUTINE_TYPE
order by rtn.ROUTINE_TYPE;
 
           
 		 
         
 
         
 
           
           
 
             
           
 		 
           
           

                
             
           
 		 
         
 
         
       
 
       
For detail-level information, use the following query:
 
       
 
         
         
 
          Db2 for z/OS Query 
          MySQL Query 
         
 
         
 
           
           
 
            
SELECT SCHEMA AS SCHEMA_NAME,
NAME AS PROCEDURE_NAME
FROM SYSIBM.SYSROUTINES
WHERE ROUTINETYPE = 'F'
AND SCHEMA NOT LIKE 'SYS%'
ORDER BY SCHEMA,NAME;
 
           
 		 
           
           
 
            
SELECT rtn.ROUTINE_SCHEMA as SCHEMA_NAME
,rtn.ROUTINE_NAME as FUNCTION_NAME
FROM information_schema.ROUTINES rtn
where rtn.ROUTINE_TYPE = 'FUNCTION'
and rtn.ROUTINE_SCHEMA not in ('information_schema',
'sys', 'performance_schema', 'mysql')
and rtn.ROUTINE_SCHEMA not like 'aws_db2z/OS%'
ORDER BY SCHEMA_NAME, FUNCTION_NAME;
 
           
 		 
         
 
         
 
           
           
 
             
           
 		 
           
           

                 
             
           
 		 
         
 
         
       
 
       
Useful MySQL catalog tables
 
       
The following table summarizes some of the Db2 z/OS objects and their corresponding objects on MySQL that are helpful for database object validation. For MySQL databases with many objects and concurrent workloads, the queries provided in this post can take longer to complete. This can be improved to some extent using data dictionary changes in MySQL 8.0
 
       
 
         
         
 
          Db2 z/OS 
          MySQL  
         
 
         
 
          sysibm.systables/ sysibm.syscolumns 
          information_schema.tables 
         
 
         
 
          sysibm.systables/ sysibm.syscolumns 
          information_schema.views 
         
 
         
 
          sysibm.systables/ sysibm.syscolumns / sysibm.sysforeignkeys / sysibm.sysrels 
          information_schema.table_constraints 
         
 
         
 
          sysibm.sysroutines 
          information_schema.routines 
         
 
         
 
          sysibm.systriggers 
          information_schema.triggers 
         
 
         
 
          sysibm.systables / sysibm.systablespace / sysibm.systablepart 
          Information_schema.partitions 
         
 
         
       
 
       
Handling objects not supported in MySQL
 
       
For Db2 z/OS objects which are not supported by MySQL (like aliases, sequences, or materialized query tables), you must manually perform the code conversion for such database objects from Db2 Z/OS to MySQL to achieve similar functionality. You can use the queries provided in this post to iteratively validate the migrated objects to identify gaps and fix them.
 
       
Conclusion
 
       
Database object validation is an essential step that provides an in-depth view of migration accuracy. It confirms whether all the database objects are migrated appropriately and the integrity of target database. It ensures business continuity of the dependent application processes.
 
       
In this post, we discussed post-migration validation of database objects with the metadata queries for a Db2 z/OS source and RDS for MySQL or Aurora MySQL target database. You can run the queries provided in this post on your source and target database to retrieve the metadata and compare the output to confirm if your migration was successful.
 
       
Let us know if you have any comments or questions. We value your feedback!
 
       
 
       
About the Authors
 
       
Sai Parthasaradhi is a Database Migration Specialist with AWS Professional Services. He works closely with customers to help them migrate and modernize their databases on AWS.
 
       
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 the AWS Cloud.
 
       
Vikas Gupta is a Lead Database Migration Consultant with AWS Professional Services. He loves to automate manual processes and enhance the user experience. He helps customers migrate and modernize workloads in the AWS Cloud, with a special focus on modern application architectures and development best practices.