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:

CREATE TABLE ABC()
IN "DBNAME"."TSNAME"
CCSID EBCDIC

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:

SELECT DISTINCT CREATOR AS SCHEMA_NAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR NOT LIKE 'SYS%'
AND CREATOR NOT IN ('SQLJ','NULLID')
ORDER  BY SCHEMA_NAME;

SELECT SCHEMANAME AS SCHEMA_NAME
FROM SYSCAT.SCHEMATA
WHERE SCHEMANAME NOT LIKE 'SYS%'
AND SCHEMANAME NOT IN ('SQLJ','NULLID')
ORDER BY SCHEMA_NAME;

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.

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 TAB.TABSCHEMA AS SCHEMA_NAME ,
COUNT(TAB.TABNAME) AS TABLE_COUNT
FROM SYSCAT.TABLES TAB
WHERE TAB.TYPE = 'T'
AND TAB.TABSCHEMA NOT LIKE 'SYS%'
GROUP BY TAB.TABSCHEMA
ORDER BY TAB.TABSCHEMA;

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.

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 TAB.TABSCHEMA AS SCHEMA_NAME ,
TAB.TABNAME AS TABLE_NAME
FROM SYSCAT.TABLES TAB
WHERE TAB.TYPE = 'T'
AND TAB.TABSCHEMA NOT LIKE 'SYS%'
ORDER BY TAB.TABSCHEMA, TAB.TABNAME;

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.

SELECT TBCREATOR AS SCHEMA_NAME ,
TBNAME AS TABLE_NAME ,
COUNT(NAME) AS NUM_OF_COLUMNS
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR NOT LIKE 'SYS%'
GROUP BY CREATOR, TBNAME
ORDER BY CREATOR, TBNAME;

SELECT TABSCHEMA AS SCHEMA_NAME ,
TABNAME AS TABLE_NAME ,
COUNT(COLNAME) AS NUM_OF_COLUMNS
FROM SYSCAT.COLUMNS
WHERE TABSCHEMA NOT LIKE 'SYS%'
GROUP BY TABSCHEMA, TABNAME
ORDER BY TABSCHEMA, TABNAME;

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.

SELECT TBCREATOR AS SCHEMA_NAME ,
TBNAME AS TABLE_NAME ,
NAME AS COL_NAME ,
COLTYPE AS TYPE_NAME ,
LENGTH AS LENGTH ,
SCALE AS SCALE
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR NOT LIKE 'SYS%'
AND COLTYPE <> 'ROWID'
ORDER BY TBCREATOR, TBNAME, NAME;

SELECT TABSCHEMA AS SCHEMA_NAME ,
      TABNAME AS TABLE_NAME ,
      COLNAME AS COL_NAME ,
      CASE TYPENAME
        WHEN 'CHARACTER' THEN 'CHAR'
        WHEN 'TIMESTAMP' THEN 'TIMESTMP'
        WHEN 'DOUBLE' THEN 'FLOAT'
        WHEN 'SMALLINT' THEN 'SMALLINT'
        WHEN 'LONG VARCHAR' THEN 'LONGVAR'
        WHEN 'VARBINARY' THEN 'VARBIN'
        WHEN 'DECIMAL' THEN 'DECIMAL'
        WHEN 'INTEGER' THEN 'INTEGER'
        WHEN 'VARCHAR' THEN 'VARCHAR'
        WHEN 'BIGINT' THEN 'BIGINT'
        WHEN 'BINARY' THEN 'BINARY'
        WHEN 'BLOB' THEN 'BLOB'
        WHEN 'CLOB' THEN 'CLOB'
        WHEN 'DATE' THEN 'DATE'
        WHEN 'REAL' THEN 'REAL'
        WHEN 'TIME' THEN 'TIME'
        WHEN 'XML' THEN 'XML'
      END AS TYPE_NAME ,
      LENGTH AS LENGTH ,
      SCALE AS SCALE
FROM SYSCAT.COLUMNS
WHERE TABSCHEMA NOT LIKE 'SYS%'
ORDER BY TABSCHEMA, TABNAME, COLNAME;

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.

SELECT CREATOR AS TABLE_SCHEMA ,
NAME AS TABLE_NAME ,
PARTITION AS SEQNO
FROM SYSIBM.SYSTABLES TAB ,
SYSIBM.SYSTABLEPART PART
WHERE TAB.TSNAME = PART.TSNAME
AND PART.PARTITION > 0
AND TAB.TYPE = 'T'
AND TAB.DBNAME = PART.DBNAME
ORDER BY TABLE_SCHEMA, TABLE_NAME;

