Close Menu

    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:

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.

    Db2 for z/OS SQL statement
    RDS for Db2 SQL statement

    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.
     

    Source: Read More

    Related Posts

    Leave A Reply