CollisionDetect

Action CollisionDetect causes Fivetran Local Data Processing 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 Local Data Processing maintains extra timestamp information for each tuple, unless parameter 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 .

• 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 section for in .
• Action CollisionDetect cannot be combined with the method.

Parameters

This section describes the parameters available for action CollisionDetect.

Following are the two tabs/ways, which you can use for defining action parameters in this dialog:

• Regular: Allows you to define the required parameters by using the UI elements like checkbox and text field.
• Text: Allows you to define the required parameters by specifying them in the text field. You can also copy-paste the action definitions from Local Data Processing documentation, emails, or demo notes.

Parameter	Argument	Description
TreatCollisionAsError		Treat a collision as an error instead of performing automatic resolution using the 'first wins' rule. If parameter in action is defined then the collision will be written to the fail table and the integration of other changes will continue. If parameter is not defined then the integrate job will keep failing until the collision is cleared, either by deleting the row from the history table (using command ) 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 is more than one database where changes can occur then if a change occurs in the same second, then the 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 manually using command .
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 (option -mui) to reconcile the difference between two databases without removing newer rows from either. This parameter must be used with parameter (e.g. Context=refresh).
Context	context	Action only applies if refresh or compare matches.