AWS Database Blog

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

Customers are modernizing their mission-critical legacy on-premises IBM Db2 for z/OS databases to Amazon Relational Database Service (Amazon RDS) for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition for its scalability, performance, agility, and availability.
You can use the AWS Schema Conversion Tool (AWS SCT) to simplify the schema conversion from Db2 for z/OS to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition. For details about converting schema objects with the AWS SCT for Db2 z/OS as a source, refer to AWS SCT now supports IBM DB2 for z/OS as a source. After converting the schema objects using the AWS SCT, you can use AWS Database Migration Service (AWS DMS) to migrate data. As the schema migration is a semi-automatic process, it may still need manual conversion sometimes when the objects are not converted fully or when a mismatch of key object features occurs.

In this post, we walk you through how to validate database schema objects migrated from Db2 for z/OS to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition. A similar validation between Db2 for z/OS to Amazon RDS for MySQL or Amazon Aurora MySQL-Compatible Edition will be covered in another post.

Objects to validate

As best practice you should perform schema validation after you successfully convert your database schema objects from Db2 for z/OS and deploy the converted schema objects in PostgreSQL using the AWS SCT. Post-migration schema validation determines the success factor of the overall migration.

In the following sections, we go through the validation scenarios for each of the following database schema object types in detail to ensure that the number of objects for each object type remains consistent between source and target databases. These validation scenarios do not cover or explore the conversion accuracy of your migration, they validate the migrated database objects directly.

  • Schemas
  • Tables
  • Views
  • Primary keys
  • Foreign keys
  • Indexes
  • Materialized query tables
  • User-defined data types
  • Triggers
  • Sequences
  • Procedures
  • Functions

Schemas

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

Db2 for z/OS Query PostgreSQL Query 
SELECT DISTINCT CREATOR AS SCHEMA_NAME 
FROM SYSIBM.SYSTABLES 
WHERE CREATOR NOT LIKE 'SYS%' 
ORDER BY SCHEMA_NAME;
 
SELECT SCHEMA_NAME, SCHEMA_OWNER 
FROM INFORMATION_SCHEMA.SCHEMATA 
WHERE SCHEMA_NAME NOT IN ('pg_catalog',
'information_schema', 
'aws_db2zos_ext','public') 
AND SCHEMA_NAME NOT LIKE 'pg_temp%' 
AND SCHEMA_NAME NOT LIKE 'pg_toast%' 
ORDER BY SCHEMA_NAME;

When you convert your Db2 for z/OS schema, the AWS SCT adds an additional schema (aws_db2zos_ext) to your target database. These schemas implement SQL system functions of the Db2 for z/OS database that are required when writing the converted schema to your Aurora PostgreSQL database. These additional schemas are called the AWS SCT extension pack.

We exclude schemas related to system or catalog tables (SYS%) in Db2 for z/OS and in PostgreSQL (pg_catalog,information_schema, public).

Verify if the number of schemas in the source and target database matches. If any differences are found, look at the AWS SCT logs to identify reasons for failure or create it manually.

Tables

The AWS SCT converts source Db2 for z/OS tables to the equivalent target (PostgreSQL) tables. If required, we can use custom mapping rules to include or exclude specific tables from migration. The following scripts return the counts and detail-level information for all the tables:

Db2 for z/OS Query PostgreSQL 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 NSPNAME as schema_name,
count(RELNAME) as table_count
FROM PG_CLASS C
LEFT JOIN PG_NAMESPACE N 
ON N.OID = C.RELNAMESPACE
WHERE C.RELKIND in (‘r’)
AND N.NSPNAME
not in 
('pg_catalog',
'information_schema',
'aws_db2zos_ext','public')
group by NSPNAME
ORDER BY NSPNAME;

For detail-level information, use the following queries:

Db2 for z/OS Query PostgreSQL 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 NSPNAME as schema_name,
RELNAME as table_name
FROM PG_CLASS C
LEFT JOIN PG_NAMESPACE N 
ON N.OID = C.RELNAMESPACE
WHERE C.RELKIND in (‘r’)
AND N.NSPNAME
not in ('pg_catalog', 
'information_schema',
'aws_db2zos_ext',
'public') 
order by NSPNAME,RELNAME;

