ODI refresh failed after modifying primary key solution

The ODI incremental refresh, after modifying the primary key value, causes the refresh to fail. Workaround: Use columns with fixed values in the table as the basis column for incremental refreshes.

The demo is as follows:

As above, the source and target tables have the same records. The source table primary key information is now modified

After performing a data synchronization refresh, the target table data is as follows

found that when the source table is in the update primary key, after the synchronization is performed, a new record is inserted into the target table, and the original key value record remains in the library.

Solution: Find a column (a set of) fixed fields from the source table, and establish the primary key in the target table as a fixed field in the source table as the basis for the update.

As follows:

The source table ORDTASK2, the primary key is ordno,methodcode; when we do data integration, we think Origrec is unique, and we add a unique index with NOT NULL as the constraint. The value never changes (and the primary key corresponding to the target table corresponds to ORDTASK2.ORIGREC).

Target table ORDTASK3, primary key is Origrec

Enable logging for the source table and add it to CDC, for example, with a small clock in front of ORDTASK2

At this point you need to modify the trigger above the ORDTASK2, and the log information related to several tables, views.

Modify the Log table first

Cancels the original defined Ordno, Methodcode, and modifies to Origrec. Next, modify the trigger

Write Origrec to the log table Jtask2, and then modify Jv$dordtask2, Jvtask2

After the above modification operation, the data synchronization operation is basically no problem, next create the mapping ORDTASK2

Test data synchronization

Add a piece of data to ORDTSK2

, this data change has been captured in the ORDTASK2 log table. Perform data synchronization

found that the ORDTASK3 target table has been synchronized.

Modify non-primary key testno to "TN00100"

Perform synchronization

Data is synchronized

Update primary key value Ordno modified to "A0010"

Perform synchronization

ORDTASK3 data has been refreshed

Modify primary key + non-primary key

Perform synchronization

Delete Data test

Delete data in ORDTASK2

Perform synchronization

Sync succeeded!

ODI refresh failed after modifying primary key solution