Comparing Environments

Overview

The Structure Compare Task (SC) is used to identify differences between two database environments.

It compares database structures such as:

  • tables

  • columns

  • indexes

  • properties and configurations of database objects

This guide explains how to configure and use the SC Task with DB2 LUW databases as an example.

The DDL for the tables used in this example can be found here.

Prerequisites

Before using the Structure Compare Task, make sure that:

  • both environments are accessible

  • the executing user has sufficient database privileges

  • the required tables and schemas can be read

Steps

This example compares two DB2 LUW tables located in different schemas:

  • Source table: EXAMPLEDB.CUSTOMER

  • Target table: TESTEX1.CUSTOMER

Step 1: Select Objects for Comparison

Choose the objects that should be compared.

In this example, select the source table EXAMPLEDB.CUSTOMER.

Create a Selection Rule:

  • Object Type: TABLE

  • Schema Pattern: EXAMPLEDB

  • Name Pattern: CUSTOMER

To compare entire environments, set the schema pattern and name pattern of the selection rule to %. Exclude unnecessary tables, such as catalog tables, by using Exclude Rules.

Next, create a Mapping Rule to map the source schema EXAMPLEDB to the target schema TESTEX1.

Create a Mapping Rule:

  • Object Type: ANY

  • Field Type: SCHEMA

  • Source Selection Pattern: %

  • Target Value: TESTEX1

This maps the source object EXAMPLEDB.CUSTOMER to the target object TESTEX1.CUSTOMER.

Step 2: Define Comparable Fields

Specify which fields should be considered during comparison.

These fields define which properties of the database objects are evaluated.

For example, when comparing DB2 LUW tables and columns, use fields such as:

  • TABLES_REMARKS

  • TABLES_CODEPAGE

  • TABLES_CARD

  • TABLES_TBSPACE

  • TABLES_INDEX_TBSPACE

  • TABLES_DROPRULE

  • COLUMNS_TYPENAME

  • COLUMNS_LENGTH

  • COLUMNS_DEFAULT

Add additional fields as needed.

Make sure to include all relevant fields for a comprehensive comparison.

Step 3: Exclude Additional Objects

Exclude object types that are not relevant for the comparison.

For example, to skip index comparisons, create 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

If an object exists only in the source environment or only in the target environment, one Exclude Rule for the corresponding scope is sufficient.

Step 4: Execute the Task

Run the Structure Compare Task with the configured:

  • selection rules

  • mapping rules

  • comparable fields

  • exclude rules

The task generates a Structure Difference Report that shows the detected 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
------------------------------------------------------------------------------------------------------------------------------------------------------------------

Step 5: Ignore Known Differences

Known differences can be excluded from the report.

This helps to focus on relevant or unexpected differences.

For example, to ignore differences in column data types, create an Ignore Difference Rule for the property COLUMNS_TYPENAME.

Ignore Difference Rule:

  • Property name: COLUMNS_TYPENAME

  • Scope: Source

  • Value: VARCHAR

Re-run the task to generate an updated report without the ignored differences.

Step 6: Fail on Differences

After the Structure Compare Task is configured correctly, only relevant objects are compared and known differences are ignored.

If the structure changes later, only new or unexpected differences are shown in the report.

To make the task fail when new differences are detected, enable the Abort on difference option.

With this option enabled, the task is aborted if new differences occur.

Benefits

The Structure Compare Task provides several benefits:

  • Streamlined comparison: Quickly identify differences in database structures.

  • Customizable analysis: Compare only relevant objects and properties.

  • Focused reporting: Exclude known or irrelevant differences.

  • Actionable insights: Use the report to align database environments.

  • Quality control: Abort the task when unexpected structural differences occur.

Conclusion

Use the Structure Compare Task to compare database structures and identify discrepancies between environments.

A proper configuration of selection rules, mapping rules, comparable fields, exclude rules, and ignore difference rules ensures accurate and focused results.

The generated report helps maintain consistency across database systems and supports controlled environment synchronization.