Verify the results from the source and target databases. If you see any differences, identify the reason from the AWS SCT or manual logs and rerun the failed statement after fixing the problem.

Views

You can validate the views count converted by the AWS SCT with the following queries on the source and target databases:

Db2 for z/OS Query PostgreSQL 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 NSPNAME as schema_name,
COUNT(RELNAME) as view_count
FROM PG_CLASS C
LEFT JOIN PG_NAMESPACE N 
ON N.OID = C.RELNAMESPACE
WHERE C.RELKIND in ('v')
AND N.NSPNAME
not in ('pg_catalog',
'information_schema',
'aws_db2zos_ext',
'public')
group by NSPNAME
order by NSPNAME;

For detail-level information, use the following queries:

Db2 for z/OS Query PostgreSQL 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 NSPNAME as schema_name,
RELNAME as view_name
FROM PG_CLASS C
LEFT JOIN PG_NAMESPACE N 
ON N.OID = C.RELNAMESPACE
WHERE C.RELKIND in ('v')
AND N.NSPNAME
not in ('pg_catalog', 
'information_schema',
'aws_db2zos_ext',
'public')
order by NSPNAME,RELNAME;

Verify the count and details between the source and target using these queries. If any differences are found, identify the cause and fix the differences.

Primary keys

Primary keys allow you to have unique values for columns, which prevents information from being duplicated. This key helps improve the search based on the key values and avoid table scans.

The following queries help you extract the counts and details of primary keys in the source and target databases:

Db2 for z/OS Query PostgreSQL Query 
SELECT TBCREATOR AS SCHEMA_NAME,
SUM(COLCOUNT) AS PK_COUNT
FROM SYSIBM.SYSTABCONST
WHERE TBCREATOR NOT LIKE 'SYS%'
AND TYPE='P'
GROUP BY TBCREATOR
Order by TBCREATOR;
 
select kcu.table_schema,
count(*) as pk_count
from information_schema.table_constraints tco
join information_schema.key_column_usage kcu
on kcu.constraint_name = tco.constraint_name
and kcu.constraint_schema = tco.constraint_schema
and kcu.constraint_name = tco.constraint_name
where tco.constraint_type = 'PRIMARY KEY'
and kcu.table_schema not in ('pg_catalog',
'information_schema'
,'aws_db2zos_ext', 'public')
group by kcu.table_schema
order by kcu.table_schema;

For detail-level information, use the following queries:

Db2 for z/OS Query PostgreSQL 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 TAB.CREATOR,
TAB.NAME,COL.NAME, COL.KEYSEQ;
 
select kcu.table_schema, 
kcu.table_name, 
tco.constraint_name, 
kcu.column_name, 
kcu.ordinal_position as position 
from information_schema.table_constraints tco 
join 
information_schema.key_column_usage kcu 
on kcu.constraint_name = tco.constraint_name 
and kcu.constraint_schema = tco.constraint_schema 
and kcu.constraint_name = tco.constraint_name 
where tco.constraint_type = 'PRIMARY KEY' 
and kcu.table_schema 
not in ('pg_catalog',
'information_schema', 
'aws_db2zos_ext','public') 
order by kcu.table_schema,kcu.table_name, 
tco.constraint_name,kcu.column_name, 
kcu.ordinal_position;

Verify the count and details of the primary keys between the source and target using these queries. If any differences are found, identify the cause through the deployment logs and fix the differences.

Foreign keys

Foreign keys help you maintain referential integrity between tables. These keys should be turned off on the target before performing data migration using AWS DMS full load migration. For more information, see Using a PostgreSQL database as a target for AWS Database Migration Service.

With the following queries, you get the counts and detail-level information about the foreign keys in both the source and target databases. You validate the foreign keys after completing the full load data migration using AWS DMS.

