You can create a schema comparison job to capture, compare, and synchronize database schemas. In addition, you can compare a schema against an archived version of a schema. For more information, see Creating a schema archive.
The following tasks step you through creating and running a schema comparison job:
After comparing the data, you can synchronize it to match; see Synchronizing Data Repositories.
Create or Open a Schema Comparison Job
You create, modify, and run a schema comparison job in the Schema Comparison Job editor.
To create a new schema comparison job
- Select File > New > Schema Comparison Job. The Schema Comparison Job editor opens.
To open an existing schema comparison job
- Select File > Open Job. Select a job from the list, and then click OK. The Schema Comparison Job editor opens.
Describe and Save the Schema Comparison Job
The Job Name and Description pane shows where the job is stored in Project Explorer. When you click the Details button, you can see the project’s file directory path.
If the job is used to monitor compliance against company standards or policies, you can add it to the Compliance Explorer list. This list lets you quickly see all the jobs that have recently passed or failed the compliance criteria you set.
To describe and save a job
- Type a new name for the job in the Name field. Enter a meaningful name that will quickly and uniquely identify the job in other views and dialogs.
- Add relevant information in the Notes field.
- Click Track Results in Compliance Explorer if you want the job to be listed in the Compliance Explorer.
- Click File > Save or File > Save All, or highlight the editor window, and then press Ctrl+S.
Specify a Schema Comparison Source and Targets
The Schema Comparison Source and Schema Comparison Target panes identify what data sources or schema archives are compared when the job runs. The Schema Comparison Job editor displays the Name, Type, and Host of the source and target.
You can also use a schema archive in a schema comparison job, to compare an older schema against a new one. For more information, see Creating a schema archive.
In addition, you can use the following radio buttons to specify whether to compare the current properties of the live data source, or an archived set of properties previously stored in DB Change Manager.
- Select Use Live Sources to compare the current properties of the data source when the job is executed.
- Select Use an Archive, and then select the schema archive and version from the pulldown menu.
To add a data source to a schema comparison job
- Click and drag a data source from Data Source Explorer to the Schema Comparison Source or Schema Comparison Target box, or click Change Data Source, and then choose a data source from the dialog box.
To add a schema archive to a schema comparison job
- Click and drag a schema archive from Data Source Explorer to the Schema Comparison Source or Schema Comparison Target box.
To change a schema archive or data source in a schema comparison job
- Click Change Data Source in the appropriate box, and then select a new data source from the dialog; or drag a different data source from Data Source Explorer to the box.
Cross-Platform Schema Comparisons Supported by DB Change Manager
DB Change Manager supports cross-platform schema comparisons. However, such jobs are limited to the following DBMS combinations and data source objects:
| DBMS Source/Target | Objects Compared |
|---|---|
| Sybase / SQL Server | Tables, Unique Keys, Check Constraints, Foreign Keys, Indexes, Primary Keys, Views |
| Oracle / SQL Server | Tables, Unique Keys, Check Constraints, Foreign Keys, Indexes, Primary Keys, Views |
If you attempt to compare two data sources that are not compatible, the Schema Comparison Job editor does not let you place objects in the Source or Target panes.
Add Multiple Schema Comparison Job Targets
You can add more targets to compare a source schema against multiple target schemas.
To add multiple targets to a schema comparison job
Drag and drop additional data sources or schema archives from Data Source Explorer to the Schema Comparison Target box, or click the Add Archive or Add Data Source buttons beneath the table and select the appropriate target from the dialog box.
Refine Schema Comparisons
You can refine the schema comparison to use only the schema objects you want to include using the Refinements tab in the Schema Comparison Job editor.
- The Object Refinement pane lists the database objects of the comparison source. Objects are listed by owner (if applicable to the database platform) or by object type.
- The Filter by Owner and Filter by Type panes let you add or remove objects from the Object Refinement pane by owner (if applicable to the database platform) or by object type.
To specify the objects used in a schema comparison job
- In the Refinements tab, select or deselect objects in the Filter by Owner and/or Filter by Type panes to add or remove objects from the Object Refinement pane.
- Select or deselect the objects in the Object Refinement pane.
Customize Schema Mapping
The mapping process pairs objects between the job source and target so that the objects can be compared. You can see and modify pairs using the Mapping tab of the Schema Comparison Job editor.
DB Change Manager automatically maps schema objects based on its interpretation of both schemas. Elements that do not match are initially excluded; you can manually map them prior to running the job.
The Refinements Mapping table lists schema elements for the job source and target.
By default, new objects are included in this list. For more information, see CM/Schema > Refinements Options.
When comparing master catalogs or user mappings, you can select multiple targets against a single source.
To redefine schema object mappings
- In the Refinements Mapping tab, click the target of the pair you want to change. Use the drop-down menu to define the object you prefer.
To restore original schema object mappings
- Click Restore Default Mappings to revert to the original object mappings as determined by DB Change Manager.
Specify Schema Comparison Job Options
Prior to running a job, you can set various options to control aspects of the comparison process. Set them using the Options tab.
The list of options differs depending on the DMBS of the source and target data sources. Options differ according to the objects included in the job. For example, Ignore Constraint Name Differences do not appear if you exclude constants from the job.
The options you see in the job have defaults. You can change the defaults using Preferences.
Schema comparison job options include:
- Synchronization Options. These options specify how you want DB Change Manager to handle the synchronization script after it runs the comparison job, either:
- Manual causes DB Change Manager to display the results of the comparison, but it does not automatically attempt to repair any synchronization issues. If you choose this option, you will still be able to manually generate and run a synchronization script after the job runs.
- Automatic causes DB Change Manager to automatically generate and run a synchronization script immediately after the comparison job runs.
- Include Object Options. These settings determine what elements of the job source and target will be omitted when running a schema comparison job and synchronization. For more information, see CM/Schema > Comparison Options > Include Objects Options.
- Compare Options. These settings affect how objects are treated in the comparison process. You can include or exclude specific object types in the job, and specify the behavior of the comparison job and synchronization scripts. For more information, see CM/Schema > Comparison Options > Compare Options.
- Decorator Options. These settings affect particular database objects. For more information, see CM/Schema > Comparison Options > Decorator Options.
- Identifier Options. These settings determine what elements of the source and target data source will be ignored during the schema comparison and synchronization process. For more information, see CM/Schema > Comparison Options > Identifier Options.
- DDL Extract Option. This setting causes the DDL code to appear in a separate editor window after the comparison job runs. For more information, see CM/Schema > Comparison Options > DDL Extract Options.
- Custom Options. This section lists options that you defined in the Create New Schema Custom Compare Options dialog. For more information, see Select Schema Properties to Ignore During a Comparison
As needed, DBMS-specific options settings are also listed. For more information, see CM/Schema > Comparison Options > Additional Options.