ODI 11g - Incremental Update load using Surrogate Key [Solved]

In one of our projects we had a scenario where we had to load data from an Oracle source to an Oracle target. We faced a lot of issues during the process. Then we came across Himu's blog post on the same, which saved us a lot of hours and frustration.

Below are some key pointers to note when using Incremental Update.

• Do not make the Surrogate Key as Primary key in your Target Table.

• Make the Natural Key as the Primary Key. 

• Check Insert only for Surrogate Key and Natural Key

• Disable 'Check not null' key on Mapping editor for Surrogate Key.  

ODI Mapping Quick edit screenshot - Refer for first four points

In the above diagram ROW_WID is the surrogate key and ROW_ID the natural key.

• Make the Natural Key as the Update Key in flow tab.

This worked for us. Let us know if this helps. 

Do check out the Mhimu's blog post at https://mhimu.wordpress.com/2009/05/04/odi-incremental-update-and-surrogate-key-using-database-sequence/