SELECT TABSCHEMA AS SCHEMA_NAME ,
TABNAME AS TABLE_NAME ,
SEQNO AS SEQNO
FROM SYSCAT.DATAPARTITIONS
WHERE TABSCHEMA NOT LIKE 'SYS%'
ORDER BY TABSCHEMA, TABNAME;

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.

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 TAB.TABSCHEMA AS SCHEMA_NAME ,
COUNT(TAB.TABNAME) AS TABLE_COUNT
FROM SYSCAT.TABLES TAB
WHERE TAB.TYPE = 'V'
AND TAB.TABSCHEMA NOT LIKE 'SYS%'
GROUP BY TAB.TABSCHEMA
ORDER BY TAB.TABSCHEMA;

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.

SELECT TAB.CREATOR AS SCHEMA_NAME ,
COUNT(TAB.NAME) AS TABLE_COUNT
FROM SYSIBM.SYSTABLES TAB
WHERE TAB.TYPE = 'M'
AND TAB.CREATOR NOT LIKE 'SYS%'
GROUP BY TAB.CREATOR
ORDER BY TAB.CREATOR;

SELECT TAB.TABSCHEMA AS SCHEMA_NAME ,
COUNT(TAB.TABNAME) AS TABLE_COUNT
FROM SYSCAT.TABLES TAB
WHERE TAB.TYPE = 'S'
AND TAB.TABSCHEMA NOT LIKE 'SYS%'
GROUP BY TAB.TABSCHEMA
ORDER BY TAB.TABSCHEMA;

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.

SELECT TAB.CREATOR AS SCHEMA_NAME ,
TAB.NAME AS TABLE_NAME
FROM SYSIBM.SYSTABLES TAB
WHERE TAB.TYPE = 'M'
AND TAB.CREATOR NOT LIKE 'SYS%'
ORDER BY TAB.CREATOR, TAB.NAME;

