You can create a data comparison job to compare the data of two registered data sources, specify configuration adjustments and refine mapping parameters. After running the job, DB Change Manager can generate SQL scripts which you can run to synchronize any discrepancies. You can run the scripts to update data sources immediately, or store the scripts for later use.
DB Change Manager displays data comparison job results at row level, and it can automatically update mismatches or add missing rows at the table level.
The following tasks step you through creating and running a data comparison job:
After comparing the data, you can synchronize it to match. For more information about this feature, see Synchronizing Data Repositories.
Create a Data Comparison Job
You create, modify, and run a Data Comparison job in the Data Comparison Job editor. For more details about the editor, see Data Comparison Job Editor.
To create a new data comparison job
Select File > New > Data Comparison Job and the Data Comparison Job editor opens.
To open an existing data comparison job
- Select File > Open Job.
- Select a data comparison job from the list.
- Click OK.
The Data Comparison Job editor opens.
Describe and Save the Job
The Job Name and Description pane contains more than just the Name field. It shows where the job is stored in Project Explorer. When you click the Details button, you can see the project’s directory path.
If the job will be 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. Compliance is used most commonly for configuration comparisons.
- Click File > Save or File > Save All, or highlight the editor window and press Ctrl+S.
Specify the Source and Target Data Sources
The Data Comparison Source and Data Comparison Target boxes identify what data sources are compared when the job runs. When a data source is added to a job, its name, database type, and host are displayed. The Databases drop down menu lets you select from a list of the databases on the source.
To add a data source to a job
Click and drag a data source from Data Source Explorer to the Data Comparison Source or Data Comparison Target box, or click Select Data Source in the appropriate box and choose a data source from the dialog.
You can change a data source selection by clicking Change Data Source from the appropriate box, or by dragging a different data source to the box from Data Source Explorer.
- Select the databases, or tables and views (depending on the type of database), from the Databases pulldown menu.
- After you name the job and specify the data sources, click Options.
Specify Execution, Mapping, Comparison, and Resolution Options
You can set various options that control how a job is processed on the Options tab.
To set data comparison job options
- Scroll down to see all the options and their descriptions, and select or deselect them as needed.
- If an option is not available for the data source, the check box is greyed out. The options are described in the DB Change Manager Preferences:
- When you are satisfied with the options, click Mapping.
To save options as defaults for future jobs
- Set the options.
- Click Make these the defaults for new jobs.
Understand the Data Source Mapping Process
Mapping is the process of pairing data source elements between the source and target of a job. These elements (databases, tables, and rows) are compared when the job runs to determine matches and mismatches between the two sources.
Mapping details are shown on the Mapping tab of the editor.
Customize Database Mapping
When you first click into the Mapping tab, the Database Mapping table lists the two data sources and lists all of their databases. If you previously selected a database in the Overview tab, it is selected. You can scroll right to see the Last Inspected column, which indicates that the pair has not yet been analyzed, or inspected, by DB Change Manager. The right side of the pane provides similar information.
To select databases to compare
Use the check boxes to select the databases you want to compare. The Select All or None option is helpful if there are many databases in the list.
To change the default target database
- In the database row, select the target.
- From the dropdown menu, select a new database.
Inspect the Databases
Before you can see the default, initial table mappings, you run an inspection process.
To run the database comparison inspection
On the right side of the Database Mapping pane, click one of the following Inspect options:
- All inspects all pairs in the table. This is not recommended if you have a large number of databases.
- Current inspects the current object mappings.
Checked inspects only those pairs selected in the table.
Alternatively, you can select a row and in the Table Mapping pane click Yes, retrieve the objects in this pair.
Once inspected, icons in the table that indicate how much of the selected objects matched. Hover over the icon to see more information.
To restore the Database Mapping table to its original settings
In the Database Mappings pane, click Remap to Defaults.
All settings revert to the original object mappings saved with the job, based on the options you selected in the Mapping tab.
Customize Table Mapping
The Table Mapping pane lists the paired tables. The icons indicate whether mappings were complete, and if not, error icons indicate what problems were encountered, if any.
To change the mapping to a new target column
- In the table row, select the target.
- From the dropdown menu, select a new target column.
To view or update the column mappings of a pair of tables
- Select a row by highlighting it with the cursor. (The check box is irrelevant for this task.)
- On the right side of the pane, click View Column Mapping.
- In the Columns and Comparison Key pane, you can:
- Select or deselect columns to use in the comparison job. Because this could affect referential integrity, review Resolution Options.
- Change or add a comparison key if multiple keys are available. In the Comparison Filter pane, you can:
- Write a WHERE clause to use during the comparison.
Create a different WHERE clause per table in the pair. Select Separate Where Clause and an additional text box is added for a second WHERE clause.
When entering selective WHERE clauses, do not include the WHERE keyword; for example, use “AGE>12” instead of “WHERE AGE>12”.
Reconfigure the Data Source Mapping
After configuring and running a comparison job, you may discover that you need to set different mapping options. You can reconfigure the way DB Change Manager maps data source elements and then run the job again.
To reconfigure the automatic mapping process
In the Options tab, select or deselect Mapping Options. The options are also described in Mapping Options.
If you change the mapping options in Preferences, they will become the defaults for all new jobs.
- In the Mapping tab, click Refresh Databases and Owners to update the automatic comparison.
Set Job Notification Options
When a job completes, DB Change Manager provides various ways of notifying you. See Setting Job Notifications for details.
Run the Data Comparison Job
After you have specified the options and mappings for a data comparison job, you can run it.
To run a job
- Click File > Save to save the job before you run it.
- In the upper right of the Data Comparison Job editor, click the Run icon.
While the process runs, the Results tab appears and displays the Comparison Summary pane. This information includes:
- Source and target data source names
- When the job started
- Estimated remaining time for the job to run
- Elapsed time of the job
- Names of objects currently being compared
- A progress bar showing percentage completion
Review Comparison Results
After a job run completes, the Results tab shows the Comparison Results table.
The Comparison Results table lists:
- The source and target databases
- Progress status; either Done, Failed, or, if no pairs were specified, Nothing to Compare
- The Compare Index percentage of matches
- The Results Column View icon links to details about the comparison job
After running a data comparison job, you may decide to synchronize one to the other. For more information, see Synchronizing Data Repositories.