If your use case involves replication of identical source/target tables then you will have generated an Apply Engine Replication script. If you have confirmed that primary indexes have been created on the target tables then the apply engine can use the target database catalog to identify the primary keys. Alternatively you may have decided to specify the target keys in the replication script which would be required by those source tables that had no primary keys. This is often true with "audit" type tables.
In either case you are likely to see "compensation" occur on one or more of the target tables. Compensation is an optional feature of the Apply Engine which automatically detects out of sync conditions between source and target. This condition frequently occurs during the initial implementation of replication either because there was no initial load of the target from the source or because the initial load was performed while the source was still subject to concurrent application database activity. In both cases there will be a period of "catch up" replication where a CDC Update or Delete for a missing row or a CDC insert for an existing row was captured, while the initial load or refresh was in progress.
Compensation in these cases will drop the deletes if the record does not exist in the target, convert an update to an insert and convert an insert to an update. Without compensation, all of these situations would normally cause a SQL error when an CDC Update or Delete for a missing row is processed or CDC inserts for an existing row are attempted. Compensation processing is optional but nearly always utilized in Apply Engines performing Replication. Compensation not only eliminates the impact of these timing related errors during the initial implementation but it ensures that once the initial "catch up" phase of replication is complete that the source and target are fully synchronized. During this time, out of sequence inserts, updates and deletes should reduce in number and eventually reach zero. Once the initial replication has "caught up" with processing occurring on the source side, compensation should no longer occur.
To clarify, when a COMPENSATION is reported in either a WTO, engine message or the engine report the term means the following:
test how to do
how to continued
test parm
test parm continued•Compensated Update - A CDC Update record was transformed by the Engine into an Insert.
•Compensated Delete - A CDC Delete record was dropped because there was not target with a matching "key".
- Write to Operator (WTO) warning messages may be optionally generated by the Apply Engine to identify their occurrence and we recommend using existing console monitoring tools to alert staff as they typically indicate an unexpected but non-catastrophic event after the initial "catch up" implementation phase of replication.
- Performing compensation requires Inserts, updates and deletes to be preceded by a select using the Key of the CDC record. This additional operation adds some overhead but not enough to be concerned about in most cases. Options provide for the elimination of the pre-insert select on tables known to have large volumes of insert activities, like an audit table.
- Compensations that continue past the initial synchronization period indicates a problem. Either there were errors introduced by the initial load process or the keys of the target do not match those defined for the source or they do not provide for identification of a unique row.
The remainder of this section addresses the diagnosis and remediation of errors related to incorrect target keys. It is important to acknowledge that full resolution of the problem will often require repeating the initial load and "catch up" processing but only for those targets affected.
1.Identify the engine(s) that continue to produce compensation messages, the tables being compensated and collect statistics to identify the relative number of compensations occurring for each target table in order to prioritize the order in which to correct them.
2.Gather the DDL for the source tables from the DBA including their primary key and index specifications.
3.Using the prioritized list of target tables, examine each table one-by-one comparing the primary keys defined in the source database with the keys defined in the target database and the columns listed in the script, if the KEY IS clause has been specified, and make the necessary corrections.
4.Reparse the engine script and resume processing.
5.Monitor the output of the engine to confirm compensation has ceased for the target table and if it has not, reconfirm the source and target key specifications for the table and make further corrections.
6.Determine if the nature of the table requires re-load and resynchronization. There may be tables that are periodically emptied or purged in the course of source application processing. Depending on the purpose of the target table and the method used to empty it of rows, it may be possible to simply wait for the source and target, to be emptied through replication and natural resynchronization to occur. Alternatively it may be possible to simply drop and re-create the target table and allow replication to slowly bring the table current.
7.If the table contains data that will have to be reloaded to achieve synchronization. Determine the best time and method to perform the individual table reload as previously outlined in the introductory Quick Start Approach.
If possible use the Capture Based Refresh to reload the target table since it requires the least intervention.
If another method of refresh will be used it will be necessary to disconnect the Apply engine that normally processes change data for the target table. This should be done immediately before the unload/reload process is initiated to ensure that concurrent changes made by applications to the table are captured but not processed until after the load refresh has been completed. Once the load has been completed the Apply engine can be restarted.
With either method of Refresh, there will again be some expected compensation while the resynchronization occurs.
Repeat these steps with the next table in the same or another engine and continue the process for each table that was identified with compensation until all are eliminated.