Using Structure Compare Task to Compare Environments

Overview

The Structure Compare (SC) Task is a powerful tool designed to identify differences between two database environments. It allows users to compare structures such as tables, columns, and indexes, highlighting discrepancies in properties and configurations. This guide provides a step-by-step explanation of how to use the SC Task effectively, with DB2 LUW databases as an example. The DDL for the tables used in this example can be found here.

Prerequisites

Before starting configuring the SC Task, ensure the following prerequisites are met:

  • Database Access: Access to both the source and target database environments.

  • Technical Knowledge: Familiarity with SQL and database schema concepts.

Steps to Configure the Structure Compare Task

This section outlines the procedure for identifying differences between two database environments using the SC Task. We will use two DB2 LUW tables, located in different schemas, as an example.

1. Select Objects for Comparison

  • Choose the specific objects to compare. In our example, select the table EXAMPLEDB.CUSTOMER as the source table. For this, create a Selection Rule:

    • Object Type: TABLE

    • Schema Pattern: EXAMPLEDB

    • Name Pattern: CUSTOMER

      To compare entire environments, set the selection rule schema and name pattern to % and exclude unnecessary tables (e.g., catalog tables) using Exclude Rules.

  • Set a Mapping rule to map EXAMPLEDB.CUSTOMER from source environment to TESTEX1.CUSTOMER in target environment. For this, create a Mapping Rule:

    • Object Type: ANY

    • Field Type: SCHEMA

    • Source Selection Pattern: %

    • Target Value: TESTEX1

2. Define Comparable Fields

Specify the fields that should be considered during comparison. These fields determine what properties of the database objects are evaluated. For example, when comparing DB2 LUW tables and columns, use the following fields:

  • TABLES_REMARKS

  • TABLES_CODEPAGE

  • TABLES_CARD

  • TABLES_TBSPACE

  • TABLES_INDEX_TBSPACE

  • TABLES_DROPRULE

  • COLUMNS_TYPENAME

  • COLUMNS_LENGTH

  • COLUMNS_DEFAULT

  • [Add additional fields as needed]

Ensure to include all relevant fields in the configuration for comprehensive comparisons.

3. Exclude Additional Objects

If certain object types are not relevant to the comparison, exclude them. For example:

  • To skip index comparisons, create two Exclude Rules for both scopes:

  • Exclude Rule for scope Source:

    • Object Type: INDEX

    • Schema Pattern: %

    • Name Pattern: %

    • Scope: Source

  • Exclude Rule for scope Target:

    • Object Type: INDEX

    • Schema Pattern: %

    • Name Pattern: %

    • Scope: Target

For excluding objects, that do exist only in source environment or in target environment, it is only necessary to create one exclude rule.

4. Execute the Task

Run the SC Task with the configured selection, comparison fields, and exclusion rules. The task generates a Structure Difference Report outlining the discrepancies.

Example output:

Report name:Structure compare difference report
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Object type                | Parent object                | Object name              | Compared object          | Result                   | Mapping
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table                      |                              | EXAMPLEDB.CUSTOMER       | TESTEX1.CUSTOMER         | Different                | true
| Column                     | EXAMPLEDB.CUSTOMER           | EMAIL                    | EMAIL                    | Different                | false
| PROPERTY: COLUMNS_LENGTH   | EXAMPLEDB.CUSTOMER.EMAIL     | 50                       | 150                      | Different                | false
| Column                     | EXAMPLEDB.CUSTOMER           | CUST_NAME                | CUST_NAME                | Different                | false
| PROPERTY: COLUMNS_TYPENAME | EXAMPLEDB.CUSTOMER.CUST_NAME | VARCHAR                  | CHARACTER                | Different                | false
| Column                     | EXAMPLEDB.CUSTOMER           | -                        | CUST_NR                  | Missing                  | false
------------------------------------------------------------------------------------------------------------------------------------------------------------------

5. Ignore Known Differences

Known discrepancies can be excluded from the report to focus on critical issues. For example:

  • Ignore differences in column data types by creating an Ignore Difference Rule for the property COLUMNS_TYPENAME with:

    • Property name: COLUMNS_TYPENAME

    • Scope: Source

    • Value: VARCHAR

Re-run the task to generate an updated report excluding these differences.

6. Fail on differences

If the Structure Compare Task is now set up so that only the necessary objects are compared and all known differences are ignored, the task should run successfully. If the structure then changes over time, only the differences that have been added are displayed in the report. If the Abort on difference option is set in the task, the task is aborted if new differences occur.

Benefits

The SC Task offers numerous advantages: - Streamlined Comparison: Quickly identify differences in database structures. - Customizable Analysis: Focus on specific properties and exclude irrelevant data. - Actionable Insights: The report provides clear guidance for aligning environments.

Conclusion

By following this guide, you can efficiently use the SC Task to compare database structures and address discrepancies. Proper configuration ensures accurate results, enabling streamlined environment synchronization. Use the insights from the comparison to optimize and maintain consistency across your database systems.