SELECT TAB.TABSCHEMA AS SCHEMA_NAME ,
TAB.TABNAME AS TABLE_NAME
FROM SYSCAT.TABLES TAB
WHERE TAB.TYPE = 'S'
AND TAB.TABSCHEMA NOT LIKE 'SYS%'
ORDER BY TAB.TABSCHEMA, TAB.TABNAME;

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.

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 TAB.TABSCHEMA AS SCHEMA_NAME ,
COUNT(*) AS PK_COUNT
FROM SYSCAT.TABLES TAB
INNER JOIN SYSCAT.TABCONST CONST ON
CONST.TABSCHEMA = TAB.TABSCHEMA
AND CONST.TABNAME = TAB.TABNAME
AND CONST.TYPE = 'P'
INNER JOIN SYSCAT.KEYCOLUSE KEY ON
CONST.TABSCHEMA = KEY.TABSCHEMA
AND CONST.TABNAME = KEY.TABNAME
AND CONST.CONSTNAME = KEY.CONSTNAME
WHERE TAB.TYPE = 'T'
AND TAB.TABSCHEMA NOT LIKE 'SYS%'
GROUP BY TAB.TABSCHEMA
ORDER BY TAB.TABSCHEMA;

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.

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 TAB.TABSCHEMA AS SCHEMA_NAME ,
TAB.TABNAME AS TABLE_NAME ,
'PRIMARY' AS CONSTRAINT_NAME ,
KEY.COLNAME AS COLUMN_NAME ,
KEY.COLSEQ AS POSITION
FROM SYSCAT.TABLES TAB
INNER JOIN SYSCAT.TABCONST CONST ON
CONST.TABSCHEMA = TAB.TABSCHEMA
AND CONST.TABNAME = TAB.TABNAME
AND CONST.TYPE = 'P'
INNER JOIN SYSCAT.KEYCOLUSE KEY ON
CONST.TABSCHEMA = KEY.TABSCHEMA
AND CONST.TABNAME = KEY.TABNAME
AND CONST.CONSTNAME = KEY.CONSTNAME
WHERE TAB.TYPE = 'T'
AND TAB.TABSCHEMA NOT LIKE 'SYS%'
ORDER BY TAB.TABSCHEMA, TAB.TABNAME,
KEY.COLNAME, KEY.COLSEQ;

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.

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 TABSCHEMA AS SCHEMA_NAME ,
COUNT(*) AS FK_COUNT
FROM SYSCAT.REFERENCES
WHERE TABSCHEMA NOT LIKE 'SYS%'
GROUP BY TABSCHEMA
ORDER BY TABSCHEMA;

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.

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 REF.REFTABSCHEMA AS SCHEMA_NAME ,
REF.REFTABNAME AS TABLE_NAME ,
TRIM(KEY.COLNAME) AS COLUMN_NAME ,
REF.CONSTNAME AS FK_CONST_NAME ,
REF.TABNAME AS FOREIGN_TABLE_NAME
FROM SYSCAT.REFERENCES REF
LEFT OUTER JOIN SYSCAT.KEYCOLUSE KEY 
ON KEY.TABSCHEMA = REF.TABSCHEMA
AND KEY.TABNAME = REF.TABNAME
AND KEY.CONSTNAME = REF.CONSTNAME
WHERE REF.TABSCHEMA NOT LIKE 'SYS%'
ORDER BY REF.REFTABSCHEMA, REF.REFTABNAME,
REF.CONSTNAME, REF.TABNAME,
FK_COLUMN_NAME;

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.

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 IND.TABSCHEMA AS SCHEMA_NAME ,
COUNT(COLS.COLNAME) AS UNIQUE_COUNT
FROM SYSCAT.INDEXES IND
JOIN SYSCAT.INDEXCOLUSE COLS ON
IND.INDNAME = COLS.INDNAME
AND IND.INDSCHEMA = COLS.INDSCHEMA
WHERE IND.TABSCHEMA NOT LIKE 'SYS%'
AND IND.UNIQUERULE IN ('U')
GROUP BY IND.TABSCHEMA
ORDER BY SCHEMA_NAME;

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.

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 IND.TABSCHEMA AS SCHEMA_NAME ,
IND.TABNAME AS TABLE_NAME ,
IND.INDNAME AS CONSTRAINT_NAME ,
'UNIQUE INDEX' AS CONSTRAINT_TYPE ,
COLS.COLNAME AS COLUMN_NAME
FROM SYSCAT.INDEXES IND
JOIN SYSCAT.INDEXCOLUSE COLS
ON IND.INDNAME = COLS.INDNAME
AND IND.INDSCHEMA = COLS.INDSCHEMA
WHERE IND.TABSCHEMA NOT LIKE 'SYS%'
AND IND.UNIQUERULE IN ('U')
ORDER BY SCHEMA_NAME, IND.TABNAME ,
IND.INDNAME, COLS.COLNAME;

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.

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 IND.TABSCHEMA AS SCHEMA_NAME ,
COUNT(COLS.COLNAME) AS INDEX_COUNT
FROM SYSCAT.INDEXES IND
JOIN SYSCAT.INDEXCOLUSE COLS ON IND.INDNAME = COLS.INDNAME
AND IND.INDSCHEMA = COLS.INDSCHEMA
WHERE IND.TABSCHEMA NOT LIKE 'SYS%'
AND IND.UNIQUERULE IN ('D')
GROUP BY IND.TABSCHEMA
ORDER BY SCHEMA_NAME;

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.

SELECT IND.CREATOR AS SCHEMA_NAME ,
IND.TBNAME AS TABLE_NAME ,
IND.NAME AS INDEX_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 IND.TABSCHEMA AS SCHEMA_NAME ,
IND.TABNAME AS TABLE_NAME ,
IND.INDNAME AS INDEX_NAME ,
'NON-UNIQUE INDEX' AS CONSTRAINT_TYPE,
COLS.COLNAME AS COLUMN_NAME
FROM SYSCAT.INDEXES IND
JOIN SYSCAT.INDEXCOLUSE COLS 
ON IND.INDNAME = COLS.INDNAME
AND IND.INDSCHEMA = COLS.INDSCHEMA
WHERE IND.TABSCHEMA NOT LIKE 'SYS%'
AND IND.UNIQUERULE IN ('D')
ORDER BY SCHEMA_NAME , IND.TABNAME ,
IND.INDNAME , COLS.COLNAME;

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.

SELECT SCHEMA AS SCHEMA_NAME ,
COUNT(*) AS TRIGGER_COUNT
FROM SYSIBM.SYSTRIGGERS
WHERE SCHEMA NOT LIKE 'SYS%'
GROUP BY SCHEMA
ORDER BY SCHEMA;

