An Apply Mode of Source correct on captured Synchronization request data could fail on update. The problem involves a Synchronization request with the following characteristics:
-
During the Copy phase of a Synchronization request, a user executes an update statement followed by a delete statement. Connect CDC never copies the row in the table to the target table(s) during the Copy phase of the request.
-
The source table is not journaled.
-
The source table is unprotected. (Protected tables cannot participate in Synchronization requests.)
-
There are no gate conditions on the source table.
-
In the request, the Apply mode for update statements captured during the Copy phase is 'Source correct'.
-
The Apply Mode for insert, update, and/or delete statements captured after the Copy phase complete are all either None or Target correct. None are Source correct.
-
A column at the target has the 'not null' constraint or is a foreign key. The column is not defined to the table's distribution key; its source column is not referenced by an expression or a join statement.
-
If the target column's mapped source column is a primary key and the source table has a mapped long varchar, long varbinary, clob or blob column, then the value will be captured.
The problem occurs in the change phase of the Synchronization request when applying rows captured during the Copy phase. The update row sent to the target will only have values for those columns that changed or are always captured (as described above).
The Connect CDC apply component will attempt to apply the update but will discover there is no row in the target table for the distribution key. Because the Apply Mode for update statements captured during the Copy phase is Source correct, the update statement will be converted to an insert. Those column values not sent to the target will be set to NULL in the insert statement. The insert will fail because the target column(s) have constraints. If there were no constraints, the update succeeds, and the row is deleted when the delete statement is processed later.
There are several choices for a workaround. These choices involve forcing the kernel to send values for those columns that have a constraint even if a source column value did not change in an update statement. Choose the one most appropriate for you.
-
Change the Apply Mode for update statements captured during the Copy phase to Target correct so the applier will not ignore the failed update statement.
-
Change the Apply Mode for update statements captured after the Copy phase completes to Source correct.
-
Add a gate condition to any one column in the table that always evaluates to a true expression.
-
Define the source table as a journaled table.
-
Add an expression to the target columns that have a not-null or foreign key constraint