ODI-IKM Incremental Update (Updateless) v1

I have been thinking of creating a brand new Knowledge Module which will make an Incremental Update without using any UPDATE/DELETE SQL statements, only with inserts to increase performance. Initially, I was thinking that there should be a second integration table, but when I started scrawling to figure out flow diagram and how Knowledge Module works, I realized that there is no need for a second integration table.
If you are using Oracle database which your target table existing, this Knowledge Module can fit your needs. Let me try to explain how it works;

1. Drops and Creates I$ flow table (as usual)
2. Inserts the missing records (new records on source) to I$ flow table (if INSERT_NEW_ROWS option is set to true).
3. Inserts the matching records (existing records on source) to I$ flow table (if UPDATE_EXISTING_ROWS option is set to true).
4. Inserts the non-matching records (deleted records on source) to I$ flow table (if NOT_DELETE_NON_EXISTING_ROWS option is set to true).
5. Wrap them up and truncate & insert target table from flow table and drop flow table (if DELETE_TEMPORARY_OBJECTS option is set to true).
6. Analyze target (if ANALYZE_TARGET option is set to true).

To find the matching records, we need a set of key columns (depending on tables structure), which is set as “Update Key” on Target Datastore of our interface and can be defined in model. There could be some alternative keys that sometimes you don’t need to use primary key (that is already created in database). Especially when you are making a Surrogate Key Transformation. So, you can create your own key (this key should not be stored in database, only in ODI usage) by right clicking “Constraints” on table (in ODI Model) and add an “Alternate Key”, then add desired columns to it. This key, you created, can be selected as “Update Key” in this Knowledge Module by changing “Update Key” in Target Properties in Target Datastore section (it is a combo box).

Click the links to download the IKM Oracle Incremental Update (Updateless) v1 Knowledge Module from my Google drive or from here IKM Oracle Incremental Update Updateless(v1) (don’t forget to change the surname of the file to xml after downloading).

Remember that, use these files at your own risk, backup your current environment and this Knowledge Module is not supported by Oracle. 🙂

10 thoughts on “ODI-IKM Incremental Update (Updateless) v1

  1. Hi Gurcanorhan,

    I am new to ODI. I have a source table STAGE_GLCOA_SYN and the target table FIF_GL_CROSS_REF.

    Current Logic : identify the record is new /existing record in the target (FIF_GLCROSS_REF) if new then insert else update

    New logic — identify the record is new /existing record in the target (FIF_GLCROSS_REF) if new then insert else if record exists then delete the old record and insert the new set

    How to achieve this. Is there any option like this in KMs or do I need to add a procedure for this.

  2. Hi Gurcan,

    I was going to test your KM, but when I try to import it I am getting: “Please specify an Upgrade Key for converting Object ID’s to Unique Global ID’s.” How do I go about this?

    Hopefully, this will help me with this issue as workaround: https://community.oracle.com/message/14030429#14030429.

    What you developed sounds great. I wonder how well it will work with large tables. The the update might be more efficient? Say if you are updating just few records?


    • To be honest, Updateless Incremental Update KM will work better in larger tables, especially if you’re running on Exadata with Hybrid Columnar Compression. You may get over the ID problem since KM needs to have a ID to match records (which to update, which to insert, which to delete) and you need to specify these during usage.
      I am planning to update the post upon your comments and add a “how to use this KM” section at the bottom.
      Thank you for the kind words.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.