As of 5.6.0, Online Compare is recommended. See: Online Compare. For earlier versions:
HVR Compare compares the data in the tables on source and target location. It uses the channel definition as the basis for comparison, including any transformations that may be defined in the channel and taking into consideration data type mappings between source and destination.
HVR Compare provides two modes:
Bulk: the bulk mode computes the checksum of all data in a table. HVR will report a difference between the source and destination tables if the checksum is different, irrespective of whether the also-reported rowcount is identical. The bulk mode runs on the database server and only passes the checksum to the hub so it is very efficient.
Row-by-row: in row-by-row mode all data is sorted and passed to the hub for detailed comparison. Row-by-row comparison in verbose mode provides a detailed report of all differences including a set of SQL statements to be run on the destination of the compare operation to bring its system back in sync with the source system.
Compare jobs can be run interactively or scheduled in the HVR job scheduler. When scheduled, separate log files are created for the job and all output is also sent to the hvr.out log file. The compare job does not take in-flight transactions into consideration so expect differences on a live system. Therefore compare jobs are ideally run during down-time or when the system is relatively idle.
Instructions
In order to identify tables that have data consistency issues the following procedure may be used:
Run HVR Compare in bulk mode for all tables
For tables that report differences in step 1
Run HVR Compare in row-by-row mode. Keep the number of reported differences in a spreadsheet.
Repeat this step multiple times for the tables that have differences.
When the row-count of the differences goes up and down with a low minimum (e.g. 100, 10, 0, 1, 0 on subsequent runs) this indicates that the differences are due to in flight transactions. If the number fluctuates but has a high minimum e.g. (10100, 10010, 10000 on subsequent runs) this indicates this table has missed data.
For tables that have been identified with data loss there are 2 options:
Run HVR Compare in row-by-row verbose mode. This will give the SQL statements that should be used to correct the data differences on the target.
Run HVR Refresh in Online Mode for these tables.