Db2 for z/OS Query PostgreSQL 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 tc.table_schema, 
count(*) as fk_count 
from information_schema.table_constraints tc 
join information_schema.key_column_usage kcu 
on kcu.constraint_name = tc.constraint_name 
and kcu.constraint_schema = tc.constraint_schema 
and kcu.constraint_name = tc.constraint_name 
where tc.constraint_type = 'FOREIGN KEY' 
and kcu.table_schema 
not in ('pg_catalog',
'information_schema', 
'aws_db2zos_ext','public') 
group by tc.table_schema 
order by tc.table_schema;

For detail-level information, use the following queries:

Db2 for z/OS Query PostgreSQL Query 
SELECT FK.CREATOR AS SCHEMA_NAME, 
REL.TBNAME AS TABLE_NAME, 
REL.RELNAME AS FK_CONST_NAME, 
REL.REFTBNAME AS FOREIGN_TABLE_NAME, 
FK.COLNAME AS FK_COLUMN_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;
 
SELECT tc.table_schema AS schema_name, 
tc.table_name AS table_name, 
tc.constraint_name AS fk_const_name, 
ccu.table_name AS foreign_table_name, 
kcu.column_name AS fk_column_name 
FROM information_schema.table_constraints AS tc 
JOIN information_schema.key_column_usage AS kcu 
ON tc.constraint_name = kcu.constraint_name 
AND tc.table_schema = kcu.table_schema 
JOIN information_schema.constraint_column_usage AS ccu 
ON ccu.constraint_name = tc.constraint_name 
AND ccu.table_schema = tc.table_schema 
WHERE tc.constraint_type = 'FOREIGN KEY' 
AND tc.table_schema 
not in ('pg_catalog', 
'information_schema', 
'aws_db2zos_ext', 'public') 
order by tc.table_schema, tc.table_name;

Indexes

Indexes are the database objects created based on one or more columns of a table. Indexes are used to improve the query performance and ensure uniqueness of data when defined as unique indexes.

Unique indexes

With unique keys, you can maintain the uniqueness of data in the column. With the following queries, you get the counts and detail-level information about the unique keys in both the source and target databases:

Db2 for z/OS Query PostgreSQL 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 sch.nspname as schema_name,
count(*) as unique_count 
FROM pg_index idx JOIN pg_class cls 
ON cls.oid=idx.indexrelid JOIN pg_class tab 
ON tab.oid=idx.indrelid 
and tab.RELISPARTITION = 'f' 
JOIN pg_namespace sch 
on sch.oid = tab.relnamespace 
JOIN pg_am am ON am.oid=cls.relam 
JOIN pg_indexes ids 
ON sch.nspname = ids.schemaname 
and ids.tablename = tab.relname 
and cls.relname = ids.indexname 
where idx.indisunique='t' 
and indisprimary='f' 
and sch.nspname 
not in ('pg_toast',
'pg_catalog','information_schema', 
'aws_db2zos_ext','public') 
group by sch.nspname 
order by sch.nspname;

For detail-level information, use the following queries:

Db2 for z/OS Query PostgreSQL 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
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'
ORDER BY IND.CREATOR, 
IND.TBNAME,IND.NAME, COL.COLNAME;
 
SELECT sch.nspname as schema_name,
tab.relname as table_name,
cls.relname as constraint_name,
ids.indexdef as definition
FROM pg_index idx
JOIN pg_class cls 
ON cls.oid=idx.indexrelid
JOIN pg_class tab 
ON tab.oid=idx.indrelid 
and tab.RELISPARTITION = 'f'
JOIN pg_namespace sch 
on sch.oid = tab.relnamespace
JOIN pg_am am ON am.oid=cls.relam
JOIN pg_indexes ids 
ON sch.nspname = ids.schemaname
and ids.tablename = tab.relname
and cls.relname = ids.indexname
where idx.indisunique='t'
and indisprimary='f'
and sch.nspname 
not in ('pg_toast',
'pg_catalog',
'information_schema',
'aws_db2zos_ext',
'public')
order by sch.nspname;

Non-unique 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 for z/OS and PostgreSQL databases based on different use cases, so index counts also may differ. The index count may also differ due to the limitation of partitioned tables in PostgreSQL.

Use the following queries:

Db2 for z/OS Query PostgreSQL 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 sch.nspname,count(*) as index_count
FROM pg_index idx
JOIN pg_class cls 
ON cls.oid=idx.indexrelid
JOIN pg_class tab 
ON tab.oid=idx.indrelid 
and tab.RELISPARTITION = 'f'
JOIN pg_namespace sch 
on sch.oid = tab.relnamespace
JOIN pg_am am ON am.oid=cls.relam
JOIN pg_indexes ids 
ON sch.nspname = ids.schemaname
and ids.tablename = tab.relname
and cls.relname = ids.indexname
where idx.indisunique='f'
and indisprimary='f'
and sch.nspname 
not in ('pg_toast',
'pg_catalog',
'information_schema',
'aws_db2zos_ext',
'public',
'db2inst1')
group by sch.nspname
order by sch.nspname;

For detail-level information, use the following queries:

Db2 for z/OS Query PostgreSQL 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 ('D')
ORDER BY IND.CREATOR, 
IND.TBNAME,IND.NAME, COL.COLNAME;
 
SELECT sch.nspname as schema_name,
tab.relname as tabl_name,
cls.relname as constraint_name,
ids.indexdef as definition
FROM pg_index idx
JOIN pg_class cls 
ON cls.oid=idx.indexrelid
JOIN pg_class tab 
ON tab.oid=idx.indrelid 
and tab.RELISPARTITION = 'f'
JOIN pg_namespace sch 
on sch.oid = tab.relnamespace
JOIN pg_am am ON am.oid=cls.relam
JOIN pg_indexes ids ON 
sch.nspname = ids.schemaname
and ids.tablename = tab.relname
and cls.relname = ids.indexname
where idx.indisunique='f'
and indisprimary='f'
and sch.nspname 
not in ('pg_toast',
'pg_catalog',
'information_schema',
'aws_db2zos_ext',
'public')
order by sch.nspname;

Verify the count and detail of indexes between the source and target database, and any differences should either be attributed to a known reason or investigated and fixed based on the deployment logs.

Materialized query tables

Materialized query tables from Db2 for z/OS are migrated as materialized views in PostgreSQL. They’re similar to regular views, except that the materialized query tables persist the results in a table-like form. This improves query performance because the data is readily available to be returned. You can use the following queries to compare the objects between source and target:

Db2 for z/OS Query PostgreSQL Query 
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 schemaname,count(*) as mq_count
from pg_matviews
where schemaname NOT IN ('information_schema',
'pg_catalog', 'public','aws_db2zos_ext')
group by schemaname;

For detail-level information, use the following queries:

Db2 for z/OS Query PostgreSQL Query 
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 schemaname,matviewname as mq_name
from pg_matviews
where schemaname NOT IN ('information_schema',
'pg_catalog', 'public','aws_db2zos_ext');

Verify the count and detail of materialized query tables and materialized views between the source and target database, and any differences should be investigated and fixed based on the deployment logs.

User-defined data types

The AWS SCT migrates custom data types from Db2 for z/OS to PostgreSQL as types. You can use the following queries to compare the objects between source and target:

Db2 for z/OS Query PostgreSQL Query 
SELECT SCHEMA AS SCHEMA_NAME,
COUNT(*) AS UDT_COUNT
FROM SYSIBM.SYSDATATYPES
WHERE SCHEMA NOT LIKE 'SYS%'
GROUP BY SCHEMA
ORDER BY SCHEMA;
 
SELECT n.nspname as schema_name,
count(*) as udt_count
FROM pg_catalog.pg_type t
JOIN pg_catalog.pg_namespace n
ON n.oid = t.typnamespace
WHERE ( t.typrelid = 0
OR ( SELECT c.relkind = 'c'
FROM pg_catalog.pg_class c
WHERE c.oid = t.typrelid ) )
AND NOT EXISTS (
SELECT 1
FROM pg_catalog.pg_type el
WHERE el.oid = t.typelem
AND el.typarray = t.oid )
AND n.nspname NOT IN ('information_schema',
'pg_toast',
'pg_catalog', 'public',
'aws_db2zos_ext')
group by n.nspname
order by n.nspname;