SELECT TABSCHEMA AS TABLE_SCHEMA ,
COUNT(TRIGNAME) AS TRIGGER_COUNT
FROM SYSCAT.TRIGGERS T
WHERE TABSCHEMA NOT LIKE 'SYS%'
GROUP BY TABSCHEMA
ORDER BY TABSCHEMA;

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.

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 , TBNAME ,
NAME ;

SELECT TABSCHEMA AS TABLE_SCHEMA ,
TRIGNAME AS TRIGGER_NAME ,
TABNAME AS TABLE_NAME ,
CASE TRIGTIME
WHEN 'B' THEN 'BEFORE'
WHEN 'A' THEN 'AFTER'
WHEN 'I' THEN 'INSTEAD OF'
END AS ACTIVATION
,RTRIM(
CASE WHEN EVENTUPDATE ='Y' THEN 'UPDATE ' ELSE ''
END
||
CASE WHEN EVENTDELETE ='Y' THEN 'DELETE ' ELSE ''
END
||
CASE WHEN EVENTINSERT ='Y' THEN 'INSERT ' ELSE ''
END
) AS EVENT
FROM     SYSCAT.TRIGGERS T
WHERE    TABSCHEMA NOT LIKE 'SYS%'
ORDER BY TABLE_SCHEMA , TABLE_NAME ,
TRIGGER_NAME ;

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.

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 ROUTINESCHEMA AS SCHEMA_NAME ,
COUNT(*) AS PROC_COUNT
FROM SYSCAT.ROUTINES
WHERE ROUTINETYPE = 'P'
AND ROUTINESCHEMA NOT LIKE 'SYS%'
AND ROUTINESCHEMA NOT LIKE 'SQLJ%'
GROUP BY ROUTINESCHEMA
ORDER BY ROUTINESCHEMA;

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.

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 ROUTINESCHEMA AS SCHEMA_NAME ,
ROUTINENAME AS PROCEDURE_NAME
FROM SYSCAT.ROUTINES
WHERE ROUTINETYPE = 'P'
AND ROUTINESCHEMA NOT LIKE 'SYS%'
AND ROUTINESCHEMA NOT LIKE 'SQLJ%'
ORDER BY SCHEMA_NAME , PROCEDURE_NAME;

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.

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 ROUTINESCHEMA AS SCHEMA_NAME ,
COUNT(*) AS PROC_COUNT
FROM SYSCAT.ROUTINES
WHERE ROUTINETYPE = 'F'
AND ROUTINESCHEMA NOT LIKE 'SYS%'
AND ROUTINESCHEMA NOT LIKE 'SQLJ%'
GROUP BY ROUTINESCHEMA
ORDER BY ROUTINESCHEMA;

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.

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 ROUTINESCHEMA AS SCHEMA_NAME ,
ROUTINENAME AS PROCEDURE_NAME
FROM SYSCAT.ROUTINES
WHERE ROUTINETYPE = 'F'
AND ROUTINESCHEMA NOT LIKE 'SYS%'
AND ROUTINESCHEMA NOT LIKE 'SQLJ%'
ORDER BY SCHEMA_NAME , PROCEDURE_NAME;

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.

SELECT SCHEMA AS SCHEMA_NAME ,
COUNT(*) AS SEQ_COUNT
FROM SYSIBM.SYSSEQUENCES
WHERE SCHEMA NOT LIKE 'SYS%'
AND SEQTYPE = 'S'
GROUP BY SCHEMA
ORDER BY SCHEMA;

SELECT SEQSCHEMA AS SCHEMA_NAME ,
COUNT(*) AS SEQ_COUNT
FROM SYSCAT.SEQUENCES
WHERE SEQSCHEMA NOT LIKE 'SYS%'
AND SEQTYPE = 'S'
GROUP BY SEQSCHEMA
ORDER BY SEQSCHEMA;

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.

SELECT SCHEMA AS SCHEMA_NAME ,
NAME AS SEQ_NAME ,
CYCLE ,
ORDER ,
CACHE
FROM SYSIBM.SYSSEQUENCES
WHERE SCHEMA NOT LIKE 'SYS%'
AND SEQTYPE = 'S'
ORDER BY SCHEMA, NAME ;

SELECT SEQSCHEMA AS SCHEMA_NAME ,
SEQNAME AS SEQ_NAME ,
CYCLE ,
ORDER ,
CACHE
FROM SYSCAT.SEQUENCES
WHERE SEQSCHEMA NOT LIKE 'SYS%'
AND SEQTYPE = 'S'
ORDER BY SEQSCHEMA , SEQNAME;

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.