The concept of synchronization applies mainly to table-to-table replication, where Post performs integrity checks to make certain that only one row in the target matches the row change that is being replicated. It does not apply to file, JMS, Kafka, and change-history targets, which contain a record of every operation replicated by Post, some of which may be identical over time. The Post process does not perform integrity checks on those targets.
The basic characteristics of synchronized source and target tables are as follows (unless the transformation feature is used).
Ensuring data integrity is the responsibility of the Post process. Post applies a WHERE clause to compare the key values and the before values of the SQL operations that it processes. Post uses the following logic to validate synchronization between source and target tables:
Post applies a replicated INSERT but a row with the same key already exists in the target. Post applies the following logic:
Post applies a replicated UPDATE but either cannot find a row in the target with the same key value as the one in the UPDATE or Post finds the correct row but the row values do not match the before values in the UPDATE. Post applies the following logic:
Note: You can configure Post so that it returns an out-of-sync message if the current values in the target row match the after values of the UPDATE. See the SP_OPO_SUPPRESSED_OOS parameter in the SharePlex Reference Guide.
Post only verifies the integrity of the rows that are being changed by its current SQL operation. It does not verify whether other rows in that table, or other tables, are out of synchronization in the target database. Hidden out-of-sync conditions may not show up until much later, when a change is eventually made by SharePlex to that data or a discrepancy is detected in the course of using that data.
Example of a detectable out-of-sync condition
Someone logs into the target and updates the COLOR column in the target table from “blue” to “red” in Row1. Then, an application user on the source system makes the same change to the source table, and SharePlex replicates it to the target. In the WHERE clause used by Post, the pre-image for the target table is “blue,” but the current value in the target row is “red.” Post generates an out-of-sync error alerting you to the out-of-sync condition.
Example of a hidden out-of-sync condition
Someone logs into the target and updates the COLOR column in the target table from “blue” to “red” in Row2, but the change is not made to the source table and is not replicated. The two tables are now out-of-sync, but there is no error message as in the previous example, because there is no Post involvement. No matter how many subsequent updates are made to other columns in the row (SIZE, WEIGHT), the hidden out-of-sync condition for the COLOR column persists (and users on the target have inaccurate information) until someone updates the COLOR column in the source table. When that change is replicated, Post compares the pre-images, and only then is there an error message.
The majority of time, the cause of out-of-sync data is not anything done wrong by replication, but rather DML applied on the target, an incomplete backup restore, or some other hidden out-of-sync condition that can go undetected a long time until an operation applied by Post finally affects that data and an error is returned. Solving out-of-sync conditions can be time-consuming and disruptive to user activity. Once replication is started, it is recommended that you: