Action CollisionDetect causes HVR's integration to detect 'collisions', i.e. when a target row has been changed after the change that is being applied was captured. Collisions can happen during bi–directional replication; if the same row is changed to different values in databases A and B so quickly that there is no time to replicate the changes to the other database then there is danger that the change to A will be applied to B while the change to B is on its way to A. Collisions can also happen without bi–directional replication; if changes are made first to A and then to B, then the change to B could reach database C before the change from A reaches C. Undetected collisions can lead to inconsistencies; the replicated database will become more different as time passes.
The default behavior for CollisionDetect is automatic resolution using a simple rule; the most recent change is kept and the older changes discarded. The timestamps used have a granularity of one second; if the changes occur in the same second then one arbitrary location (the one whose name sorts first) will 'win'. Parameters are available to change this automatic resolution rule and to tune performance.
Collision detection requires that HVR maintains extra timestamp information for each tuple, unless parameter /TimestampColumn is defined. This information is held in a special history table (named tbl__h). This table is created and maintained by both capture and integration for each replicated table. The old rows in this history table are periodically purged using timestamp information from the integrate receive timestamp table (see also section Integrate Receive Timestamp Table).
|/TreatCollisionAsError||Treat a collision as an error instead of performing automatic resolution using the 'first wins' rule. If Integrate /OnErrorSaveFailed is defined then the collision will be written to the fail table and the integration of other changes will continue. If /OnErrorSaveFailed not defined then the integrate job will keep failing until the collision is cleared, either by deleting the row from the history table or by deleting the transaction file in the HVR_CONFIG/router directory.|
|/TimestampColumn||col_name||Exploit a timestamp column named col_name in the replicated table for collision detection. By relying on the contents of this column collision detection can avoid the overhead of updating the history table. Deletes must still be recorded. One disadvantage of this parameter is that collision handling relies on this column being filled accurately by the application. Another disadvantage is that if there are more than one database where changes can occur then if a change occurs in the same second, then collision cannot be detected properly.|
|/AutoHistoryPurge||Delete rows from history table once the receive stamp table indicates that they are no longer necessary for collision detection. These rows can also be deleted using command hvrhistorypurge.|
|/DetectDuringRefresh||colname||During row–wise refresh, discard updates if the timestamp value in colname is newer in the target then the source. This parameter can be used with hvrrefresh –mui to reconcile the difference between two databases without removing newer rows from either. If this parameter is defined with parameter /Context then it can be used for refresh only without enabling collision detection during replication.|
|/Context||Action only applies if refresh or compare context matches.|
Manually Purging History Tables
Command hvrhistorypurge will purge old data from collision handling history tables. This is done automatically after integration if CollisionDetect /AutoHistoryPurge is defined. The command can be called as follows:
$ hvrhistorypurge [–a] [–ffreq] [–hclass] [–ttbl]… [–uuser] hubdb chn loc
The first argument hubdb specifies the connection to the hub database. This can be an Oracle, Ingres, SQL Server, DB2, DB2 for I, PostgreSQL, or Teradata database depending on its form. See further section Calling HVR on the Command Line. The following options are allowed:
|–a||Purge all history, not just changes older than receive stamps.|
|–ffreq||Commit frequency. Default is commit every 100 deletes.|
|–ttbl||Only parse output for a specific table. Alternatively, a specific table can be omitted using form –t!tbl. This option can be specified multiple times.|