Validate database objects after migrating from IBM Db2 LUW to Amazon RDS for MySQL, Amazon RDS for MariaDB, or Amazon Aurora MySQL

{"value":"Migrating your database from [IBM Db2 LUW](https://www.ibm.com/in-en/analytics/db2) to [Amazon Relational Database Service (Amazon RDS) for MySQL](https://aws.amazon.com/rds/mysql/), [Amazon RDS for MariaDB](https://aws.amazon.com/rds/mariadb/), or [Amazon Aurora MySQL-Compatible Edition](https://aws.amazon.com/rds/aurora/mysql-features/) is a complex, multistage process, which usually includes assessment, database schema conversion, data migration, functional testing, performance tuning, and many other steps spanning across the stages.\n\nYou can use [AWS Schema Conversion Tool](https://aws.amazon.com/dms/schema-conversion-tool/) (AWS SCT) to convert your database schema into a format compatible with your target database. [AWS Database Migration Service](https://aws.amazon.com/dms) (AWS DMS) supports many of the most popular [source](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Introduction.Sources.html#CHAP_Introduction.Sources.title) and [target](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Introduction.Targets.html) database engines to help you migrate databases to AWS quickly and securely.\n\nSchema migration with AWS SCT is a semi-automated process, so there is a chance of missing objects or key features in the target database. Therefore, schema validation is a crucial milestone that prevents missing objects during schema conversion and certifies that everything intended for migration has been migrated successfully.\n\nIn this post, we walk you through how to validate the database schema objects migrated from Db2 LUW to [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for MySQL, [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for MariaDB, or Aurora MySQL. A similar validation between Db2 LUW to [Amazon Aurora PostgreSQL-Compatible Edition](https://aws.amazon.com/rds/aurora/postgresql-features/) will be covered in another [post](https://aws.amazon.com/blogs/database/validate-database-objects-after-migrating-from-ibm-db2-luw-to-amazon-aurora-postgresql-or-amazon-rds-for-postgresql/).\n\n#### **Validating database objects**\n\nYou should perform schema validation right after you successfully convert the source schema objects from Db2 LUW to their equivalent MySQL or MariaDB schema objects. To perform the validations, we first need to understand the different types of Db2 LUW database objects and their equivalent MySQL or MariaDB database object type.\n\nThe following list shows database objects that you can compare between Db2 LUW (source) and the corresponding MySQL or MariaDB database (target). We should validate these objects thoroughly to reduce issues during later stages of database migration.\n\n- Schema\n- Tables\n- Views\n- Primary keys\n- Foreign keys\n- Indexes\n- Triggers\n- Procedures\n- Functions\n\nIn following sections, we deep dive into each of these object types and validate using their corresponding SQL queries to help us identify any missing migrated schema objects.\n\nIf you find differences for any of the schema objects, identify the reason of failure from the [AWS SCT logs](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_BestPractices.html#CHAP_BestPractices.Logging), convert the objects to the target database equivalent manually, and create the objects on the target database. For example, the SQL syntax to create data partition tables in Db2 LUW is different than that of MySQL or MariaDB. As a result, these tables aren’t created on the target database. You need to manually correct the SQL scripts to replace the target equivalent syntax for table partitions before running them on the target database.\n\nThe queries we use in these sections exclude system schemas in the source and target databases. We cover both summary-level as well as detail-level validations, wherever applicable. You can further modify these queries to include more scrutiny as required.\n\n#### **Schema**\n\nSchemas are used to represent a collection of database objects that serve a related functionality in an application or microservice. You should validate the schemas at the source and target databases using the following SQL queries.\n\nUse the following SQL query for Db2 LUW:\n\n```\\nselect schemaname as schema_name\\nfrom syscat.schemata\\nwhere schemaname not like 'SYS%' \\nand schemaname not IN ('SQLJ', 'NULLID')\\norder by schema_name;\\n```\n\nThe following screenshot is an example of the Db2 LUW output.\n\n\n\n\nThe following SQL query is for MySQL or MariaDB:\n\n```\\nselect schema_name\\nfrom INFORMATION_SCHEMA.schemata\\nwhere SCHEMA_NAME not in ('information_schema', 'performance_schema', 'sys', 'mysql')\\nand SCHEMA_NAME not like 'aws_db2%'\\norder by schema_name;\\n```\n\nYou get the following output.\n\n\n\n\n\nVerify if the schema results match between the source and target database using the aforementioned SQL queries. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In this example, the output is as expected. No issues were detected, so you can move to the next level of validation.\n\n#### **Extension packs**\n\nWhen you convert your database or data warehouse schema, AWS SCT may add additional schemas to your target database. These schemas implement SQL system functions of the source database that are required when writing your converted schema to your target database. These additional schemas are called [extension packs](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_ExtensionPack.html).\n\nWhen migrating your database from Db2 LUW to [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for MySQL or MariaDB or Aurora MySQL, AWS SCT creates two extension packs: `aws_db2_ext` and `aws_db2_ext_data`, as shown in the following examples.\n\nThe following SQL query is for MySQL or MariaDB:\n\n```\\nselect schema_name\\nfrom INFORMATION_SCHEMA.schemata\\nwhere SCHEMA_NAME in ('aws_db2_ext','aws_db2_ext_data')\\norder by schema_name;\\n```\n\nYou get the following output.\n\n\n\nYou can directly deploy these extension pack schemas to the target database after verifying the native equivalent options in both the source and target databases.\n\n#### **Tables**\n\nAWS SCT converts source Db2 LUW tables to the equivalent MySQL or MariaDB target tables with appropriate data types and relative table definitions using the default or custom [mapping rules](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Mapping.html). The following scripts return the counts and detail-level information for all the tables, assuming the source database doesn’t have any partitioned tables.\n\nUse the following Db2 LUW query:\n\n```\\nselect tab.tabschema as schema_name,\\n count(tab.tabname) as table_count\\nfrom syscat.tables tab\\nwhere tab.type = 'T'\\n and tab.tabschema not like 'SYS%'\\ngroup by tab.tabschema\\norder by tab.tabschema;\\n```\n\nThe following screenshot shows your output.\n\n\n\nUse the following SQL query for MySQL or MariaDB:\n\n```\\nSELECT table_schema AS Schema_name,\\n Count(table_name) AS Tables_Count\\nFROM information_schema.tables\\nWHERE table_type = 'BASE TABLE'\\nAND table_schema not in ('information_schema', 'performance_schema', 'sys', 'mysql')\\nand table_schema not like 'aws_db2%'\\nGROUP BY table_schema\\nORDER BY table_schema;\\n```\n\nYou get the following output.\n\n\n\nVerify the table counts between the source and target database using the aforementioned SQL queries. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In the preceding example, the output is as expected. No issues were detected, so you can move to the next level of validation.\n\n#### **Views**\n\nA view is a virtual table created by a query joining one or more tables. You can validate the views count converted by AWS SCT by using the following queries on the source and target databases.\n\nUse the following SQL query for Db2 LUW:\n\n```\\nselect tab.tabschema as schema_name,\\n count(tab.tabname) as table_count\\nfrom syscat.tables tab\\nwhere tab.type = 'V'\\nand tab.tabschema not like 'SYS%'\\ngroup by tab.tabschema\\norder by tab.tabschema;\\n```\n\nYou get the following output.\n\n\n\nUse the following SQL query for MySQL or MariaDB:\n\n```\\nSELECT TABLE_SCHEMA AS OBJECT_SCHEMA,\\n count(TABLE_NAME) AS View_Count\\nFROM information_schema.VIEWS\\nwhere table_schema not in ('information_schema', 'performance_schema', 'sys', 'mysql')\\nand table_schema not like 'aws_db2%'\\ngroup by TABLE_SCHEMA\\norder by TABLE_SCHEMA;\\n```\n\nThe following screenshot shows the output.\n\n\n\nVerify the view counts between the source and target database. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In this example, the output is as expected. No issues were detected, so you can move to the next level of validation.\n\n#### **Primary keys**\n\nA primary key is a column or group of columns whose values uniquely identify every row in the table. The following queries help you extract the counts and details of primary keys in source and target databases.\n\nThe following is SQL query for Db2 LUW:\n\n```\\nselect tab.tabschema as schema_name,\\n count(*) as PK_Count\\nfrom syscat.tables tab\\ninner join syscat.tabconst const\\n on const.tabschema = tab.tabschema\\n and const.tabname = tab.tabname and const.type = 'P'\\ninner join syscat.keycoluse key\\n on const.tabschema = key.tabschema\\n and const.tabname = key.tabname\\n and const.constname = key.constname\\nwhere tab.type = 'T'\\nand tab.tabschema not like 'SYS%'\\ngroup by tab.tabschema\\norder by tab.tabschema;\\n```\n\nThe following screenshot shows the output.\n\n\n\nUse the following SQL query for MySQL or MariaDB:\n\n```\\nSELECT cons.CONSTRAINT_SCHEMA as SCHEMA_NAME\\n , count(*) as PK_Count\\nFROM information_schema.TABLE_CONSTRAINTS cons\\njoin information_schema.KEY_COLUMN_USAGE col_use on cons.CONSTRAINT_SCHEMA=col_use.CONSTRAINT_SCHEMA \\nand cons.CONSTRAINT_NAME=col_use.CONSTRAINT_NAME \\nand cons.TABLE_SCHEMA=col_use.TABLE_SCHEMA\\nand cons.TABLE_NAME=col_use.TABLE_NAME\\nwhere cons.constraint_type in ('PRIMARY KEY')\\nand cons.table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')\\nand cons.table_schema not like 'aws_db2%'\\ngroup by cons.CONSTRAINT_SCHEMA\\norder by cons.CONSTRAINT_SCHEMA;\\n```\n\nYou get the following output.\n\n\n\nTo verify details including the column names in the constraint along with their ordinal position, you can use the following queries.\n\nUse the following SQL query for Db2 LUW:\n\n```\\nSelect tab.tabschema as schema_name, \\n tab.tabname as table_name,\\n 'PRIMARY' as constraint_name,\\n key.colname as column_name,\\n key.colseq as position\\nfrom syscat.tables tab\\ninner join syscat.tabconst const\\n on const.tabschema = tab.tabschema\\n and const.tabname = tab.tabname and const.type = 'P'\\ninner join syscat.keycoluse key\\n on const.tabschema = key.tabschema\\n and const.tabname = key.tabname\\n and const.constname = key.constname\\nwhere tab.type = 'T'\\nand tab.tabschema not like 'SYS%'\\norder by tab.tabschema, tab.tabname, key.colseq, key.colname;\\n```\n\nYou get the following output.\n\n\n\n\n\nThe following is SQL query for MySQL or MariaDB:\n\n```\\nSELECT cons.CONSTRAINT_SCHEMA as SCHEMA_NAME\\n, cons.TABLE_NAME\\n, cons.CONSTRAINT_NAME as CONSTRAINT_NAME\\n, col_use.COLUMN_NAME\\n, col_use.ORDINAL_POSITION as POSITION\\nFROM information_schema.TABLE_CONSTRAINTS cons\\njoin information_schema.KEY_COLUMN_USAGE col_use on cons.CONSTRAINT_SCHEMA=col_use.CONSTRAINT_SCHEMA \\nand cons.CONSTRAINT_NAME=col_use.CONSTRAINT_NAME \\nand cons.TABLE_SCHEMA=col_use.TABLE_SCHEMA\\nand cons.TABLE_NAME=col_use.TABLE_NAME\\nwhere cons.constraint_type in ('PRIMARY KEY')\\nand cons.table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')\\nand cons.table_schema not like 'aws_db2%'\\norder by cons.TABLE_SCHEMA, cons.TABLE_NAME, col_use.ORDINAL_POSITION, col_use.COLUMN_NAME;\\n```\n\nThe following screenshot shows the output.\n\n\n\nVerify the count and details of the primary keys between the source and target database. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In the preceding example, the output is as expected. No issues were detected, so you can move to the next level of validation.\n\n#### **Foreign keys**\n\nForeign keys link data in a parent table to the data in another child table. Foreign key constraints help maintain referential integrity between tables. You can use the following queries to get the counts and detail-level information about the foreign keys in both the source and target databases.\n\nUse the following Db2 LUW query:\n\n```\\nselect tabschema as schema_name, count(*) as fk_count\\nfrom syscat.references \\nwhere tabschema not like 'SYS%'\\ngroup by tabschema\\norder by tabschema;\\n```\n\nYou get the following output.\n\n\n\n\n\nUse the following MySQL or MariaDB query:\n\n```\\nSELECT TABLE_SCHEMA, count(*) as FK_COUNT\\nFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE\\nWHERE TABLE_SCHEMA not in ('information_schema', 'sys', 'performance_schema', 'mysql')\\nand TABLE_SCHEMA not like 'aws_db2%'\\nand REFERENCED_TABLE_SCHEMA IS NOT NULL\\ngroup by TABLE_SCHEMA\\norder by TABLE_SCHEMA; \\n```\n\nYou get the following output.\n\n\n\nFor detailed information, use the following queries.\n\nThe following SQL query is for Db2 LUW:\n\n```\\nselect ref.reftabschema as schema_name ,\\n ref.reftabname as table_name,\\n ref.constname as fk_constraint_name,\\n ref.tabname as foreign_table_name,\\n trim(key.colname) as fk_column_name\\nfrom syscat.references ref\\nleft outer join syscat.keycoluse key on key.tabschema = ref.tabschema \\nand key.tabname = ref.tabname\\nand key.constname = ref.constname\\nwhere ref.tabschema not like 'SYS%'\\norder by ref.reftabschema,ref.reftabname,ref.constname;\\n```\n\nThe following screenshot shows the output.\n\n\n\nThe following SQL query is for MySQL or MariaDB:\n\n```\\nselect referenced_table_schema as schema_name\\n , referenced_table_name as table_name\\n , constraint_name as fk_constraint_name\\n , table_name as foreign_table_name\\n , column_name as fk_column_name\\nFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE \\nWHERE TABLE_SCHEMA not in ('information_schema', 'sys', 'performance_schema', 'mysql')\\nand TABLE_SCHEMA not like 'aws_db2%'\\nand REFERENCED_TABLE_SCHEMA IS NOT NULL\\norder by referenced_table_schema, referenced_table_name, constraint_name;\\n```\n\nYou get the following output.\n\n\n\nVerify the count and the details of foreign keys between the source and target database. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In the preceding example, the output is as expected. No issues were detected, so you can move to the next level of validation.\n\n#### **Indexes**\n\n[Indexes](https://dev.mysql.com/doc/refman/5.7/en/optimization-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 MySQL or MariaDB databases.\n\nWith the following queries, you can get the counts of indexes and their types in both Db2 LUW and MySQL or MariaDB databases.\n\n**Unique indexes**\n\nFor unique indexes, use the following Db2 LUW query:\n\n```\\nselect ind.tabschema as schema_name,\\n count(cols.colname) as unique_count\\nfrom syscat.indexes ind\\njoin syscat.indexcoluse cols\\n on ind.indname = cols.indname\\n and ind.indschema = cols.indschema\\nwhere ind.tabschema not like 'SYS%'\\nand ind.uniquerule in ('U')\\ngroup by ind.tabschema\\norder by schema_name;\\n```\n\nThe following screenshot shows the output.\n\n\n\n\n\nUse the following SQL query for MySQL or MariaDB:\n\n```\\nSELECT cons.table_schema as SCHEMA_NAME\\n , count(col_use.COLUMN_NAME) as unique_count\\nFROM information_schema.TABLE_CONSTRAINTS cons\\njoin information_schema.KEY_COLUMN_USAGE col_use on cons.CONSTRAINT_SCHEMA=col_use.CONSTRAINT_SCHEMA \\nand cons.CONSTRAINT_NAME=col_use.CONSTRAINT_NAME \\nand cons.TABLE_SCHEMA=col_use.TABLE_SCHEMA\\nand cons.TABLE_NAME=col_use.TABLE_NAME\\nwhere cons.constraint_type in ('UNIQUE')\\nand cons.table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')\\nand cons.table_schema not like 'aws_db2%'\\ngroup by cons.table_schema\\norder by cons.table_schema;\\n```\n\nYou get the following output.\n\n\n\nFor detailed information, use the following queries.\n\nThe following SQL query is for Db2 LUW:\n\n```\\nselect ind.tabschema as schema_name,\\n ind.tabname as table_name,\\n ind.indname as CONSTRAINT_NAME,\\n 'Unique Index' as constraint_type,\\n cols.colname as column_name\\nfrom syscat.indexes ind\\njoin syscat.indexcoluse cols\\n on ind.indname = cols.indname\\n and ind.indschema = cols.indschema\\nwhere ind.tabschema not like 'SYS%'\\nand ind.uniquerule in ('U')\\norder by schema_name,\\n ind.tabname,\\n ind.indname;\\n```\n\nYou get the following output.\n\n\n\nUse the following SQL query for MySQL or MariaDB:\n\n```\\nSELECT cons.table_schema as SCHEMA_NAME\\n, cons.TABLE_NAME\\n, cons.CONSTRAINT_NAME as CONSTRAINT_NAME\\n,'Unique Index' as constraint_type\\n, col_use.COLUMN_NAME\\nFROM information_schema.TABLE_CONSTRAINTS cons\\njoin information_schema.KEY_COLUMN_USAGE col_use on cons.CONSTRAINT_SCHEMA=col_use.CONSTRAINT_SCHEMA \\nand cons.CONSTRAINT_NAME=col_use.CONSTRAINT_NAME \\nand cons.TABLE_SCHEMA=col_use.TABLE_SCHEMA\\nand cons.TABLE_NAME=col_use.TABLE_NAME\\nwhere cons.constraint_type in ('UNIQUE')\\nand cons.table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')\\nand cons.table_schema not like 'aws_db2%'\\norder by cons.table_schema, cons.TABLE_NAME, cons.CONSTRAINT_NAME, col_use.COLUMN_NAME;\\n```\n\nThe following screenshot shows the output.\n\n\n\n#### **Non-unique indexes**\n\nMySQL and MariaDB create implicit indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. The MySQL and MariaDB database queries used in this section exclude such indexes so that you can perform the validations against the source without any mismatch.\n\nUse the following SQL query for Db2 LUW:\n\n```\\nselect ind.tabschema as schema_name,\\n count(cols.colname) as index_count\\nfrom syscat.indexes ind\\njoin syscat.indexcoluse cols\\n on ind.indname = cols.indname\\n and ind.indschema = cols.indschema\\nwhere ind.tabschema not like 'SYS%'\\nand ind.uniquerule in ('D')\\ngroup by ind.tabschema\\norder by schema_name;\\n```\n\nThe output is as follows.\n\n\n\nUse the following SQL query for MySQL or MariaDB:\n\n```\\nSELECT idx.table_schema\\n ,count(idx.column_name) as index_count\\nFROM INFORMATION_SCHEMA.STATISTICS idx\\nWHERE idx.TABLE_SCHEMA not in ('information_schema', 'sys', 'performance_schema', 'mysql', 'DB2INST1')\\nand idx.table_schema not like 'aws_db2%'\\nand idx.index_name not in ('PRIMARY')\\nand idx.non_unique = 1\\nand index_name not like 'FK_%'\\ngroup by idx.table_schema\\norder by idx.table_schema;\\n```\n\nYou get the following output.\n\n\n\nFor detailed information, use the following queries.\n\nThe following SQL query is for Db2 LUW:\n\n```\\nselect ind.tabschema as schema_name,\\n ind.tabname as table_name,\\n ind.indname as index_name,\\n cols.colname as column_name\\nfrom syscat.indexes ind\\njoin syscat.indexcoluse cols\\n on ind.indname = cols.indname\\n and ind.indschema = cols.indschema\\nwhere ind.tabschema not like 'SYS%'\\nand ind.uniquerule in ('D')\\norder by schema_name,\\n ind.tabname,\\n ind.indname,\\n cols.colname;\\n```\n\nThe following screenshot shows the output.\n\n\n\nUse the following SQL query for MySQL or MariaDB:\n\n```\\nSELECT idx.table_schema\\n, idx.table_name\\n, idx.index_name\\n, idx.column_name\\nFROM INFORMATION_SCHEMA.STATISTICS idx\\nWHERE idx.TABLE_SCHEMA not in ('information_schema', 'sys', 'performance_schema', 'mysql', 'DB2INST1')\\nand idx.table_schema not like 'aws_db2%'\\nand idx.index_name not in ('PRIMARY')\\nand idx.non_unique = 1\\nand index_name not like 'FK_%'\\norder by idx.table_schema\\n, idx.table_name\\n, idx.index_name\\n, idx.column_name;\\n```\n\nThe output is as follows.\n\n\n\nVerify the count and the details of the indexes between the source and target database. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In this example, the output is as expected. No issues were detected, so you can move to the next level of validation.\n\n#### **Triggers**\n\n[Triggers](https://dev.mysql.com/doc/refman/5.7/en/triggers.html) define a set of actions that are performed in response to an insert, update, or delete operation on a specified table. The following queries give you the count and details of triggers for both the source and target databases.\n\nUse the following Db2 LUW query:\n\n```\\nSelect tabschema as table_schema\\n, count(trigname) as trigger_count\\nFrom\\tsyscat.triggers t\\nwhere tabschema not like 'SYS%'\\ngroup by tabschema\\norder by tabschema;\\n```\n\nYou get the following output.\n\n\n\nUse the following MySQL or MariaDB query:\n\n```\\nSELECT tgr.EVENT_OBJECT_SCHEMA as TABLE_SCHEMA\\n\\t , count(tgr.TRIGGER_NAME) as trigger_count\\nFROM information_schema.triggers tgr\\nwhere tgr.TRIGGER_SCHEMA not in ('sys','performance_schema','mysql')\\nand tgr.TRIGGER_SCHEMA not like 'aws_db2%'\\ngroup by tgr.EVENT_OBJECT_SCHEMA\\norder by tgr.EVENT_OBJECT_SCHEMA;\\n```\n\nThe output is as follows.\n\n\n\nFor detail-level information, use the following queries.\n\nThe following SQL query is for Db2 LUW:\n\n```\\nselect\\n tabschema as table_schema,\\n trigname as trigger_name,\\n tabname as table_name,\\n case trigtime\\n when 'B' then 'before'\\n when 'A' then 'after'\\n when 'I' then 'instead of'\\n end as activation,\\n rtrim(case when eventupdate ='Y' then 'update ' else '' end\\n ||\\n case when eventdelete ='Y' then 'delete ' else '' end\\n ||\\n case when eventinsert ='Y' then 'insert ' else '' end)\\n as event\\nfrom syscat.triggers t\\nwhere tabschema not like 'SYS%'\\norder by table_name, trigger_name;\\n```\n\nYou get the following output.\n\n\n\nUse the following MySQL or MariaDB query:\n\n```\\nSELECT tgr.EVENT_OBJECT_SCHEMA as TABLE_SCHEMA\\n,tgr.TRIGGER_NAME\\n\\t,tgr.EVENT_OBJECT_TABLE as Table_name\\n,tgr.ACTION_TIMING as activation\\n,tgr.EVENT_MANIPULATION as event\\nFROM information_schema.triggers tgr\\nwhere tgr.TRIGGER_SCHEMA not in ('sys', 'performance_schema', 'mysql')\\nand tgr.TRIGGER_SCHEMA not like 'aws_db2%';\\n```\n\nThe following screenshot shows the output.\n\n\n\nVerify the trigger count and details between the source and target database. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In the preceding example, the output is as expected. No issues were detected, so you can move to the next level of validation.\n\n#### **Procedures**\n\nA stored procedure is a collection of precompiled SQL statements stored in a database that can be called from an application program. Stored procedures have better performance compared to inline queries because the SQL queries are precompiled with reusable execution plans. It also improves productivity because similar SQL statements or the business logic are consolidated and reused across applications or other programs. The following queries give you the count and details of stored procedures for both the source and target databases.\n\nThe following is SQL query for Db2 LUW:\n\n```\\nselect routineschema as schema_name\\n,count(*) as proc_count\\nfrom syscat.routines\\nwhere routinetype = 'P'\\n and routineschema not like 'SYS%'\\n and routineschema not like 'SQLJ%'\\ngroup by routineschema\\norder by routineschema;\\n```\n\nYou get the following output.\n\n\n\nUse the following SQL query for MySQL or MariaDB:\n\n```\\nSELECT rtn.ROUTINE_SCHEMA\\n ,count(*) as proc_count\\nFROM information_schema.ROUTINES rtn\\nwhere rtn.ROUTINE_TYPE = 'PROCEDURE'\\nand rtn.ROUTINE_SCHEMA not in ('information_schema','sys', 'performance_schema', 'mysql')\\nand rtn.ROUTINE_SCHEMA not like 'aws_db2%'\\ngroup by rtn.ROUTINE_TYPE\\norder by rtn.ROUTINE_TYPE;\\n```\n\nThe output is as follows.\n\n\n\nFor detail-level information, use the following queries.\n\nThe following is SQL query for Db2 LUW:\n\n```\\nselect routineschema as schema_name,\\n routinename as procedure_name\\nfrom syscat.routines\\nwhere routinetype = 'P'\\n and routineschema not like 'SYS%'\\n and routineschema not like 'SQLJ%'\\norder by schema_name,\\n procedure_name;\\n```\n\nYou get the following output.\n\n\n\nUse the following SQL query for MySQL or MariaDB:\n\n```\\nSELECT rtn.ROUTINE_TYPE\\n\\t ,rtn.ROUTINE_SCHEMA\\n ,rtn.ROUTINE_NAME\\nFROM information_schema.ROUTINES rtn\\nwhere rtn.ROUTINE_TYPE = 'PROCEDURE'\\nand rtn.ROUTINE_SCHEMA not in ('information_schema','sys', 'performance_schema', 'mysql')\\nand rtn.ROUTINE_SCHEMA not like 'aws_db2%';\\n```\n\nThe following screenshot shows the output.\n\n\n\nVerify the count and the details of procedures between the source and target database. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In this example, the output is as expected. No issues were detected, so you can move to the next level of validation.\n\n#### **Functions**\n\nFunctions implement specific business or functional logic based on the given input and return a predefined output. The following queries give you the count and details of functions for both the source and target databases.\n\nUse the following SQL query for Db2 LUW:\n\n```\\nselect routineschema as schema_name,\\n count(*) as proc_count\\nfrom syscat.routines\\nwhere routinetype = 'F'\\n and routineschema not like 'SYS%'\\n and routineschema not like 'SQLJ%'\\ngroup by routineschema\\norder by routineschema;\\n```\n\nThe following screenshot shows the output.\n\n\n\nThe following is SQL query for MySQL or MariaDB:\n\n```\\nSELECT rtn.ROUTINE_SCHEMA\\n\\t,count(*) as proc_count\\nFROM information_schema.ROUTINES rtn\\nwhere rtn.ROUTINE_TYPE = 'FUNCTION'\\nand rtn.ROUTINE_SCHEMA not in ('information_schema', 'sys', 'performance_schema', 'mysql')\\nand rtn.ROUTINE_SCHEMA not like 'aws_db2%'\\ngroup by rtn.ROUTINE_TYPE\\norder by rtn.ROUTINE_TYPE;\\n```\n\nYou get the following output.\n\n\n\nFor detail-level information, use the following queries.\n\nUse the following SQL query for Db2 LUW:\n\n```\\nselect routineschema as schema_name,\\n routinename as procedure_name\\nfrom syscat.routines\\nwhere routinetype = 'F'\\n and routineschema not like 'SYS%'\\n and routineschema not like 'SQLJ%'\\norder by schema_name,\\n procedure_name;\\n```\n\nThe output is as follows.\n\n\n\nUse the following SQL query for MySQL or MariaDB:\n\n```\\nSELECT rtn.ROUTINE_SCHEMA\\n ,rtn.ROUTINE_NAME\\nFROM information_schema.ROUTINES rtn\\nwhere rtn.ROUTINE_TYPE = 'FUNCTION'\\nand rtn.ROUTINE_SCHEMA not in ('information_schema','sys', 'performance_schema', 'mysql')\\nand rtn.ROUTINE_SCHEMA not like 'aws_db2%';\\n```\n\nThe following screenshot shows the output.\n\n\n\nVerify the count and the details of functions between the source and target database. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In the preceding example, the output is as expected. No issues were detected, so you can move to the next level of validation.\n\n#### **Validating data-partitioned tables**\n\nPartitioned tables use a data organization scheme in which table data is divided across multiple storage objects, called *data partitions* or *ranges*, according to values in one or more table partitioning key columns of the table. You can use the following queries to compare the partitioned tables between the source and target.\n\nThe following is SQL query for Db2 LUW:\n\n```\\nselect dp.tabschema as table_schema\\n, dp.tabname as table_name\\n, datapartitionname as partition_name\\n, char(datapartitionexpression,30) as partition_expression\\n, highvalue as partition_description\\nfrom SYSCAT.DATAPARTITIONS dp \\ninner join syscat.datapartitionexpression dpe on dp.tabschema=dpe.tabschema\\nand dp.tabname=dpe.tabname\\nwhere dp.tabschema='GOASALESDW';\\n```\n\nYou get the following output.\n\n\n\nThe following SQL query is for MySQL or MariaDB:\n\n```\\nselect table_schema\\n, table_name\\n, partition_name\\n, partition_expression\\n, partition_description\\nFROM INFORMATION_SCHEMA.PARTITIONS \\nwhere table_schema='GOASALESDW';\\n```\n\nThe output is as follows.\n\n\n\nVerify the count and the details of partitioned data tables between the source and target database. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In the preceding example, the output is as expected. No issues were detected, so you can move to the next level of validation.\n\n#### **Useful MySQL or MariaDB catalog tables**\n\nThe following table summarizes some of the Db2 LUW objects and their corresponding objects on MySQL and MariaDB that are helpful for database object validation. For MySQL databases with many objects and concurrent workloads, the queries provided in this post can take longer duration to complete. This can be improved to some extent using [data dictionary changes](https://dev.mysql.com/doc/refman/8.0/en/information-schema-optimization.html) in MySQL 8.0.\n\n\n\n#### **Handling objects not supported in MySQL or MariaDB**\n\nFor the Db2 LUW objects not supported by MySQL or MariaDB (like aliases, sequences, or materialized query tables), you must perform the migration from source to target database manually to achieve the functionality that exists in the source database. You can use the queries provided in this post to iteratively validate the migrated objects to identify gaps and fix them.\n\n#### **Conclusion**\n\nDatabase object validation is an essential step that provides an in-depth view of the migration accuracy. It confirms whether all the database objects are migrated appropriately and the integrity of target database. It ensures business continuity of the dependent application processes.\n\nIn this post, we discussed post-migration validation of database objects with the metadata queries for a Db2 LUW source and [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for MySQL, [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for MariaDB, or Aurora MySQL target database. You can run the queries provided in this post on your source and target database to retrieve the metadata and compare the output to confirm if your migration was successful.\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 AWS Professional Services. He works closely with customers to help them migrate and modernize their databases on AWS.\n\n\n\n**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.","render":"<p>Migrating your database from <a href=\\"https://www.ibm.com/in-en/analytics/db2\\" target=\\"_blank\\">IBM Db2 LUW</a> to <a href=\\"https://aws.amazon.com/rds/mysql/\\" target=\\"_blank\\">Amazon Relational Database Service (Amazon RDS) for MySQL</a>, <a href=\\"https://aws.amazon.com/rds/mariadb/\\" target=\\"_blank\\">Amazon RDS for MariaDB</a>, or <a href=\\"https://aws.amazon.com/rds/aurora/mysql-features/\\" target=\\"_blank\\">Amazon Aurora MySQL-Compatible Edition</a> is a complex, multistage process, which usually includes assessment, database schema conversion, data migration, functional testing, performance tuning, and many other steps spanning across the stages.</p>\\n<p>You can use <a href=\\"https://aws.amazon.com/dms/schema-conversion-tool/\\" target=\\"_blank\\">AWS Schema Conversion Tool</a> (AWS SCT) to convert your database schema into a format compatible with your target database. <a href=\\"https://aws.amazon.com/dms\\" target=\\"_blank\\">AWS Database Migration Service</a> (AWS DMS) supports many of the most popular <a href=\\"https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Introduction.Sources.html#CHAP_Introduction.Sources.title\\" target=\\"_blank\\">source</a> and <a href=\\"https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Introduction.Targets.html\\" target=\\"_blank\\">target</a> database engines to help you migrate databases to AWS quickly and securely.</p>\\n<p>Schema migration with AWS SCT is a semi-automated process, so there is a chance of missing objects or key features in the target database. Therefore, schema validation is a crucial milestone that prevents missing objects during schema conversion and certifies that everything intended for migration has been migrated successfully.</p>\n<p>In this post, we walk you through how to validate the database schema objects migrated from Db2 LUW to Amazon RDS for MySQL, Amazon RDS for MariaDB, or Aurora MySQL. A similar validation between Db2 LUW to <a href=\\"https://aws.amazon.com/rds/aurora/postgresql-features/\\" target=\\"_blank\\">Amazon Aurora PostgreSQL-Compatible Edition</a> will be covered in another <a href=\\"https://aws.amazon.com/blogs/database/validate-database-objects-after-migrating-from-ibm-db2-luw-to-amazon-aurora-postgresql-or-amazon-rds-for-postgresql/\\" target=\\"_blank\\">post</a>.</p>\\n<h4><a id=\\"Validating_database_objects_8\\"></a><strong>Validating database objects</strong></h4>\\n<p>You should perform schema validation right after you successfully convert the source schema objects from Db2 LUW to their equivalent MySQL or MariaDB schema objects. To perform the validations, we first need to understand the different types of Db2 LUW database objects and their equivalent MySQL or MariaDB database object type.</p>\n<p>The following list shows database objects that you can compare between Db2 LUW (source) and the corresponding MySQL or MariaDB database (target). We should validate these objects thoroughly to reduce issues during later stages of database migration.</p>\n<ul>\\n<li>Schema</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>Triggers</li>\n<li>Procedures</li>\n<li>Functions</li>\n</ul>\\n<p>In following sections, we deep dive into each of these object types and validate using their corresponding SQL queries to help us identify any missing migrated schema objects.</p>\n<p>If you find differences for any of the schema objects, identify the reason of failure from the <a href=\\"https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_BestPractices.html#CHAP_BestPractices.Logging\\" target=\\"_blank\\">AWS SCT logs</a>, convert the objects to the target database equivalent manually, and create the objects on the target database. For example, the SQL syntax to create data partition tables in Db2 LUW is different than that of MySQL or MariaDB. As a result, these tables aren’t created on the target database. You need to manually correct the SQL scripts to replace the target equivalent syntax for table partitions before running them on the target database.</p>\\n<p>The queries we use in these sections exclude system schemas in the source and target databases. We cover both summary-level as well as detail-level validations, wherever applicable. You can further modify these queries to include more scrutiny as required.</p>\n<h4><a id=\\"Schema_30\\"></a><strong>Schema</strong></h4>\\n<p>Schemas are used to represent a collection of database objects that serve a related functionality in an application or microservice. You should validate the schemas at the source and target databases using the following SQL queries.</p>\n<p>Use the following SQL query for Db2 LUW:</p>\n<pre><code class=\\"lang-\\">select schemaname as schema_name\\nfrom syscat.schemata\\nwhere schemaname not like 'SYS%' \\nand schemaname not IN ('SQLJ', 'NULLID')\\norder by schema_name;\\n</code></pre>\\n<p>The following screenshot is an example of the Db2 LUW output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/47c186667b2b402cbaa61ff5ecc1a3e7_image.png\\" alt=\\"image.png\\" /></p>\n<p>The following SQL query is for MySQL or MariaDB:</p>\n<pre><code class=\\"lang-\\">select schema_name\\nfrom INFORMATION_SCHEMA.schemata\\nwhere SCHEMA_NAME not in ('information_schema', 'performance_schema', 'sys', 'mysql')\\nand SCHEMA_NAME not like 'aws_db2%'\\norder by schema_name;\\n</code></pre>\\n<p>You get the following output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/1e818bba2ded4eab9998732768aec9f1_image.png\\" alt=\\"image.png\\" /></p>\n<p>Verify if the schema results match between the source and target database using the aforementioned SQL queries. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In this example, the output is as expected. No issues were detected, so you can move to the next level of validation.</p>\n<h4><a id=\\"Extension_packs_67\\"></a><strong>Extension packs</strong></h4>\\n<p>When you convert your database or data warehouse schema, AWS SCT may add additional schemas to your target database. These schemas implement SQL system functions of the source database that are required when writing your converted schema to your target database. These additional schemas are called <a href=\\"https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_ExtensionPack.html\\" target=\\"_blank\\">extension packs</a>.</p>\\n<p>When migrating your database from Db2 LUW to Amazon RDS for MySQL or MariaDB or Aurora MySQL, AWS SCT creates two extension packs: <code>aws_db2_ext</code> and <code>aws_db2_ext_data</code>, as shown in the following examples.</p>\\n<p>The following SQL query is for MySQL or MariaDB:</p>\n<pre><code class=\\"lang-\\">select schema_name\\nfrom INFORMATION_SCHEMA.schemata\\nwhere SCHEMA_NAME in ('aws_db2_ext','aws_db2_ext_data')\\norder by schema_name;\\n</code></pre>\\n<p>You get the following output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/5a381fceb5fc4dafbec5b585d6eecd2a_image.png\\" alt=\\"image.png\\" /></p>\n<p>You can directly deploy these extension pack schemas to the target database after verifying the native equivalent options in both the source and target databases.</p>\n<h4><a id=\\"Tables_88\\"></a><strong>Tables</strong></h4>\\n<p>AWS SCT converts source Db2 LUW tables to the equivalent MySQL or MariaDB target tables with appropriate data types and relative table definitions using the default or custom <a href=\\"https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Mapping.html\\" target=\\"_blank\\">mapping rules</a>. The following scripts return the counts and detail-level information for all the tables, assuming the source database doesn’t have any partitioned tables.</p>\\n<p>Use the following Db2 LUW query:</p>\n<pre><code class=\\"lang-\\">select tab.tabschema as schema_name,\\n count(tab.tabname) as table_count\\nfrom syscat.tables tab\\nwhere tab.type = 'T'\\n and tab.tabschema not like 'SYS%'\\ngroup by tab.tabschema\\norder by tab.tabschema;\\n</code></pre>\\n<p>The following screenshot shows your output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/46abcf43150044ba881cc1de3e5cb7ab_image.png\\" alt=\\"image.png\\" /></p>\n<p>Use the following SQL query for MySQL or MariaDB:</p>\n<pre><code class=\\"lang-\\">SELECT table_schema AS Schema_name,\\n Count(table_name) AS Tables_Count\\nFROM information_schema.tables\\nWHERE table_type = 'BASE TABLE'\\nAND table_schema not in ('information_schema', 'performance_schema', 'sys', 'mysql')\\nand table_schema not like 'aws_db2%'\\nGROUP BY table_schema\\nORDER BY table_schema;\\n</code></pre>\\n<p>You get the following output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/1b0a4db9e25441308520e3f1ec47848d_image.png\\" alt=\\"image.png\\" /></p>\n<p>Verify the table counts between the source and target database using the aforementioned SQL queries. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In the preceding example, the output is as expected. No issues were detected, so you can move to the next level of validation.</p>\n<h4><a id=\\"Views_127\\"></a><strong>Views</strong></h4>\\n<p>A view is a virtual table created by a query joining one or more tables. You can validate the views count converted by AWS SCT by using the following queries on the source and target databases.</p>\n<p>Use the following SQL query for Db2 LUW:</p>\n<pre><code class=\\"lang-\\">select tab.tabschema as schema_name,\\n count(tab.tabname) as table_count\\nfrom syscat.tables tab\\nwhere tab.type = 'V'\\nand tab.tabschema not like 'SYS%'\\ngroup by tab.tabschema\\norder by tab.tabschema;\\n</code></pre>\\n<p>You get the following output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/f3255f2de05d4338804f5056d7e1cb15_image.png\\" alt=\\"image.png\\" /></p>\n<p>Use the following SQL query for MySQL or MariaDB:</p>\n<pre><code class=\\"lang-\\">SELECT TABLE_SCHEMA AS OBJECT_SCHEMA,\\n count(TABLE_NAME) AS View_Count\\nFROM information_schema.VIEWS\\nwhere table_schema not in ('information_schema', 'performance_schema', 'sys', 'mysql')\\nand table_schema not like 'aws_db2%'\\ngroup by TABLE_SCHEMA\\norder by TABLE_SCHEMA;\\n</code></pre>\\n<p>The following screenshot shows the output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/7992928615a74c72a46d2e1bebd69b1d_image.png\\" alt=\\"image.png\\" /></p>\n<p>Verify the view counts between the source and target database. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In this example, the output is as expected. No issues were detected, so you can move to the next level of validation.</p>\n<h4><a id=\\"Primary_keys_165\\"></a><strong>Primary keys</strong></h4>\\n<p>A primary key is a column or group of columns whose values uniquely identify every row in the table. The following queries help you extract the counts and details of primary keys in source and target databases.</p>\n<p>The following is SQL query for Db2 LUW:</p>\n<pre><code class=\\"lang-\\">select tab.tabschema as schema_name,\\n count(*) as PK_Count\\nfrom syscat.tables tab\\ninner join syscat.tabconst const\\n on const.tabschema = tab.tabschema\\n and const.tabname = tab.tabname and const.type = 'P'\\ninner join syscat.keycoluse key\\n on const.tabschema = key.tabschema\\n and const.tabname = key.tabname\\n and const.constname = key.constname\\nwhere tab.type = 'T'\\nand tab.tabschema not like 'SYS%'\\ngroup by tab.tabschema\\norder by tab.tabschema;\\n</code></pre>\\n<p>The following screenshot shows the output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/7bba4bf869ab4f44a9e9107dbd667398_image.png\\" alt=\\"image.png\\" /></p>\n<p>Use the following SQL query for MySQL or MariaDB:</p>\n<pre><code class=\\"lang-\\">SELECT cons.CONSTRAINT_SCHEMA as SCHEMA_NAME\\n , count(*) as PK_Count\\nFROM information_schema.TABLE_CONSTRAINTS cons\\njoin information_schema.KEY_COLUMN_USAGE col_use on cons.CONSTRAINT_SCHEMA=col_use.CONSTRAINT_SCHEMA \\nand cons.CONSTRAINT_NAME=col_use.CONSTRAINT_NAME \\nand cons.TABLE_SCHEMA=col_use.TABLE_SCHEMA\\nand cons.TABLE_NAME=col_use.TABLE_NAME\\nwhere cons.constraint_type in ('PRIMARY KEY')\\nand cons.table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')\\nand cons.table_schema not like 'aws_db2%'\\ngroup by cons.CONSTRAINT_SCHEMA\\norder by cons.CONSTRAINT_SCHEMA;\\n</code></pre>\\n<p>You get the following output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/5d523b2a87834f4f8aab008abdeccb3f_image.png\\" alt=\\"image.png\\" /></p>\n<p>To verify details including the column names in the constraint along with their ordinal position, you can use the following queries.</p>\n<p>Use the following SQL query for Db2 LUW:</p>\n<pre><code class=\\"lang-\\">Select tab.tabschema as schema_name, \\n tab.tabname as table_name,\\n 'PRIMARY' as constraint_name,\\n key.colname as column_name,\\n key.colseq as position\\nfrom syscat.tables tab\\ninner join syscat.tabconst const\\n on const.tabschema = tab.tabschema\\n and const.tabname = tab.tabname and const.type = 'P'\\ninner join syscat.keycoluse key\\n on const.tabschema = key.tabschema\\n and const.tabname = key.tabname\\n and const.constname = key.constname\\nwhere tab.type = 'T'\\nand tab.tabschema not like 'SYS%'\\norder by tab.tabschema, tab.tabname, key.colseq, key.colname;\\n</code></pre>\\n<p>You get the following output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/21c03e1810584255872ce0a2966caeca_image.png\\" alt=\\"image.png\\" /></p>\n<p>The following is SQL query for MySQL or MariaDB:</p>\n<pre><code class=\\"lang-\\">SELECT cons.CONSTRAINT_SCHEMA as SCHEMA_NAME\\n, cons.TABLE_NAME\\n, cons.CONSTRAINT_NAME as CONSTRAINT_NAME\\n, col_use.COLUMN_NAME\\n, col_use.ORDINAL_POSITION as POSITION\\nFROM information_schema.TABLE_CONSTRAINTS cons\\njoin information_schema.KEY_COLUMN_USAGE col_use on cons.CONSTRAINT_SCHEMA=col_use.CONSTRAINT_SCHEMA \\nand cons.CONSTRAINT_NAME=col_use.CONSTRAINT_NAME \\nand cons.TABLE_SCHEMA=col_use.TABLE_SCHEMA\\nand cons.TABLE_NAME=col_use.TABLE_NAME\\nwhere cons.constraint_type in ('PRIMARY KEY')\\nand cons.table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')\\nand cons.table_schema not like 'aws_db2%'\\norder by cons.TABLE_SCHEMA, cons.TABLE_NAME, col_use.ORDINAL_POSITION, col_use.COLUMN_NAME;\\n</code></pre>\\n<p>The following screenshot shows the output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/c8a31b942584430883ee2dc2b71191b0_image.png\\" alt=\\"image.png\\" /></p>\n<p>Verify the count and details of the primary keys between the source and target database. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In the preceding example, the output is as expected. No issues were detected, so you can move to the next level of validation.</p>\n<h4><a id=\\"Foreign_keys_267\\"></a><strong>Foreign keys</strong></h4>\\n<p>Foreign keys link data in a parent table to the data in another child table. Foreign key constraints help maintain referential integrity between tables. You can use the following queries to get the counts and detail-level information about the foreign keys in both the source and target databases.</p>\n<p>Use the following Db2 LUW query:</p>\n<pre><code class=\\"lang-\\">select tabschema as schema_name, count(*) as fk_count\\nfrom syscat.references \\nwhere tabschema not like 'SYS%'\\ngroup by tabschema\\norder by tabschema;\\n</code></pre>\\n<p>You get the following output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/a6d25e48a392493590809c7ad769bfcc_image.png\\" alt=\\"image.png\\" /></p>\n<p>Use the following MySQL or MariaDB query:</p>\n<pre><code class=\\"lang-\\">SELECT TABLE_SCHEMA, count(*) as FK_COUNT\\nFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE\\nWHERE TABLE_SCHEMA not in ('information_schema', 'sys', 'performance_schema', 'mysql')\\nand TABLE_SCHEMA not like 'aws_db2%'\\nand REFERENCED_TABLE_SCHEMA IS NOT NULL\\ngroup by TABLE_SCHEMA\\norder by TABLE_SCHEMA; \\n</code></pre>\\n<p>You get the following output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/2cd5202b4aff4eebbd0f7b8007b6e312_image.png\\" alt=\\"image.png\\" /></p>\n<p>For detailed information, use the following queries.</p>\n<p>The following SQL query is for Db2 LUW:</p>\n<pre><code class=\\"lang-\\">select ref.reftabschema as schema_name ,\\n ref.reftabname as table_name,\\n ref.constname as fk_constraint_name,\\n ref.tabname as foreign_table_name,\\n trim(key.colname) as fk_column_name\\nfrom syscat.references ref\\nleft outer join syscat.keycoluse key on key.tabschema = ref.tabschema \\nand key.tabname = ref.tabname\\nand key.constname = ref.constname\\nwhere ref.tabschema not like 'SYS%'\\norder by ref.reftabschema,ref.reftabname,ref.constname;\\n</code></pre>\\n<p>The following screenshot shows the output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/7ebac4fdf41a4ac18835be28865768e6_image.png\\" alt=\\"image.png\\" /></p>\n<p>The following SQL query is for MySQL or MariaDB:</p>\n<pre><code class=\\"lang-\\">select referenced_table_schema as schema_name\\n , referenced_table_name as table_name\\n , constraint_name as fk_constraint_name\\n , table_name as foreign_table_name\\n , column_name as fk_column_name\\nFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE \\nWHERE TABLE_SCHEMA not in ('information_schema', 'sys', 'performance_schema', 'mysql')\\nand TABLE_SCHEMA not like 'aws_db2%'\\nand REFERENCED_TABLE_SCHEMA IS NOT NULL\\norder by referenced_table_schema, referenced_table_name, constraint_name;\\n</code></pre>\\n<p>You get the following output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/78d31adad72e459b89be64f4550f0118_image.png\\" alt=\\"image.png\\" /></p>\n<p>Verify the count and the details of foreign keys between the source and target database. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In the preceding example, the output is as expected. No issues were detected, so you can move to the next level of validation.</p>\n<h4><a id=\\"Indexes_346\\"></a><strong>Indexes</strong></h4>\\n<p><a href=\\"https://dev.mysql.com/doc/refman/5.7/en/optimization-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 MySQL or MariaDB databases.</p>\\n<p>With the following queries, you can get the counts of indexes and their types in both Db2 LUW and MySQL or MariaDB databases.</p>\n<p><strong>Unique indexes</strong></p>\\n<p>For unique indexes, use the following Db2 LUW query:</p>\n<pre><code class=\\"lang-\\">select ind.tabschema as schema_name,\\n count(cols.colname) as unique_count\\nfrom syscat.indexes ind\\njoin syscat.indexcoluse cols\\n on ind.indname = cols.indname\\n and ind.indschema = cols.indschema\\nwhere ind.tabschema not like 'SYS%'\\nand ind.uniquerule in ('U')\\ngroup by ind.tabschema\\norder by schema_name;\\n</code></pre>\\n<p>The following screenshot shows the output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/ed55e82fcda6447c8cbc020d84d77570_image.png\\" alt=\\"image.png\\" /></p>\n<p>Use the following SQL query for MySQL or MariaDB:</p>\n<pre><code class=\\"lang-\\">SELECT cons.table_schema as SCHEMA_NAME\\n , count(col_use.COLUMN_NAME) as unique_count\\nFROM information_schema.TABLE_CONSTRAINTS cons\\njoin information_schema.KEY_COLUMN_USAGE col_use on cons.CONSTRAINT_SCHEMA=col_use.CONSTRAINT_SCHEMA \\nand cons.CONSTRAINT_NAME=col_use.CONSTRAINT_NAME \\nand cons.TABLE_SCHEMA=col_use.TABLE_SCHEMA\\nand cons.TABLE_NAME=col_use.TABLE_NAME\\nwhere cons.constraint_type in ('UNIQUE')\\nand cons.table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')\\nand cons.table_schema not like 'aws_db2%'\\ngroup by cons.table_schema\\norder by cons.table_schema;\\n</code></pre>\\n<p>You get the following output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/0e47b2714ace4280b3f8734630a03172_image.png\\" alt=\\"image.png\\" /></p>\n<p>For detailed information, use the following queries.</p>\n<p>The following SQL query is for Db2 LUW:</p>\n<pre><code class=\\"lang-\\">select ind.tabschema as schema_name,\\n ind.tabname as table_name,\\n ind.indname as CONSTRAINT_NAME,\\n 'Unique Index' as constraint_type,\\n cols.colname as column_name\\nfrom syscat.indexes ind\\njoin syscat.indexcoluse cols\\n on ind.indname = cols.indname\\n and ind.indschema = cols.indschema\\nwhere ind.tabschema not like 'SYS%'\\nand ind.uniquerule in ('U')\\norder by schema_name,\\n ind.tabname,\\n ind.indname;\\n</code></pre>\\n<p>You get the following output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/b63929f5a70e48969178832e6aa878a8_image.png\\" alt=\\"image.png\\" /></p>\n<p>Use the following SQL query for MySQL or MariaDB:</p>\n<pre><code class=\\"lang-\\">SELECT cons.table_schema as SCHEMA_NAME\\n, cons.TABLE_NAME\\n, cons.CONSTRAINT_NAME as CONSTRAINT_NAME\\n,'Unique Index' as constraint_type\\n, col_use.COLUMN_NAME\\nFROM information_schema.TABLE_CONSTRAINTS cons\\njoin information_schema.KEY_COLUMN_USAGE col_use on cons.CONSTRAINT_SCHEMA=col_use.CONSTRAINT_SCHEMA \\nand cons.CONSTRAINT_NAME=col_use.CONSTRAINT_NAME \\nand cons.TABLE_SCHEMA=col_use.TABLE_SCHEMA\\nand cons.TABLE_NAME=col_use.TABLE_NAME\\nwhere cons.constraint_type in ('UNIQUE')\\nand cons.table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')\\nand cons.table_schema not like 'aws_db2%'\\norder by cons.table_schema, cons.TABLE_NAME, cons.CONSTRAINT_NAME, col_use.COLUMN_NAME;\\n</code></pre>\\n<p>The following screenshot shows the output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/05fc881847db416b904762ce0258cbd1_image.png\\" alt=\\"image.png\\" /></p>\n<h4><a id=\\"Nonunique_indexes_444\\"></a><strong>Non-unique indexes</strong></h4>\\n<p>MySQL and MariaDB create implicit indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. The MySQL and MariaDB database queries used in this section exclude such indexes so that you can perform the validations against the source without any mismatch.</p>\n<p>Use the following SQL query for Db2 LUW:</p>\n<pre><code class=\\"lang-\\">select ind.tabschema as schema_name,\\n count(cols.colname) as index_count\\nfrom syscat.indexes ind\\njoin syscat.indexcoluse cols\\n on ind.indname = cols.indname\\n and ind.indschema = cols.indschema\\nwhere ind.tabschema not like 'SYS%'\\nand ind.uniquerule in ('D')\\ngroup by ind.tabschema\\norder by schema_name;\\n</code></pre>\\n<p>The output is as follows.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/9f3d5c3f51c24116aa5344371f672c6f_image.png\\" alt=\\"image.png\\" /></p>\n<p>Use the following SQL query for MySQL or MariaDB:</p>\n<pre><code class=\\"lang-\\">SELECT idx.table_schema\\n ,count(idx.column_name) as index_count\\nFROM INFORMATION_SCHEMA.STATISTICS idx\\nWHERE idx.TABLE_SCHEMA not in ('information_schema', 'sys', 'performance_schema', 'mysql', 'DB2INST1')\\nand idx.table_schema not like 'aws_db2%'\\nand idx.index_name not in ('PRIMARY')\\nand idx.non_unique = 1\\nand index_name not like 'FK_%'\\ngroup by idx.table_schema\\norder by idx.table_schema;\\n</code></pre>\\n<p>You get the following output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/38bbfaad6e104ae084a8f4dd0538235f_image.png\\" alt=\\"image.png\\" /></p>\n<p>For detailed information, use the following queries.</p>\n<p>The following SQL query is for Db2 LUW:</p>\n<pre><code class=\\"lang-\\">select ind.tabschema as schema_name,\\n ind.tabname as table_name,\\n ind.indname as index_name,\\n cols.colname as column_name\\nfrom syscat.indexes ind\\njoin syscat.indexcoluse cols\\n on ind.indname = cols.indname\\n and ind.indschema = cols.indschema\\nwhere ind.tabschema not like 'SYS%'\\nand ind.uniquerule in ('D')\\norder by schema_name,\\n ind.tabname,\\n ind.indname,\\n cols.colname;\\n</code></pre>\\n<p>The following screenshot shows the output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/c1570517f08e4eba9412bf9a7f2a5903_image.png\\" alt=\\"image.png\\" /></p>\n<p>Use the following SQL query for MySQL or MariaDB:</p>\n<pre><code class=\\"lang-\\">SELECT idx.table_schema\\n, idx.table_name\\n, idx.index_name\\n, idx.column_name\\nFROM INFORMATION_SCHEMA.STATISTICS idx\\nWHERE idx.TABLE_SCHEMA not in ('information_schema', 'sys', 'performance_schema', 'mysql', 'DB2INST1')\\nand idx.table_schema not like 'aws_db2%'\\nand idx.index_name not in ('PRIMARY')\\nand idx.non_unique = 1\\nand index_name not like 'FK_%'\\norder by idx.table_schema\\n, idx.table_name\\n, idx.index_name\\n, idx.column_name;\\n</code></pre>\\n<p>The output is as follows.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/ced1cf80955b4709baaba1a5a48728ee_image.png\\" alt=\\"image.png\\" /></p>\n<p>Verify the count and the details of the indexes between the source and target database. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In this example, the output is as expected. No issues were detected, so you can move to the next level of validation.</p>\n<h4><a id=\\"Triggers_536\\"></a><strong>Triggers</strong></h4>\\n<p><a href=\\"https://dev.mysql.com/doc/refman/5.7/en/triggers.html\\" target=\\"_blank\\">Triggers</a> define a set of actions that are performed in response to an insert, update, or delete operation on a specified table. The following queries give you the count and details of triggers for both the source and target databases.</p>\\n<p>Use the following Db2 LUW query:</p>\n<pre><code class=\\"lang-\\">Select tabschema as table_schema\\n, count(trigname) as trigger_count\\nFrom\\tsyscat.triggers t\\nwhere tabschema not like 'SYS%'\\ngroup by tabschema\\norder by tabschema;\\n</code></pre>\\n<p>You get the following output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/9eb9ed6dae8f4b30a3da1daac435a156_image.png\\" alt=\\"image.png\\" /></p>\n<p>Use the following MySQL or MariaDB query:</p>\n<pre><code class=\\"lang-\\">SELECT tgr.EVENT_OBJECT_SCHEMA as TABLE_SCHEMA\\n\\t , count(tgr.TRIGGER_NAME) as trigger_count\\nFROM information_schema.triggers tgr\\nwhere tgr.TRIGGER_SCHEMA not in ('sys','performance_schema','mysql')\\nand tgr.TRIGGER_SCHEMA not like 'aws_db2%'\\ngroup by tgr.EVENT_OBJECT_SCHEMA\\norder by tgr.EVENT_OBJECT_SCHEMA;\\n</code></pre>\\n<p>The output is as follows.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/3bf2548bdf6b432187a77b5f25711030_image.png\\" alt=\\"image.png\\" /></p>\n<p>For detail-level information, use the following queries.</p>\n<p>The following SQL query is for Db2 LUW:</p>\n<pre><code class=\\"lang-\\">select\\n tabschema as table_schema,\\n trigname as trigger_name,\\n tabname as table_name,\\n case trigtime\\n when 'B' then 'before'\\n when 'A' then 'after'\\n when 'I' then 'instead of'\\n end as activation,\\n rtrim(case when eventupdate ='Y' then 'update ' else '' end\\n ||\\n case when eventdelete ='Y' then 'delete ' else '' end\\n ||\\n case when eventinsert ='Y' then 'insert ' else '' end)\\n as event\\nfrom syscat.triggers t\\nwhere tabschema not like 'SYS%'\\norder by table_name, trigger_name;\\n</code></pre>\\n<p>You get the following output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/d0cd1c42327f44a38ed0b8f4692a7a7f_image.png\\" alt=\\"image.png\\" /></p>\n<p>Use the following MySQL or MariaDB query:</p>\n<pre><code class=\\"lang-\\">SELECT tgr.EVENT_OBJECT_SCHEMA as TABLE_SCHEMA\\n,tgr.TRIGGER_NAME\\n\\t,tgr.EVENT_OBJECT_TABLE as Table_name\\n,tgr.ACTION_TIMING as activation\\n,tgr.EVENT_MANIPULATION as event\\nFROM information_schema.triggers tgr\\nwhere tgr.TRIGGER_SCHEMA not in ('sys', 'performance_schema', 'mysql')\\nand tgr.TRIGGER_SCHEMA not like 'aws_db2%';\\n</code></pre>\\n<p>The following screenshot shows the output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/1124baad8e754a4094711606dd211678_image.png\\" alt=\\"image.png\\" /></p>\n<p>Verify the trigger count and details between the source and target database. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In the preceding example, the output is as expected. No issues were detected, so you can move to the next level of validation.</p>\n<h4><a id=\\"Procedures_619\\"></a><strong>Procedures</strong></h4>\\n<p>A stored procedure is a collection of precompiled SQL statements stored in a database that can be called from an application program. Stored procedures have better performance compared to inline queries because the SQL queries are precompiled with reusable execution plans. It also improves productivity because similar SQL statements or the business logic are consolidated and reused across applications or other programs. The following queries give you the count and details of stored procedures for both the source and target databases.</p>\n<p>The following is SQL query for Db2 LUW:</p>\n<pre><code class=\\"lang-\\">select routineschema as schema_name\\n,count(*) as proc_count\\nfrom syscat.routines\\nwhere routinetype = 'P'\\n and routineschema not like 'SYS%'\\n and routineschema not like 'SQLJ%'\\ngroup by routineschema\\norder by routineschema;\\n</code></pre>\\n<p>You get the following output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/5fc2e514841a42788933c355c23cc8fe_image.png\\" alt=\\"image.png\\" /></p>\n<p>Use the following SQL query for MySQL or MariaDB:</p>\n<pre><code class=\\"lang-\\">SELECT rtn.ROUTINE_SCHEMA\\n ,count(*) as proc_count\\nFROM information_schema.ROUTINES rtn\\nwhere rtn.ROUTINE_TYPE = 'PROCEDURE'\\nand rtn.ROUTINE_SCHEMA not in ('information_schema','sys', 'performance_schema', 'mysql')\\nand rtn.ROUTINE_SCHEMA not like 'aws_db2%'\\ngroup by rtn.ROUTINE_TYPE\\norder by rtn.ROUTINE_TYPE;\\n</code></pre>\\n<p>The output is as follows.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/7e3ca5214d65455d92e8015de79a7166_image.png\\" alt=\\"image.png\\" /></p>\n<p>For detail-level information, use the following queries.</p>\n<p>The following is SQL query for Db2 LUW:</p>\n<pre><code class=\\"lang-\\">select routineschema as schema_name,\\n routinename as procedure_name\\nfrom syscat.routines\\nwhere routinetype = 'P'\\n and routineschema not like 'SYS%'\\n and routineschema not like 'SQLJ%'\\norder by schema_name,\\n procedure_name;\\n</code></pre>\\n<p>You get the following output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/d376e306c3304d52b7e5746050b9fe2a_image.png\\" alt=\\"image.png\\" /></p>\n<p>Use the following SQL query for MySQL or MariaDB:</p>\n<pre><code class=\\"lang-\\">SELECT rtn.ROUTINE_TYPE\\n\\t ,rtn.ROUTINE_SCHEMA\\n ,rtn.ROUTINE_NAME\\nFROM information_schema.ROUTINES rtn\\nwhere rtn.ROUTINE_TYPE = 'PROCEDURE'\\nand rtn.ROUTINE_SCHEMA not in ('information_schema','sys', 'performance_schema', 'mysql')\\nand rtn.ROUTINE_SCHEMA not like 'aws_db2%';\\n</code></pre>\\n<p>The following screenshot shows the output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/aa873fa03f274e0a81c896c0ca04c696_image.png\\" alt=\\"image.png\\" /></p>\n<p>Verify the count and the details of procedures between the source and target database. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In this example, the output is as expected. No issues were detected, so you can move to the next level of validation.</p>\n<h4><a id=\\"Functions_694\\"></a><strong>Functions</strong></h4>\\n<p>Functions implement specific business or functional logic based on the given input and return a predefined output. The following queries give you the count and details of functions for both the source and target databases.</p>\n<p>Use the following SQL query for Db2 LUW:</p>\n<pre><code class=\\"lang-\\">select routineschema as schema_name,\\n count(*) as proc_count\\nfrom syscat.routines\\nwhere routinetype = 'F'\\n and routineschema not like 'SYS%'\\n and routineschema not like 'SQLJ%'\\ngroup by routineschema\\norder by routineschema;\\n</code></pre>\\n<p>The following screenshot shows the output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/5ee33db7f6fd4750bbb57dfb64f3afec_image.png\\" alt=\\"image.png\\" /></p>\n<p>The following is SQL query for MySQL or MariaDB:</p>\n<pre><code class=\\"lang-\\">SELECT rtn.ROUTINE_SCHEMA\\n\\t,count(*) as proc_count\\nFROM information_schema.ROUTINES rtn\\nwhere rtn.ROUTINE_TYPE = 'FUNCTION'\\nand rtn.ROUTINE_SCHEMA not in ('information_schema', 'sys', 'performance_schema', 'mysql')\\nand rtn.ROUTINE_SCHEMA not like 'aws_db2%'\\ngroup by rtn.ROUTINE_TYPE\\norder by rtn.ROUTINE_TYPE;\\n</code></pre>\\n<p>You get the following output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/2826d7350ed945b3b249b0ad5ccf4779_image.png\\" alt=\\"image.png\\" /></p>\n<p>For detail-level information, use the following queries.</p>\n<p>Use the following SQL query for Db2 LUW:</p>\n<pre><code class=\\"lang-\\">select routineschema as schema_name,\\n routinename as procedure_name\\nfrom syscat.routines\\nwhere routinetype = 'F'\\n and routineschema not like 'SYS%'\\n and routineschema not like 'SQLJ%'\\norder by schema_name,\\n procedure_name;\\n</code></pre>\\n<p>The output is as follows.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/48dcb20f9f1c4664a51e8786cb216e50_image.png\\" alt=\\"image.png\\" /></p>\n<p>Use the following SQL query for MySQL or MariaDB:</p>\n<pre><code class=\\"lang-\\">SELECT rtn.ROUTINE_SCHEMA\\n ,rtn.ROUTINE_NAME\\nFROM information_schema.ROUTINES rtn\\nwhere rtn.ROUTINE_TYPE = 'FUNCTION'\\nand rtn.ROUTINE_SCHEMA not in ('information_schema','sys', 'performance_schema', 'mysql')\\nand rtn.ROUTINE_SCHEMA not like 'aws_db2%';\\n</code></pre>\\n<p>The following screenshot shows the output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/68d81a55eba34988b462a041f1bb356d_image.png\\" alt=\\"image.png\\" /></p>\n<p>Verify the count and the details of functions between the source and target database. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In the preceding example, the output is as expected. No issues were detected, so you can move to the next level of validation.</p>\n<h4><a id=\\"Validating_datapartitioned_tables_768\\"></a><strong>Validating data-partitioned tables</strong></h4>\\n<p>Partitioned tables use a data organization scheme in which table data is divided across multiple storage objects, called <em>data partitions</em> or <em>ranges</em>, according to values in one or more table partitioning key columns of the table. You can use the following queries to compare the partitioned tables between the source and target.</p>\\n<p>The following is SQL query for Db2 LUW:</p>\n<pre><code class=\\"lang-\\">select dp.tabschema as table_schema\\n, dp.tabname as table_name\\n, datapartitionname as partition_name\\n, char(datapartitionexpression,30) as partition_expression\\n, highvalue as partition_description\\nfrom SYSCAT.DATAPARTITIONS dp \\ninner join syscat.datapartitionexpression dpe on dp.tabschema=dpe.tabschema\\nand dp.tabname=dpe.tabname\\nwhere dp.tabschema='GOASALESDW';\\n</code></pre>\\n<p>You get the following output.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/3f1bc3d6ca854336aafb35db79286f9d_image.png\\" alt=\\"image.png\\" /></p>\n<p>The following SQL query is for MySQL or MariaDB:</p>\n<pre><code class=\\"lang-\\">select table_schema\\n, table_name\\n, partition_name\\n, partition_expression\\n, partition_description\\nFROM INFORMATION_SCHEMA.PARTITIONS \\nwhere table_schema='GOASALESDW';\\n</code></pre>\\n<p>The output is as follows.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/edd5b490600845f2a6eff84f7f87e159_image.png\\" alt=\\"image.png\\" /></p>\n<p>Verify the count and the details of partitioned data tables between the source and target database. If any differences are found, identify the cause of failure from the deployment logs and fix them accordingly. In the preceding example, the output is as expected. No issues were detected, so you can move to the next level of validation.</p>\n<h4><a id=\\"Useful_MySQL_or_MariaDB_catalog_tables_808\\"></a><strong>Useful MySQL or MariaDB catalog tables</strong></h4>\\n<p>The following table summarizes some of the Db2 LUW objects and their corresponding objects on MySQL and MariaDB that are helpful for database object validation. For MySQL databases with many objects and concurrent workloads, the queries provided in this post can take longer duration to complete. This can be improved to some extent using <a href=\\"https://dev.mysql.com/doc/refman/8.0/en/information-schema-optimization.html\\" target=\\"_blank\\">data dictionary changes</a> in MySQL 8.0.</p>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/4e63d1fbab6941b28e100efae355cea8_%E6%88%AA%E5%B1%8F2022-08-28%2017.18.14.png\\" alt=\\"截屏20220828 17.18.14.png\\" /></p>\n<h4><a id=\\"Handling_objects_not_supported_in_MySQL_or_MariaDB_814\\"></a><strong>Handling objects not supported in MySQL or MariaDB</strong></h4>\\n<p>For the Db2 LUW objects not supported by MySQL or MariaDB (like aliases, sequences, or materialized query tables), you must perform the migration from source to target database manually to achieve the functionality that exists in the source database. You can use the queries provided in this post to iteratively validate the migrated objects to identify gaps and fix them.</p>\n<h4><a id=\\"Conclusion_818\\"></a><strong>Conclusion</strong></h4>\\n<p>Database object validation is an essential step that provides an in-depth view of the migration accuracy. It confirms whether all the database objects are migrated appropriately and the integrity of target database. It ensures business continuity of the dependent application processes.</p>\n<p>In this post, we discussed post-migration validation of database objects with the metadata queries for a Db2 LUW source and Amazon RDS for MySQL, Amazon RDS for MariaDB, or Aurora MySQL target database. You can run the queries provided in this post on your source and target database to retrieve the metadata and compare the output to confirm if your migration was successful.</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_826\\"></a><strong>About the Authors</strong></h4>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/65f6dbfa941f43d5ac3a4c750841797d_image.png\\" alt=\\"image.png\\" /></p>\n<p><strong>Sai Parthasaradhi</strong> is a Database Migration Specialist with AWS Professional Services. He works closely with customers to help them migrate and modernize their databases on AWS.</p>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/25a50fba619641b18d65ad0f0f8472a1_image.png\\" alt=\\"image.png\\" /></p>\n<p><strong>Vikas Gupta</strong> 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.</p>\n"}