Validate database objects after migrating from IBM Db2 LUW to Amazon Aurora PostgreSQL or Amazon RDS for PostgreSQL

{"value":"Heterogeneous database migration is a multistage process, which usually includes assessment, database schema conversion, data migration, functional testing, performance tuning, and many other steps spanning across multiple teams. Migration from IBM Db2 LUW to [Amazon Aurora PostgreSQL-Compatible Edition](https://aws.amazon.com/rds/aurora/postgresql-features/) or [Amazon Relational Database Service (Amazon RDS) for PostgreSQL](https://aws.amazon.com/rds/postgresql/) is heterogeneous in nature, and traditionally follows similar phases.\n\nAmazon Web Services provides tools and services like [Amazon Web Services Schema Conversion Tool](https://aws.amazon.com/dms/schema-conversion-tool/) (Amazon Web Services SCT), which simplifies schema conversion for heterogeneous database migration, and Amazon Web Services Database Migration Service (Amazon Web Services DMS), which helps you migrate data to Amazon Web Services quickly and securely while minimizing downtime.\n\nAmazon Web Services SCT generates an assessment report showing the percentage of Db2 code that is converted to PostgreSQL automatically and the percentage of code that requires manual effort for conversion with detailed action items. Because schema migration with Amazon Web Services SCT isn’t a fully automated process, there is always a chance of missing objects or key object features in the target database. Schema validation is a crucial milestone that prevents slippage of any issues from the schema conversion process to other stages of the database migration.\n\nIn this post, we walk you through how to validate database schema objects migrated from Db2 LUW to Amazon RDS for PostgreSQL or Aurora PostgreSQL.\n\n\n#### **When and what objects to validate**\n\n\nYou should perform schema validation after you successfully convert your schema from Db2 LUW and deploy the converted schema in PostgreSQL using Amazon Web Services SCT or other conversion tools.\n\nThe following list shows the database objects in Db2 LUW (source) and Aurora PostgreSQL (target) that you should validate during database migration:\n\n- Schemas\n- Tables\n- Views\n- Primary keys\n- Foreign keys\n- Indexes\n- Materialized query tables\n- User-defined data types\n- Triggers\n- Sequences\n- Procedures\n- Functions\n\nIn the following sections, we go through validation scenarios for each object type 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 the accuracy of the conversion.\n\n\n#### **Schemas**\n\n\nSchemas 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 SQL queries.\n\n\n\n\nWhen you convert your Db2 LUW schema, Amazon Web Services SCT adds additional schemas (aws_db2_ext and aws_db2_ext_data) to your target database. These schemas implement SQL system functions of the Db2 LUW database that are required when writing the converted schema to your Aurora PostgreSQL database. These additional schemas are called the [Amazon Web Services SCT extension pack](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_ExtensionPack.html).\nWe exclude schemas related to system or catalog tables (SYS%,’SQLJ‘, ‘NULLID‘) in Db2 LUW and in PostgreSQL (‘pg_catalog‘,’information_schema‘, ‘public‘). We also exclude schemas related to specific functionality in Aurora PostgreSQL ([aws_commons](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL-Lambda.html%22%20/l%20%22PostgreSQL-Lambda-install-extension), [aws_lambda](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL-Lambda.html#PostgreSQL-Lambda-install-extension)).\nYou should verify the number of schemas in the source and target database matches. If any differences are found, you should look at the [Amazon Web Services SCT logs](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_BestPractices.html) to identify reasons for failure or create it manually.\n\n\n#### **Tables**\n\n\nAmazon Web Services SCT converts source Db2 LUW tables to the equivalent target (PostgreSQL) tables. If required, we can use custom [mapping rules](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Mapping.html) to include or exclude specific tables from migration. The following scripts return the counts and detail-level information for all the tables:\n\n\n\n\nWe added the condition C.RELISPARTITION = 'f' to filter out the partition table in PostgreSQL because IBM Db2 doesn’t list table partitions as separate tables. It’s important to note that PostgreSQL has several restrictions on [partition tables](https://www.postgresql.org/docs/13/ddl-partitioning.html) that might impact object counts for primary keys, foreign keys, and indexes.\n\nFor detail-level information, use the following queries:\n\n\n\nVerify the results from the source and target databases. If you see any differences, identify the reason from the Amazon Web Services SCT or manual logs and rerun the failed statement after fixing the problem.\n\n\n#### **Views**\n\n\nYou can validate the views count converted by Amazon Web Services SCT with the following queries on the source and target databases:\n\n\n\n\nFor detail-level information, use the following:\n\n\n\nYou should verify the count and details between the source and target using this SQL. If any differences are found, identify the cause and fix the differences.\n\n\n#### **Primary keys**\n\n\nAlong with database object validation, you need to ensure the data is consistent and bound to integrity. Different types of [constraints](https://www.postgresql.org/docs/13/ddl-constraints.html) provide you with the flexibility to control and check the data during insertion to avoid runtime data integrity issues.\n\nPrimary keys allow you to have unique values for columns, which prevent information from being duplicated, following the normalization process. This key helps improve the search based on the key values and avoid table scans.\n\nThe following queries help you extract the counts and details of primary keys in the source and target databases:\n\n\n\nFor detail-level information, use the following query:\n\n\n\nYou should verify the count and details of the primary keys between the source and target using this SQL. If any differences are found, identify the cause through the deployment logs and fix the differences.\n\n\n#### **Foreign keys**\n\n\nForeign keys help you maintain referential integrity between tables. These keys should be turned off on the target before performing data migration using Amazon Web Services DMS full load migration. For more information, see [Using a PostgreSQL database as a target for Amazon Web Services Database Migration Service.](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.PostgreSQL.html)\nWith 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 Amazon Web Services DMS.\n\n\n\nFor detail-level information, use the following query:\n\n\n\nPostgreSQL version 11 has [limitations](https://www.postgresql.org/docs/11/ddl-partitioning.html)with respect to foreign keys on partitioned tables, but many of those limitations are overcome in version 12 and onwards. You should keep these limitations in mind when verifying the count and detail of foreign keys between the source and target database.\n\n\n#### **Indexes** \n\n\nIndexes 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.\n\n\n#### **Unique indexes**\n\n\nWith 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:\n\n\n\n\nFor detail-level information, use the following query:\n\n\n\n\n#### **Non-unique indexes**\n\n\n[Indexes ](https://www.postgresql.org/docs/13/indexes.html)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 LUW 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.\n\n\n\nFor detail-level information, use the following query:\n\n\n\nYou should 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.\n\n\n#### **Materialized query tables**\n\n\nMaterialized query tables from Db2 LUW are migrated as [materialized views](https://www.postgresql.org/docs/13/rules-materializedviews.html) 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:\n\n\n\nFor detail-level information, use the following query:\n\n\n\nYou should 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.\n\n\n#### **User-defined data types**\n\n\nAmazon Web Services SCT migrates custom data types from Db2 LUW to PostgreSQL as [types](https://www.postgresql.org/docs/13/sql-createtype.html). You can use the following queries to compare the objects between source and target:\n\n\n\nYou should 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.\n\n\n#### **Triggers**\n\n\n[Triggers](https://www.postgresql.org/docs/13/sql-createtrigger.html) 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:\n\n\n\nFor detail-level information, use the following query:\n\n\n\nThe trigger count between Db2 LUW and PostgreSQL could vary because of the way triggers are [implemented](https://www.postgresql.org/docs/13/trigger-definition.html) in PostgreSQL. You should 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.\n\n#### **Sequences**\n\nSequences 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.\n\nThe following queries help you get the counts and detail-level information of sequences available in the source and target databases:\n\n\n\nFor detail-level information, use the following:\n\n\n\nYou should verify the count and details of sequences between source and target, but it’s also important that you [set the sequence](https://www.postgresql.org/docs/13/sql-altersequence.html) 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.\n\n#### **Procedures**\n\n\nDb2 LUW stored procedures encapsulate business logic and run related DDL or DML operations in a single unit of work. In PostgreSQL, we use [functions](https://www.postgresql.org/docs/13/xfunc.html)over stored procedures, owing to the limitations of [procedures](https://www.postgresql.org/docs/13/xproc.html). This count gets added to the already existing function count in the source database. In both the source and target databases, the following queries provide counts and detail-level information about the procedures:\n\n\n\nFor detail-level information, use the following query:\n\n\n\n\n#### **Functions**\n\n\nIn Db2 LUW, 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 more than Db2 LUW. In both the source and target databases, the following queries provide counts and detail-level information about the functions:\n\n\n\nFor detail-level information, use the following query:\n\n\n\n#### **Useful PostgreSQL catalog tables**\n\nThe following table summarizes some helpful Db2 LUW 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.\n\n\n\n#### **Handling objects not supported in PostgreSQL**\n\n\nYou must manually perform migration of Db2 LUW objects not supported by PostgreSQL. You can use the queries provided in this post to iteratively validate the migrated objects to identify gaps and fix them accordingly.\n\n\n#### **Conclusion**\n\n\nIn this post, we discussed validation of database objects with metadata queries for Db2 LUW and Aurora PostgreSQL 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 also confirms the integrity of the target database and ensures business continuity of the dependent application processes.\n\nYou should do a few rounds of unit testing as well as functional testing irrespective of whether an object is automatically or manually converted. This saves a lot of rework when you conduct integration testing with your applications.\n\nLet us know if you have any comments or questions. We value your feedback!\n\n#### **About the Authors**\n\n\n\n**Sai Parthasaradhi** is a Database Migration Specialist with Amazon Web Services Professional Services. He works closely with customers to help them migrate and modernize their databases on Amazon Web Services.\n\n\n\n**Rakesh Raghav** is a Lead Database Consultant with the Amazon Web Services Professional Services in India, helping customers have a successful cloud adoption and migration journey. He is passionate about building innovative solutions to accelerate their database journey to the cloud.\n\n\n\n**Veeranjaneyulu Grandhi** is a Database Consultant with Amazon Web Services. He works with customers to build scalable, highly available, and secure solutions in the Amazon Web Services Cloud. His focus area is homogenous and heterogeneous database migrations.","render":"<p>Heterogeneous database migration is a multistage process, which usually includes assessment, database schema conversion, data migration, functional testing, performance tuning, and many other steps spanning across multiple teams. Migration from IBM Db2 LUW to <a href=\"https://aws.amazon.com/rds/aurora/postgresql-features/\" target=\"_blank\">Amazon Aurora PostgreSQL-Compatible Edition</a> or <a href=\"https://aws.amazon.com/rds/postgresql/\" target=\"_blank\">Amazon Relational Database Service (Amazon RDS) for PostgreSQL</a> is heterogeneous in nature, and traditionally follows similar phases.</p>\n<p>Amazon Web Services provides tools and services like <a href=\"https://aws.amazon.com/dms/schema-conversion-tool/\" target=\"_blank\">Amazon Web Services Schema Conversion Tool</a> (Amazon Web Services SCT), which simplifies schema conversion for heterogeneous database migration, and Amazon Web Services Database Migration Service (Amazon Web Services DMS), which helps you migrate data to Amazon Web Services quickly and securely while minimizing downtime.</p>\n<p>Amazon Web Services SCT generates an assessment report showing the percentage of Db2 code that is converted to PostgreSQL automatically and the percentage of code that requires manual effort for conversion with detailed action items. Because schema migration with Amazon Web Services SCT isn’t a fully automated process, there is always a chance of missing objects or key object features in the target database. Schema validation is a crucial milestone that prevents slippage of any issues from the schema conversion process to other stages of the database migration.</p>\n<p>In this post, we walk you through how to validate database schema objects migrated from Db2 LUW to Amazon RDS for PostgreSQL or Aurora PostgreSQL.</p>\n<h4><a id=\"When_and_what_objects_to_validate_9\"></a><strong>When and what objects to validate</strong></h4>\n<p>You should perform schema validation after you successfully convert your schema from Db2 LUW and deploy the converted schema in PostgreSQL using Amazon Web Services SCT or other conversion tools.</p>\n<p>The following list shows the database objects in Db2 LUW (source) and Aurora PostgreSQL (target) that you should validate during database migration:</p>\n<ul>\n<li>Schemas</li>\n<li>Tables</li>\n<li>Views</li>\n<li>Primary keys</li>\n<li>Foreign keys</li>\n<li>Indexes</li>\n<li>Materialized query tables</li>\n<li>User-defined data types</li>\n<li>Triggers</li>\n<li>Sequences</li>\n<li>Procedures</li>\n<li>Functions</li>\n</ul>\n<p>In the following sections, we go through validation scenarios for each object type 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 the accuracy of the conversion.</p>\n<h4><a id=\"Schemas_32\"></a><strong>Schemas</strong></h4>\n<p>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 SQL queries.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/de49afdfda134aca982eab17f0281f4d_image.png\" alt=\"image.png\" /></p>\n<p>When you convert your Db2 LUW schema, Amazon Web Services SCT adds additional schemas (aws_db2_ext and aws_db2_ext_data) to your target database. These schemas implement SQL system functions of the Db2 LUW database that are required when writing the converted schema to your Aurora PostgreSQL database. These additional schemas are called the <a href=\"https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_ExtensionPack.html\" target=\"_blank\">Amazon Web Services SCT extension pack</a>.<br />\nWe exclude schemas related to system or catalog tables (SYS%,’SQLJ‘, ‘NULLID‘) in Db2 LUW and in PostgreSQL (‘pg_catalog‘,’information_schema‘, ‘public‘). We also exclude schemas related to specific functionality in Aurora PostgreSQL (<a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL-Lambda.html%22%20/l%20%22PostgreSQL-Lambda-install-extension\" target=\"_blank\">aws_commons</a>, <a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL-Lambda.html#PostgreSQL-Lambda-install-extension\" target=\"_blank\">aws_lambda</a>).<br />\nYou should verify the number of schemas in the source and target database matches. If any differences are found, you should look at the <a href=\"https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_BestPractices.html\" target=\"_blank\">Amazon Web Services SCT logs</a> to identify reasons for failure or create it manually.</p>\n<h4><a id=\"Tables_45\"></a><strong>Tables</strong></h4>\n<p>Amazon Web Services SCT converts source Db2 LUW tables to the equivalent target (PostgreSQL) tables. If required, we can use custom <a href=\"https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Mapping.html\" target=\"_blank\">mapping rules</a> to include or exclude specific tables from migration. The following scripts return the counts and detail-level information for all the tables:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/5f9f57f60506442192d7a11d81775c90_image.png\" alt=\"image.png\" /></p>\n<p>We added the condition C.RELISPARTITION = ‘f’ to filter out the partition table in PostgreSQL because IBM Db2 doesn’t list table partitions as separate tables. It’s important to note that PostgreSQL has several restrictions on <a href=\"https://www.postgresql.org/docs/13/ddl-partitioning.html\" target=\"_blank\">partition tables</a> that might impact object counts for primary keys, foreign keys, and indexes.</p>\n<p>For detail-level information, use the following queries:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/4846c44c9c584a9898270ac67b59d12b_image.png\" alt=\"image.png\" /></p>\n<p>Verify the results from the source and target databases. If you see any differences, identify the reason from the Amazon Web Services SCT or manual logs and rerun the failed statement after fixing the problem.</p>\n<h4><a id=\"Views_62\"></a><strong>Views</strong></h4>\n<p>You can validate the views count converted by Amazon Web Services SCT with the following queries on the source and target databases:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/05ab00d9b15f4a6aa2cc57bebb142d83_image.png\" alt=\"image.png\" /></p>\n<p>For detail-level information, use the following:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/bcd1fdce791847b09db5385889faf383_image.png\" alt=\"image.png\" /></p>\n<p>You should verify the count and details between the source and target using this SQL. If any differences are found, identify the cause and fix the differences.</p>\n<h4><a id=\"Primary_keys_77\"></a><strong>Primary keys</strong></h4>\n<p>Along with database object validation, you need to ensure the data is consistent and bound to integrity. Different types of <a href=\"https://www.postgresql.org/docs/13/ddl-constraints.html\" target=\"_blank\">constraints</a> provide you with the flexibility to control and check the data during insertion to avoid runtime data integrity issues.</p>\n<p>Primary keys allow you to have unique values for columns, which prevent information from being duplicated, following the normalization process. This key helps improve the search based on the key values and avoid table scans.</p>\n<p>The following queries help you extract the counts and details of primary keys in the source and target databases:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/2408adeedf1443b584da2bfd2ed7d868_image.png\" alt=\"image.png\" /></p>\n<p>For detail-level information, use the following query:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/b04e49e218c445b3a46c12ba748230bc_image.png\" alt=\"image.png\" /></p>\n<p>You should verify the count and details of the primary keys between the source and target using this SQL. If any differences are found, identify the cause through the deployment logs and fix the differences.</p>\n<h4><a id=\"Foreign_keys_95\"></a><strong>Foreign keys</strong></h4>\n<p>Foreign keys help you maintain referential integrity between tables. These keys should be turned off on the target before performing data migration using Amazon Web Services DMS full load migration. For more information, see <a href=\"https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.PostgreSQL.html\" target=\"_blank\">Using a PostgreSQL database as a target for Amazon Web Services Database Migration Service.</a><br />\nWith 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 Amazon Web Services DMS.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/37bf264030dd4402be71e5aaccb949e1_image.png\" alt=\"image.png\" /></p>\n<p>For detail-level information, use the following query:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/18ba1c9f743f446c8162079b364a2996_image.png\" alt=\"image.png\" /></p>\n<p>PostgreSQL version 11 has <a href=\"https://www.postgresql.org/docs/11/ddl-partitioning.html\" target=\"_blank\">limitations</a>with respect to foreign keys on partitioned tables, but many of those limitations are overcome in version 12 and onwards. You should keep these limitations in mind when verifying the count and detail of foreign keys between the source and target database.</p>\n<h4><a id=\"Indexes_110\"></a><strong>Indexes</strong></h4>\n<p>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.</p>\n<h4><a id=\"Unique_indexes_116\"></a><strong>Unique indexes</strong></h4>\n<p>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:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/15e9382fe56a4b5a821a690d8de60420_image.png\" alt=\"image.png\" /></p>\n<p>For detail-level information, use the following query:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/817988379b78447197a848ba760c38ee_image.png\" alt=\"image.png\" /></p>\n<h4><a id=\"Nonunique_indexes_129\"></a><strong>Non-unique indexes</strong></h4>\n<p><a href=\"https://www.postgresql.org/docs/13/indexes.html\" target=\"_blank\">Indexes </a>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 LUW 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.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/0b3e6d7bf2d0458b941910eaa3e19fb7_image.png\" alt=\"image.png\" /></p>\n<p>For detail-level information, use the following query:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/e81ec19ce8404fab85393910a43e0eb2_image.png\" alt=\"image.png\" /></p>\n<p>You should 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.</p>\n<h4><a id=\"Materialized_query_tables_143\"></a><strong>Materialized query tables</strong></h4>\n<p>Materialized query tables from Db2 LUW are migrated as <a href=\"https://www.postgresql.org/docs/13/rules-materializedviews.html\" target=\"_blank\">materialized views</a> 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:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/20ce2c53978c498aa69c45dcac5bc3d6_image.png\" alt=\"image.png\" /></p>\n<p>For detail-level information, use the following query:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/98a2a689946e4235aec6fc1b6c273fe6_image.png\" alt=\"image.png\" /></p>\n<p>You should 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.</p>\n<h4><a id=\"Userdefined_data_types_157\"></a><strong>User-defined data types</strong></h4>\n<p>Amazon Web Services SCT migrates custom data types from Db2 LUW to PostgreSQL as <a href=\"https://www.postgresql.org/docs/13/sql-createtype.html\" target=\"_blank\">types</a>. You can use the following queries to compare the objects between source and target:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/a412ea2f8ad444f09f198fb87d71af9a_image.png\" alt=\"image.png\" /></p>\n<p>You should 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.</p>\n<h4><a id=\"Triggers_167\"></a><strong>Triggers</strong></h4>\n<p><a href=\"https://www.postgresql.org/docs/13/sql-createtrigger.html\" target=\"_blank\">Triggers</a> 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:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/c1de2a6808d14523b43841a91ff98833_image.png\" alt=\"image.png\" /></p>\n<p>For detail-level information, use the following query:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/678f7ad712344e978de21722891b681d_image.png\" alt=\"image.png\" /></p>\n<p>The trigger count between Db2 LUW and PostgreSQL could vary because of the way triggers are <a href=\"https://www.postgresql.org/docs/13/trigger-definition.html\" target=\"_blank\">implemented</a> in PostgreSQL. You should 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.</p>\n<h4><a id=\"Sequences_180\"></a><strong>Sequences</strong></h4>\n<p>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.</p>\n<p>The following queries help you get the counts and detail-level information of sequences available in the source and target databases:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/434dd1014c664845b40c7acadb0d9efb_image.png\" alt=\"image.png\" /></p>\n<p>For detail-level information, use the following:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/17c658f2206c456a8c05e3e58900838a_image.png\" alt=\"image.png\" /></p>\n<p>You should verify the count and details of sequences between source and target, but it’s also important that you <a href=\"https://www.postgresql.org/docs/13/sql-altersequence.html\" target=\"_blank\">set the sequence</a> 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.</p>\n<h4><a id=\"Procedures_194\"></a><strong>Procedures</strong></h4>\n<p>Db2 LUW stored procedures encapsulate business logic and run related DDL or DML operations in a single unit of work. In PostgreSQL, we use <a href=\"https://www.postgresql.org/docs/13/xfunc.html\" target=\"_blank\">functions</a>over stored procedures, owing to the limitations of <a href=\"https://www.postgresql.org/docs/13/xproc.html\" target=\"_blank\">procedures</a>. This count gets added to the already existing function count in the source database. In both the source and target databases, the following queries provide counts and detail-level information about the procedures:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/86336fb93a724b2caec97608e2ef1c80_image.png\" alt=\"image.png\" /></p>\n<p>For detail-level information, use the following query:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/77d7b968039447b7af6039e587941d11_image.png\" alt=\"image.png\" /></p>\n<h4><a id=\"Functions_206\"></a><strong>Functions</strong></h4>\n<p>In Db2 LUW, 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 more than Db2 LUW. In both the source and target databases, the following queries provide counts and detail-level information about the functions:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/649532289ccc42e4b1d0ad283701bffe_image.png\" alt=\"image.png\" /></p>\n<p>For detail-level information, use the following query:</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/044bbd5a2198483390a416596e81e64e_image.png\" alt=\"image.png\" /></p>\n<h4><a id=\"Useful_PostgreSQL_catalog_tables_217\"></a><strong>Useful PostgreSQL catalog tables</strong></h4>\n<p>The following table summarizes some helpful Db2 LUW 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.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/e41377a2c1fd469aa67bd0c0f0dbfb54_image.png\" alt=\"image.png\" /></p>\n<h4><a id=\"Handling_objects_not_supported_in_PostgreSQL_223\"></a><strong>Handling objects not supported in PostgreSQL</strong></h4>\n<p>You must manually perform migration of Db2 LUW objects not supported by PostgreSQL. You can use the queries provided in this post to iteratively validate the migrated objects to identify gaps and fix them accordingly.</p>\n<h4><a id=\"Conclusion_229\"></a><strong>Conclusion</strong></h4>\n<p>In this post, we discussed validation of database objects with metadata queries for Db2 LUW and Aurora PostgreSQL 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 also confirms the integrity of the target database and ensures business continuity of the dependent application processes.</p>\n<p>You should do a few rounds of unit testing as well as functional testing irrespective of whether an object is automatically or manually converted. This saves a lot of rework when you conduct integration testing with your applications.</p>\n<p>Let us know if you have any comments or questions. We value your feedback!</p>\n<h4><a id=\"About_the_Authors_238\"></a><strong>About the Authors</strong></h4>\n<p><img src=\"https://dev-media.amazoncloud.cn/94566359b27f491988159873e75f1c06_image.png\" alt=\"image.png\" /></p>\n<p><strong>Sai Parthasaradhi</strong> is a Database Migration Specialist with Amazon Web Services Professional Services. He works closely with customers to help them migrate and modernize their databases on Amazon Web Services.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/d573b5dc59fa4385b8cba279a0b7ba62_image.png\" alt=\"image.png\" /></p>\n<p><strong>Rakesh Raghav</strong> is a Lead Database Consultant with the Amazon Web Services Professional Services in India, helping customers have a successful cloud adoption and migration journey. He is passionate about building innovative solutions to accelerate their database journey to the cloud.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/129095a8f3384845a9e7b6cca1242d5b_image.png\" alt=\"image.png\" /></p>\n<p><strong>Veeranjaneyulu Grandhi</strong> is a Database Consultant with Amazon Web Services. He works with customers to build scalable, highly available, and secure solutions in the Amazon Web Services Cloud. His focus area is homogenous and heterogeneous database migrations.</p>\n"}