Comparing two schemas

Comparing schemas involves generating and viewing a set of reports. The reports list the objects that need to be created, altered, or dropped to make the two structures identical.

BMC AMI Command Center

generates the Change Definition Language (CDL) that defines the changes that must be applied to make the schema's structures identical. You can save the CDL to a data set and edit the CDL in the Scratchpad editor.

BMC AMI Command Center enables you to make the following comparisons:

Db2 catalog to Db2 catalog
DDL to DDL
Db2 catalog to DDL
DDL to Db2 catalog

Note

BMC AMI Command Center can generate DDL statements containing lines that are longer than 72 characters. These DDL statements cannot be used in a schema comparison.

The same set of reports is available for each type of comparison. For more information, see Schema-comparison-reports.

Use the following procedure to create and display a Schema Compare View for each comparison that you want to see. You can also navigate to and run a saved schema comparison.

To create a Schema Compare View

The comparison requires two inputs:

The schema or set of Db2 objects that has the structure definitions that you want to change (called the Primary Input in the procedure)
The schema that has the preferred structure definitions, to serve as the basis for changing the other schema (called the Secondary Input)

Note

After you have specified the two data structures for comparison, you can click Finish without changing the default dependent objects, attributes, or options values.

New console

In the Schema Management perspective, click Add Schema Compare View .The Compare wizard opens.
On the wizard's General page, complete the following fields and click Next:
Field
Description
Name
Enter a name for the report.
Select a report
Select to create a new report or use an existing report.
On the wizard's next page, take the appropriate action as follows:
- If you are using an existing report, complete these actions:
  1. On the wizard's Comparison Dataset page, navigate to and select the data set that contains the report and click Next.
    For more information, see Viewing and editing a data set.
  2. (Optional) In the wizard's Options page, select Enable Trace to provide informational messages in the View Messages dialog box.
  3. Click Finish.
  4. Click Show Compare Report to rerun the report.
- If creating a new report, on the wizard's Type of Comparison page, complete the following fields and click Next:
  
  Field
  Description
  Compare Type
  Select the type of comparison that you require.
  Output Compare Report
  Navigate to and select a data set to save the generated CDL output of the comparison.

Field	Description
Name	Enter a name for the report.
Select a report	Select to create a new report or use an existing report.

Field	Description
Compare Type	Select the type of comparison that you require.
Output Compare Report	Navigate to and select a data set to save the generated CDL output of the comparison.

On the wizard's Data Structures page, complete the following fields according to the type of comparison that you are performing:

Field	Description
Db2 objects that do not have the desired structure (primary input)
Object Type	Select the object type.
DB2 Connection	Select the Db2 connection from your list of favorites or add a Db2 connection to the list. For more information, see Connecting-to-Db2-subsystems.
Filter	Specify a filter for the name of the Db2 objects that you want to compare.
Db2 objects that have the desired structure (secondary input)
DB2 Connection	Select the Db2 connection from the list.
Filter	Specify a filter for the name of the Db2 objects that you want to compare.

Field	Description
Db2 objects that do not have the desired structure (primary input)
DB2 Connection	Select the Db2 connection from your list of favorites or add a Db2 connection to the list, as explained in Connecting-to-Db2-subsystems.
DDL Data Set	Select the DDL data set. This data set defines the scope of the comparison. For more information, see Viewing and editing a data set.
Db2 objects that have the desired structure (secondary input)
DDL Data Set	Select the DDL data set. For more information, see Viewing and editing a data set.

Field	Description
Db2 objects that do not have the desired structure (primary input)
DB2 Connection	Select the Db2 connection where the schema is stored.
Db2 objects that have the desired structure (secondary input)
DDL Data Set	Select the DDL data set. This data set defines the scope of the comparison. For more information, see Viewing and editing a data set.

Field	Description
Db2 objects that do not have the desired structure (primary input)
DDL Data set	Select the DDL data set. This data set defines the scope of the comparison. For more information, see Viewing and editing a data set.
Db2 objects that have the desired structure (secondary input)
DB2 Connection	Select the Db2 connection where the schema is stored.

Click Next.
On the wizard's Dependent objects page, select the dependent objects to include in the comparison and click Next.
Note
To compare a Db2 catalog to a Db2 catalog, the Resolve object or creator name differences check box is enabled by default.
When this option is enabled, the product excludes databases, table spaces, synonyms, and aliases from the comparison because duplicates might occur. The product performs this name conversion to resolve the differences in the object owners, schemas, and database names.
On the wizard's Attributes page, select the attributes for each object type to include in the comparison and click Next.
On the wizard's Options page, specify the option values for the current definition and click Next.These values do not affect the global compare options that you specify by using the Options button .
Click Finish.The Reports tab displays the reports for this comparison. For more information, see Schema-comparison-reports.
(Optional) If you want to view the generated CDL for the comparison, click Show CDL to view the CDL in the Scratchpad.
Note
You cannot run the CDL from the Scratchpad.
(Optional) If you want to rerun the comparison, click Show Compare Report .