For detail-level information, use the following queries:

Db2 for z/OS Query PostgreSQL Query 
SELECT SCHEMA AS SCHEMA_NAME,
NAME AS UDT_NAME
FROM SYSIBM.SYSDATATYPES
WHERE SCHEMA NOT LIKE 'SYS%'
ORDER BY SCHEMA;
 
SELECT n.nspname as schema_name,
t.typname as udt_name
FROM pg_catalog.pg_type t
JOIN pg_catalog.pg_namespace n
ON n.oid = t.typnamespace
WHERE ( t.typrelid = 0
OR ( SELECT c.relkind = 'c'
FROM pg_catalog.pg_class c
WHERE c.oid = t.typrelid ) )
AND NOT EXISTS (
SELECT 1
FROM pg_catalog.pg_type el
WHERE el.oid = t.typelem
AND el.typarray = t.oid )
AND n.nspname NOT IN ('information_schema',
'pg_toast',
'pg_catalog', 'public','aws_db2zos_ext')
order by n.nspname;

Verify the count and detail of user-defined types between the source and target databases, and any differences should be investigated and fixed based on the deployment logs.

Triggers

Triggers can help you audit databases, implement a business rule, or implement referential integrity. They can also impact performance based on usage in appropriate areas. The following queries give you the count and details of triggers for both the source and target databases:

Db2 for z/OS Query PostgreSQL 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 trigger_schema AS SchemaName,
Count(trigger_name) AS TriggerCount
FROM information_schema.TRIGGERS
WHERE trigger_schema NOT IN ('aws_db2zos_ext', 
'pg_catalog')
GROUP BY trigger_schema
ORDER BY trigger_schema;

For detail-level information, use the following queries:

Db2 for z/OS Query PostgreSQL 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 trigger_schema AS TriggerSchemaName,
trigger_name,
event_object_schema AS TableSchema,
event_object_table AS TableName,
event_manipulation AS TriggerType
FROM information_schema.TRIGGERS
WHERE trigger_schema NOT IN (
'aws_db2zos_ext',
'pg_catalog')
ORDER BY trigger_schema,
trigger_name;

In PostgreSQL, triggers are implemented by defining a trigger function before the trigger itself is created. The trigger function must be declared as a function taking no arguments and returning type trigger. The trigger count between Db2 for z/OS and PostgreSQL could vary because of the way they are implemented in PostgreSQL. Verify the count and detail of triggers between the source and target databases, and any differences should either be attributed to a known reason or investigated and fixed based on the deployment logs.

Sequences

Sequences help you create and increment integer values for columns based on given ranges and order. Unlike identity columns, sequences aren’t associated with specific tables. Applications refer to a sequence object to retrieve its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values across multiple rows and tables.

The following queries help you get the counts and detail-level information of sequences available in the source and target databases:

Db2 for z/OS Query PostgreSQL Query 
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 n.nspname as schema_namee
,count(*) as seq_count
from pg_sequence seq
join pg_class seqc 
on seq.seqrelid = seqc.oid
join pg_namespace n 
on seqc.relnamespace = n.oid
where n.nspname 
NOT IN ( 'pg_catalog', 
'information_schema',
'aws_db2zos_ext', 'public')
group by n.nspname
order by n.nspname ;

For detail-level information, use the following queries:

Db2 for z/OS Query PostgreSQL Query 
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 n.nspname as schema_namee
,seqc.relname as seqname,
seqcycle as cycle,
seqcache as cache
from pg_sequence seq
join pg_class seqc 
on seq.seqrelid = seqc.oid
join pg_namespace n 
on seqc.relnamespace = n.oid
where n.nspname 
NOT IN ( 'pg_catalog', 
'information_schema',
'aws_db2zos_ext', 'public')
order by n.nspname,seqc.relname;

Verify the count and details of sequences between source and target, but it’s also important that you set the sequence to the correct values after migration. Setting the sequence is important because after sequences are migrated from the source to target database, they start with the minvalue of the sequence and can cause duplicate key errors during insert and update statements.

Procedures

