CollisionDetect
Description
Action CollisionDetect allows HVR to detect collisions during data replication. Collisions can happen during bi-directional replication. For example, 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 a risk that the change to A will be applied to B while the change to B is on its way to A. Collisions can occur in cases other than bi-directional replication. For example, if changes are made first to A and then to B, then the change to B can reach database C before the change from A reaches C. Undetected collisions can lead to inconsistencies between the replicated databases.
The default behavior for CollisionDetect is automatic resolution using a simple rule: the most recent change is kept and the older changes are discarded. The timestamps used have a one-second granularity; if changes occur in the same second, then one arbitrary location (the one whose name is sorted first) will 'win'. Parameters are available to change this automatic resolution rule and to tune performance.
Collision detection requires that replicated tables have a reliable last-updated timestamp column indicating when the data was last updated. Such a column must be manually added to each table involved in the replication and defined in parameter /TimestampColumn.
For Oracle and Ingres databases, if parameter /TimestampColumn is not defined, HVR maintains extra timestamp information for each tuple in a special history table (named tbl__h). This table is created and maintained in both capture and integrate locations for each replicated table. The old rows in this history table must be periodically purged using timestamp information from the 'integrate receive timestamp table)'. In this case, parameter /AutoHistoryPurge must be defined.
Action CollisionDetect is supported only for certain location classes depending on the parameter defined with the action. For the list of supported location classes, see the corresponding sections for CollisionDetect in Capabilities.
Action CollisionDetect cannot be combined with /Burst integrate parameter.
Defining the CollisionDetect action on a table that is configured with TableProperties /DuplicateRows will prevent duplicate inserts. Instead, it will convert them into updates.
Parameters
This section describes the parameters available for action CollisionDetect.
| Parameter | Argument | Description |
|---|---|---|
| /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. One disadvantage of this parameter is that collision handling relies on this column being filled accurately by the application. Another disadvantage is that if changes can occur in more than one database simultaneously, and the changes occur in the same second in both, proper collision detection may not be possible.
|
| /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. This parameter must be used with parameter /Context (e.g. /Context=refr). CollisionDetect with parameter /DetectDuringRefresh can be used for any supported DBMS. |
| /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 for LUW, 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:
| Parameter | Description |
|---|---|
| –a | Purge all history, not just changes older than receive stamps. |
| –ffreq | Commit frequency. By default a commit is done after 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. |