AWS Database Blog
Validate database object consistency after migrating from IBM Db2 z/OS to Amazon RDS for Db2
Amazon Relational Database Service (Amazon RDS) for Db2 manages time-consuming database administration tasks, such as provisioning, patching, backups, recovery, failure detection, and repair, allowing enterprises to focus on their business and the applications that support it. This offers an opportunity for customers running IBM Db2 for z/OS databases to migrate to RDS for Db2 as part of their mainframe modernization journey. By migrating mainframe workloads into RDS for Db2, you can experience the benefits of a fully managed, highly available, secure, and compliant service.
In this post, we delve into the best practices for migrating database objects from IBM Db2 z/OS to RDS for Db2 and walk you through how to validate these migrated database objects. It’s crucial to thoroughly validate the consistency of database objects, including schema structure, constraints, and dependencies.
Tools for database object migration:
The following are some of the tools available to migrate an application’s database objects. These tools facilitate the generation of the data definition language (DDL) for the Db2 z/OS tables. You can use DDL commands to create the objects in RDS for Db2.
- Db2 z/OS tools such as BMC Catalog Manager, RC/Migrator, Db2 Administration Tool for z/OS.
- Third party tools such as Erwin, Toad for Db2, Dbeaver .
- IBM native tools such as IBM Data Studio, db2Look.
Although most DDLs are compatible, you might need to modify certain environment-specific keywords depending on the tools you use. For example, not all tools generate the keywords in italics in the following code snippet:
Note: For the preceding tools to remotely connect to the Db2 z/OS instance, a separate Db2 connect license might be required. When Db2 connect isn’t an option, the Db2 Community Edition is a free option to consider.
Database object validation
As a best practice, validate the database objects after migrating them from the source Db2 for z/OS to the target RDS for Db2. Confirming the successful migration of the objects is a necessary prerequisite for data migration.
In the subsequent sections, we outline the steps involved in comparing and validating database objects across the source and target databases. The objective is to make sure that both the count and characteristics of objects, such as tables, views, stored procedures, and functions, are consistent between the two environments.
In this post, we provide steps to validate the following objects:
- Schemas
- Tables
- Views
- Materialized query tables (MQT)
- Primary keys
- Foreign keys
- Unique indexes
- Nonunique indexes
- Triggers
- Stored procedures
- Functions
- Sequences
Note: Amazon RDS for Db2 doesn’t provide native support for COBOL stored procedures. As a result, you must modernize and rewrite any existing COBOL Stored Procedures in the Db2 for z/OS environment in a language supported by RDS for Db2, before you can deploy them in the target RDS environment.
Schemas
The objects in a relational database are organized into sets called schemas. A schema is a collection of named objects. The first part of a schema name is the qualifier. A schema provides a logical classification of objects in the database. The objects that a schema can contain include tables, indexes, tablespaces, distinct types, functions, stored procedures, and triggers. An object is assigned to a schema when it’s created. Schema is referred to as creator in Db2 for z/OS metadata tables and often used interchangeably. The schemas can be validated with the following queries:
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Table counts
Tables are logical structures that Db2 maintains that consist of columns and rows. Tables are grouped into schemas represented by the table creator. The following queries validate the number of tables grouped by schema matches both the source and target.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Table names
Table names in Db2 are used to identify the data stored in the table and usually follow guidelines. A qualified table name is a three-part name separated by periods:
- Location name that designates the DBMS where the table is stored (location name is optional if the table is on the same DBMS)
- Schema name or table creator
- Name of the table
The following queries generate a list of tables from both the source and target. This list can be compared to make sure that the tables are migrated and the names match.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Table column counts
A table in Db2 is composed of columns that have two basic components: the column name and data type. Each column contains data that has the same data type. The data type specifies the type of data that can be stored in a column. For example, a column defined with a data type of character stores character data whereas a column defined with a data type of integer stores integers. The following queries generate the count of columns for each table grouped by schema and tables from both the source and target. This list can be compared to make sure that each table contains the correct number of columns.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Table column with details
The previous queries generate the count of columns grouped by schema and table to make sure that each table has the correct number of columns. The following queries generate a list of columns and their data types ordered by the table creator and table name from both the source and target. This list can be compared to make sure that the columns have the correct type, length, and scale of the source and target.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Partitioned tables
A partitioned table is a way to physically split a table across multiple storage objects based on a partitioning key.
The following queries generate a list of the partitioned tables in each schema from both the source and target. This list can be compared to make sure that both the source and target contain the correct number of partitioned tables and that the partition sequence is correct.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Views
Views in a database can be used to either summarize data or restrict access at both the row and column levels. A view can either be on a single table or contain a union of more than one table. The following queries generate the count of views grouped by schema from both the source and target. This list can be compared to make sure that both the source and target contain the correct number of views.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Materialized query tables count
Materialized query tables (MQTs) are useful for complex queries that run on large amounts of data. Db2 can precompute all or part of such queries and use the precomputed, or materialized, results to answer them more efficiently. An MQT in Db2 contains the results of a query run either at creation or refresh time to provide the results more efficiently. The following queries generate the count of MQTs grouped by schema from both the source and target. This list can be compared to make sure that both the source and target contain the correct number of MQTs.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Materialized query tables with details
The previous queries generate the count of MQTs grouped by schema to make sure that each schema has the correct number of MQTs. The following queries generate a list of MQTs ordered by the table creator and name from both the source and target. This list can be compared to make sure that the MQTs have been created for the source and target.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Primary key counts
Keys in Db2 are either a single column or an ordered collection of columns related to a table. Keys can be used to enforce uniqueness or relationships between tables in the database via referential integrity. A primary key is a particular type of key that’s defined on a table to enforce uniqueness with the additional requirement that it cannot contain null values. The following queries generate the count of the primary keys in each schema from both the source and target. This list can be compared to make sure that both the source and target contain the correct number of primary keys.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Primary keys with details
The previous queries generate the count of primary keys grouped by schema to make sure that each schema has the correct number of primary keys. The following queries generate a list of primary keys ordered by the table creator, table name, column name, and column sequence in the primary key. This list can be compared to make sure that the primary keys match on the source and target.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Foreign key counts
A foreign key is a type of key that’s used to enforce referential integrity between tables. The foreign key points from a child table to a primary key on a parent table. The following queries generate the count of the foreign keys in each schema from both the source and target. This list can be compared to make sure that both the source and target contain the correct number of foreign keys.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Foreign key with details
The previous queries generate the count of foreign keys grouped by schema to make sure that each schema has the correct number of foreign keys. The following queries generate a list of foreign keys ordered by the table creator, table name, relationship name, referring table name, and column name. This list can be compared to make sure that the foreign keys match on the source and target.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Unique index counts
An application might require that the data contained in a table be unique. This is enforced by creating a unique constraint (or key). Db2 uses a unique index to enforce a unique constraint.
The following queries generate the count of unique indexes in each schema from both the source and target. This list can be compared to make sure that both the source and target contain the correct number of unique indexes.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Unique index with details
The previous queries generate the count of unique indexes grouped by schema to make sure that each schema has the correct number of unique indexes. The following queries generate a list of unique indexes ordered by the table creator, table name, index name, and column name. This list can be compared to make sure that the unique indexes match on the source and target.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Nonunique index counts
A nonunique index is an index that’s created to improve the performance of queries on data that’s nonunique. The following queries generate the count of the nonunique indexes in each schema from both the source and target. This list can be compared to make sure that both the source and target contain the correct number of nonunique indexes.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Nonunique index with details
The previous queries generate the count of nonunique indexes grouped by schema to make sure that each schema has the correct number of nonunique indexes. The following queries generate a list of nonunique indexes ordered by the index creator, table name, index name, and column name. This list can be compared to make sure that the nonunique indexes match on the source and target.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Trigger counts
A trigger is a database object that contains a set of actions (application logic) that’s run based on an action (insert, update, or delete) on a table. The following queries generate the count of the triggers in each schema from both the source and target. This list can be compared to make sure that both the source and target contain the correct number of triggers.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Trigger with details
The previous queries generate the count of triggers grouped by schema to make sure that each schema has the correct number of triggers. The following queries generate additional details of each trigger including the trigger name, activation type, and triggering event.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Stored procedure counts
A stored procedure is a unit of application code that’s stored within the database usually containing a common piece of code that can be called from different applications. The following queries generate the count of the stored procedures in each schema from both the source and target. In RDS for Db2, only native SQL and Java stored procedures are supported. This list can be compared to make sure that both the source and target contain the correct number of stored procedures.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Stored procedure with details
The previous queries generate the count of stored procedures grouped by schema to make sure that each schema has the correct number of stored procedures. The following queries generate a list containing the names of the stored procedures. This list can be compared to make sure that both the source and target contain the correct stored procedures.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Function counts
Db2 has a list of packaged functions. In some cases, an application might need to extend an existing function or create a specific function containing application logic. The following queries generate the count of the functions in each schema from both the source and target. This list can be compared to make sure that both the source and target contain the correct number of functions.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Function with details
The previous queries generate the count of functions grouped by schema to make sure that each schema has the correct number of functions. The following queries generate a list containing the names of the functions. This list can be compared to make sure that both the source and target contain the correct functions.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Sequence counts
Applications often have a requirement to generate unique key values in either ascending or descending order. These values can be consistently and accurately generated within the database by using sequences. The following queries generate the count of the sequences in each schema from both the source and target. This list can be compared to make sure that both the source and target contain the correct number of sequences.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Sequence with details
The previous queries generate the count of sequences grouped by schema to make sure that each schema has the correct number of sequences. The following queries generate a list containing the names of the sequences together with details such as the cycle rule, the order, and the number to cache. This list can be compared to make sure that both the source and target contain the correct sequence details.
Db2 for z/OS SQL statement | RDS for Db2 SQL statement |
Conclusion
In this post, we discussed how to validate various database objects with metadata queries for Db2 for z/OS and Amazon RDS for Db2 databases. This validation is a key step in ensuring the completeness and accuracy of a migration and thus ensuring a seamless transition from the source Db2 database on z/OS to the target Amazon RDS for Db2 database. After the number and granular object details have been validated, the application should follow the flow of unit, regression, and stress testing.
If you have any questions or comments, post your thoughts in the comments section.
About the authors
Feng Cai is a Database Consultant at AWS. He has a wide background in providing database services to different clients. Feng is currently a member of the AWS World-Wide Public-Sector team, providing homogeneous and heterogeneous database migration support to customers.
Dean Capps is a Database Consultant at AWS and has experience providing database support on legacy, open systems, and cloud platforms. Dean is currently a member of the AWS World-Wide Public-Sector team, providing homogeneous and heterogeneous database migration support to public sector clients.
Brenesh Stanslas Flower Mary is a Sr. Migration and modernization Architect at AWS. His areas of specialization include migrating and modernizing legacy applications, databases, infrastructure, and middleware with a focus on developing reusable architecture patterns that accelerate cloud adoption journeys.
Javeed Mohammed is a Sr. Database Specialist Solutions Architect with Amazon Web Services. He works with the Amazon RDS team, focusing on commercial database engines like Oracle and Db2. He enjoys working with customers to help design, deploy, and optimize relational database workloads on the AWS Cloud.