Data Difference Analyzer

Data Difference Analyzer allows you to compare data located in tables that have identical structure. It can also generate a data synchronization script that can make data in source or target data to be identical. Compared tables can be located in the same or different databases or on the same or different Informix servers.
In order to compare data in two tables, these tables must have at least one unique index.

Data Comparison Method

Data Difference Analyzer uses two methods for data comparison:

Server-Based Comparison
Client -Based Comparison

Data Difference Analyzer does dynamic check which comparison method suites best to compare each selected table pair and sets it by default. You can override the comparison method for each table pair if you think that the selected default is not the best solution or does not work for your situation.

Server-Based Comparison

This method provides fastest way to compare very large amounts of data (hundreds of MB in each table). The comparison is performed by a stored procedure that Data Diff Analyzer dynamically creates on Informix server on which compared tables are located.

This method has the following disadvantages:

If compared tables are located on two different servers, at least one server should have remote access defined to anoher server, so that it cab be accessed from SQL SELECT statement. See section below for detail information on how to configure remote server access.
Informix server should allow Server Studio user to create and execute stored procedures in a database in which selected tables is located. If compared tables are located on two different servers, at least one server should allow creating the stored procedure and this should be the same server from which remote access to another server is defined.
If one table is located with database that has ‘logging’ mode and another table is located in a database that has “no logging” mode, they can not be compared using Server-Based method.

Data Difference Analyzer automatically sets Server-Based Comparison method if compared tales have large amount of data and nether of above limitations apply. Otherwise it uses Client-Based Comparison.

Client-Based Comparison

Client-Based Comparison method allows comparing tables that have reasonably small amount of data (~50 MB). Data from both tables are retrieved to the Server Studio JE workstation and then compared locally. This method has less limitation in a sense that it does not require dynamic creation of stored procedures on IDS servers on which compared tables are located. It also does not require having remote server access defined between servers if compared tables are located on two different servers.
But it consumes large amount of memory (RAM) on workstation where Server Studio JE is installed and it is slower than Server-Based Comparison when large amount of data has to be compared.

Data Difference Analyzer uses this method as a default for small tables.

Selecting Tables to Compare

Choose Tools -> Data Manager -> Compare & Synch Data from top-level menu. The Table Selection dialog box will open.

You can either select table pairs one by one, specifying explicitly source and target table names, or you can select a group of tables in a database and specify to compare them with tables matching by name located in another database or on another server.

To add table pairs one-by-one, press Add Single Table button.

The dialog box opens that will allow you to specify source and target table locations and names.

To add group of tables, press Add Group of Tables button.

For source tables, you have to choose source database and select list of tables from the grid.

For target tables, you have just choose the server and database in which target tables are located. Target tables should match by name. If some of corresponding target tables do not exist, they will be automatically ignored and not added to the comparison list. You will get a warning message about this event.

If there are any logical errors when attempting to compare tables that have not identical structure or that do not have unique index, you will get error message after you press Add button.

There is also an ability to specify alternative pattern match rule rather than identical match for target table names when adding a group of tables.

For example,

If target tables are located in the same database as source tables and they have similar names with addition of “_bak” suffix (source table “tbl_order” and target table “tbl_order_bak”), you can press Advanced button and specify that target table name suffix is “_bak” If you have twenty or thirty such tables to compare that satisfy this pattern matching rule, it might save you a significant time on data entry.

Once the list of tables to compare is created, you can save this list in order to re-use it in the future. For example, if you have list of 20 reference tables that you always compare between QA and production servers after application upgrades, you can create this list ones and save it. Then re-use this list after each application upgrade. You can maintain multiple saved table compare lists.

To save the list, press Save to File button.

To open list of tables from a previously saved file, press Open from File button.

Starting Data Comparison

To start Data Comparison operation, press Compare Data button in Table Selection dialog box.

The Data Difference Analyzer panel will open.

Top area of this panel displays the grid that shows compared table pairs and progress and status of comparison jobs for each pair.

Once the comparison job is complete, it displays the total number of found differences in the grid. Select the job line in the grid, to see details for this table pair in middle area of the panel. It shows all records that were inserted, deleted or modified. Select the record in order to see its details in a bottom area of the panel.

For example, for modified records, the Record Details View shows source and target modified columns’ values side-by-side so that it is easy to see the differences. If compared tables have 50 columns and only 2 columns in a record were modified, Record Details View shows only these 3 modified columns by default. Too see all columns in a record, uncheck Show Different Columns Only checkbox located above Record Details View.

To re-run all comparison jobs listed in the top grid, choose from top-level menu Data Diff -> Compare All Tables.

To re-run selected jobs, select required jobs in the top grid and choose from top-level menu Data Diff -> Compare Selected Tables.

You might need to re-run jobs after you manually fixed all found differences using Data Editor in Server Studio or any other tool and want to make sure that all differences are actually fixed.

You can use Data Difference Analyzer data synchronization tool, to automatically fix all found differences.

Data Synchronization

You can synchronize data in two tables directly or you can generate data synchronization SQL script. Generated script can be reviewed and applied at later time by running it in SQL Editor.

To synchronize data directly, without generating SQL script, press Synchronize button.

To generate data synchronization SQL script, press Generate Script button.

The Data Synchronization dialog box will open.

Select tables that you want to synchronize.

*** Important ***
Please notice that if tables have referential integrity constraints (foreign keys), you have to synchronize data in master tables first and only after that synchronize details tables.

By default, Data Synchronization tool updates data in target tables to make them the same as source tables. If you want to modify this direction, use radio button on top of the Data Synchronization dialog box.

Press Proceed button to complete the operation.

For Generate Script option, the SQL Editor containing generated data synchronization script will open. You can either execute script in SQL Editor or save generated script to a file.

For Synchronize option, the changes will be applied immediately to a database. You will see error message if the data synchronization operation fails.