Common Causes of Integration Errors

Question

What are the common causes of integration errors?

Environment

HVR 5

Answer

Integration errors are generally a result of data inconsistencies between the source and target tables. This can be a result of a configuration issue, user error, or other failures.

Below, you can find some common causes and various ways to check what is going on:

1. The replication key does not match the key of the source and target table (i.e. does not pinpoint a unique row). This can be checked using HVR's Table Select feature connecting to source and target databases.

2. The target table is changed outside of HVR (the row is already deleted). This could be checked by reviewing user grants on the target.

3. Restrictions that are configured incorrectly, e.g. Restrict /CaptureCondition that can match the delete but not the insert. A Restrict /RefreshCondition that results in an incomplete refresh.

4. Wrong Online Refresh options. Online refresh needs to be enabled when the source database is being changed (DML) during the refresh. There are several settings:

   • "Skip Previous Capture and Integration" (option -qrw). This can be used if there is 1 source and 1 target in the channel and all data for the selected tables is refreshed.
   • "Skip Previous Integration" (option -qwo). This should be used when refreshing all data for selected tables from 1 source into multiple targets.
   • "Do not skip changes" (-qno). This option should be used when only part of the data for selected tables is refreshed; e.g. using a Restrict /RefreshCondition.

5. For some SBMA Online Refresh relies on timestamps to decide which changes to skip or to apply with Integrate resilience. When system clocks for source/target and hub machines are not properly synchronized this can result in data inconsistencies and in integration errors during replication.

6. HVR Load has been run with options -or (Transaction files and Capture time) maybe in combination with incorrect capture rewind options, causing a gap in captured transaction.

7. Not all data is captured. In this case, the insert is not replicated but the delete is. For example, the inserts are done using an operation that is not supported by HVR (e.g. partition exchange load) or an operation that is not logged (some direct path operation if the table or partition does not have force logging). This can be investigated by turning on Integrate /JournalRouterFiles in the channel. This will change the integration to not delete all tx-files after processing them, but move them to a separate directory. This gives an audit trail of all rows that are replicated with HVR.

8. Another option is that the changes that are not captured are made by an HVR Integrate job (in a cascading replication scenario). These changes are by default ignored by HVR's capture. This behavior can be modified by the Capture /IgnoreSessionName and Integrate /SessionName parameters.

9. A transaction had started before the initial capture time but its results were not picked up by the Refresh because the transaction had not yet been committed when the affected table(s) was (were) refreshed. If such a transaction, for example, inserts a row into a table that is subsequently updated or deleted in a new transaction then HVR would report the row as non-existent for the update/delete.