Classic console

In the Schema Management perspective, click Add Schema Compare View .The Define a Comparison wizard opens.
On the wizard's Step 1-General page, complete the following fields and click Next:
Field
Description
Name
Enter a name for the report.
Select a report
Select to create a new report or use an existing report.
On the wizard's Step 2 page, take the appropriate action as follows:
- If you are using an existing report, complete these actions:
  1. On the wizard's Step 2 - Comparison Dataset page, navigate to and select the data set that contains the report and click Next.
    For more information, see Viewing and editing a data set.
  2. (Optional) In the wizard's Step 3 - Options page, select Enable Trace to provide informational messages in the View Messages dialog box.
  3. Click Finish.
  4. Click to rerun the report.
- If creating a new report, on the wizard's Step 2-Type of Comparison page, complete the following fields and click Next:
  
  Field
  Description
  Compare Type
  Select the type of comparison that you require.
  Output Compare Report
  Navigate to and select a data set to save the output of the comparison.

Field	Description
Name	Enter a name for the report.
Select a report	Select to create a new report or use an existing report.

Field	Description
Compare Type	Select the type of comparison that you require.
Output Compare Report	Navigate to and select a data set to save the output of the comparison.

On the wizard's Step 3 - Data Structures page, complete the following fields according to the type of comparison that you are performing:

Field	Description
DB2 objects that do not have the desired structure (primary input)
Object Type	Select the object type.
DB2 Connection	Select the Db2 connection from your list of favorites or add a Db2 connection to the list. For more information, see Connecting-to-Db2-subsystems.
Filter	Specify a filter for the name of the Db2 objects that you want to compare.
DB2 objects that have the desired structure (secondary input)
DB2 Connection	Select the Db2 connection from the list.
Filter	Specify a filter for the name of the Db2 objects that you want to compare.

Field	Description
DB2 objects that do not have the desired structure (primary input)
DB2 Connection	Select the Db2 connection from your list of favorites or add a Db2 connection to the list, as explained in Connecting-to-Db2-subsystems.
DDL Data Set	Select the DDL data set. This data set defines the scope of the comparison. For more information, see Viewing and editing a data set.
DB2 objects that have the desired structure (secondary input)
DDL Data Set	Select the DDL data set. For more information, see Viewing and editing a data set.

Field	Description
DB2 objects that do not have the desired structure (primary input)
DB2 Connection	Select the Db2 connection where the schema is stored.
DB2 objects that have the desired structure (secondary input)
DDL Data Set	Select the DDL data set. This data set defines the scope of the comparison. For more information, see Viewing and editing a data set.

Field	Description
DB2 objects that do not have the desired structure (primary input)
DDL Data set	Select the DDL data set This data set defines the scope of the comparison. For more information, see Viewing and editing a data set.
DB2 objects that have the desired structure (secondary input)
DB2 Connection	Select the Db2 connection where the schema is stored.

Click Next.
On the wizard's Step 4 - Dependent objects page, select the dependent objects to include in the comparison and click Next.
Note
To compare a DB2 catalog to a DB2 catalog, the Resolve object or creator name differences check box is enabled by default.
When this option is enabled, the product excludes databases, table spaces, synonyms, and aliases from the comparison because duplicates might occur. The product performs this name conversion to resolve the differences in the object owners, schemas, and database names.
On the wizard's Step 5 - Attributes page, select the attributes for each object type to include in the comparison and click Next.
On the wizard's Step 6 - Options page, specify the option values for the current definition and click Next.These values do not affect the global compare options that you specify by using the Options button .
Click Finish.The Reports tab displays the reports for this comparison. For more information, see Schema-comparison-reports.
(Optional) If you want to view the generated CDL for the comparison, click Show CDL to view the CDL in the Scratchpad.
Note
You cannot run the CDL from the Scratchpad.
(Optional) If you want to rerun the comparison, click Run .

To copy a Schema Compare View

This feature is available in Classic console only.

You can also copy all the definitions (name, type and output, data structures, dependent objects, attributes, and options) of an existing Schema Compare View.

If you want to make a backup copy of the output data set before editing, click Copy Existing Report in step 2. A copy of the selected schema Compare View is created, but you cannot edit the definitions.

In the Navigation pane, right-click a Schema Compare View.
Click Copy Definition.
A copy of the selected Schema Compare View is created.
Edit the Schema Compare View.
Save the Workspace.

Comparing two schemas

To create a Schema Compare View

To copy a Schema Compare View

On this page