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
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
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
For detail-level information, use the following queries:
Db2 for z/OS Query
MySQL Query
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
For detail-level information, use the following queries:
Db2 for z/OS Query
MySQL Query
  Â
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
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
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
Â
 Â
For detailed information, use the following queries:
Db2 for z/OS Query
MySQL Query
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
For detailed information, use the following queries:
Db2 for z/OS Query
MySQL Query
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
For detailed information, use the following queries:
Db2 for z/OS Query
MySQL Query
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
For detail-level information, use the following queries:
Db2 for z/OS Query
MySQL Query
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
For detail-level information, use the following query:
Db2 for z/OS Query
MySQL Query
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
For detail-level information, use the following query:
Db2 for z/OS Query
MySQL Query
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.
Read MoreAWS Database Blog