Db2 for z/OS standard stored procedures encapsulate business logic and run related DDL or DML operations in a single unit of work. In PostgreSQL, we use functions over stored procedures, owing to the limitations of procedures. In those scenarios the source Db2 procedures count also gets added to the function count. In both the source and target databases, the following queries provide counts and detail-level information about the procedures:

Db2 for z/OS Query PostgreSQL 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 n.nspname AS SchemaName,
Count(p.proname) AS procCount
FROM pg_proc p
join pg_namespace n
ON p.pronamespace = n.oid
WHERE n.nspname NOT IN ( 'pg_catalog', 
'information_schema',
'aws_db2zos_ext', 'public')
AND p.prokind = 'p'
GROUP BY n.nspname
ORDER BY n.nspname;

For detail-level information, use the following queries:

Db2 for z/OS Query PostgreSQL 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 n.nspname AS SchemaName,
p.proname AS procedure_name
FROM pg_proc p
join pg_namespace n
ON p.pronamespace = n.oid
WHERE n.nspname NOT IN ( 'pg_catalog', 
'information_schema',
'aws_db2zos_ext', 'public')
AND p.prokind = 'p'
GROUP BY n.nspname, p.proname
ORDER BY n.nspname, p.proname;

Functions

In Db2 for z/OS, SQL functions implement specific business or functional logic on input parameters and return certain types of predefined output. In PostgreSQL, because functions are the preferred choice to implement business and functional logic, their count is usually higher than Db2 for z/OS. In both the source and target databases, the following queries provide counts and detail-level information about the functions:

Db2 for z/OS Query PostgreSQL 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 n.nspname AS SchemaName,
Count(p.proname) AS func_Count
FROM pg_proc p
join pg_namespace n
ON p.pronamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 
'information_schema',
'aws_db2zos_ext', 'public')
AND p.prokind = 'f'
GROUP BY n.nspname
ORDER BY n.nspname;

For detail-level information, use the following queries:

Db2 for z/OS Query PostgreSQL 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 n.nspname AS SchemaName,
p.proname AS function_name
FROM pg_proc p
join pg_namespace n
ON p.pronamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 
'information_schema',
'aws_db2zos_ext', 'public')
AND p.prokind = 'f'
GROUP BY n.nspname, p.proname
ORDER BY n.nspname, p.proname;

Useful PostgreSQL catalog tables

The following table summarizes some helpful Db2 for z/OS and their corresponding PostgreSQL system and catalog tables and views. These tables and views contain metadata with respect to various objects present in the database and are used for database object validation.

Db2 for z/OS PostgreSQL Use Case
sysibm.systables/ sysibm.syscolumns

pg_tables

/information_schema.tables

 Look for various table properties
sysibm.systables/ sysibm.syscolumns Pg_views/information_schema.views Look for different properties of views
sysibm.sysindexes/ sysibm.syskeys pg_indexes/pg_index Gather details about indexes
sysibm.sysroutines pg_proc Gather details about procedures, functions, and trigger functions
sysibm.systriggers information_schema.triggers Gather details about triggers
sysibm.syssequences pg_sequence/information_schema.sequences Gather details about sequence, and identity or serial columns
sysibm.systables pg_matviews Find more details about materialized views
sysibm.sysdatatypes pg_type Gather more information about custom data types

Handling objects not supported in PostgreSQL

You must manually perform migration of Db2 for z/OS objects not supported by PostgreSQL. These will need to be implemented differently than in DB2 for z/OS to emulate the same function as source. So manual validation would be required to be performed on such objects.

Conclusion

In this post, we discussed validation of database objects with metadata queries for Db2 for z/OS and Aurora PostgreSQL-Compatible Edition or RDS for PostgreSQL databases. Database object validation is an essential step that provides an in-depth view into migration accuracy and confirms whether all database objects are migrated appropriately. The database validation phase confirms the integrity of the target database and ensures a smoother transition for the integration of the dependent application processes.

Database migration should always be followed by unit testing, functional testing, and regression testing for all the objects irrespective of automatic or manual migration as this assures that all migrated objects confirm to standards and are working as intended. This saves a lot of reworks when you conduct integration testing with your applications.

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.

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.

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.