January 9 2012

In my previous blog, I showed how to do an ELT from Oracle DB to XML in ODI.  Every ELT uses a Loading Knowledge Module (LKM), which describes how the data is loaded from one system to another, and an Integration Knowledge Module (IKM), which describes how the data is integrated in the target system. These Knowledge Modules are fully extensible and can be edited from within ODI Studio.  In most cases, these Knowledge Modules do not need to be edited, but there are situations where you may have to, or want to, customize a Knowledge Module to achieve a specific scenario.  A good example would be to remove certain commands or a piece of code from a KM to gain higher performance.  Another example would be to combine commands or pieces of code from multiple KMs into one KM to achieve a particular functionality.  In my example below, we will create a custom IKM using a combination to two IKMs to accomplish a specific scenario.

Here is my specific scenario:

  1. Create an Interface with an Oracle DB as a target that can handle Inserts and Updates.
  2. As part of that Interface I also have to use an ODI Specific Sequence.
  3. For the ODI Specific Sequence to work, I have to use ‘IKM SQL to SQL Control Append’, for good reason, per Oracle Knowledge Base Article ID 423872.1 (login required).
  4. The IKM mentioned above, ‘IKM SQL to SQL Control Append’ can only handle Inserts.
  5. But, another IKM, ‘IKM SQL to SQL Incremental Update’ can handle Inserts and Updates, but not ODI Specific Sequences.

Given the above scenario, we need an IKM that can handle ODI Specific Sequences in an Interface and allow Inserts and Updates to happen in the same Interface.  This is where our custom IKM comes into play.  We will combine commands from the 2 the IKMs, ‘IKM SQL to SQL Control Append’ and ‘IKM SQL to SQL Incremental Update’ to create our own custom IKM.

Here are the steps to create this custom IKM:

  1. In the ‘Designer’ tab, under ‘Knowledge Modules’, right click ‘IKM SQL to SQL Incremental Update’.
  2. Click on ‘Duplicate Selection’ to create a duplicate of this IKM.
  3. Rename our new duplicate IKM, to say, ‘IKM SQL to SQL Incremental Update Custom’ and hit Save. This duplicate IKM will be our new custom IKM.
  4. Click on the Details tab of our new custom IKM.
  5. Double click ‘Insert New Rows’ command to edit it.
  6. Rename this command to ‘Insert New Rows Custom’ and hit Save.
  7. Remove the existing code under ‘Command on Target’ tab and hit Save.
  8. In the ‘Designer’ tab, under ‘Knowledge Modules’, open ‘IKM SQL to SQL Control Append’.
  9. Double click ‘Insert New Rows’ command and copy the code from ‘Command on Target’ tab. We want to use this code in our new custom IKM because we want our ODI Specific Sequence to work.
  10. Paste this code under our ‘Insert New Rows Custom’ ‘Command on Target’ tab, from step 7.
  11. I simplified this code a bit.
    insert into <%=snpRef.getTable("L","TARG_NAME","A")%>
        <%=snpRef.getColList("", "[COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%>
        <%=snpRef.getColList(",", "[COL_NAME]", ",\n\t", "", "((INS and TRG) and REW)")%>
    ) values
        <%=snpRef.getColList("", ":[COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%>
        <%=snpRef.getColList(",\n\t", "[EXPRESSION]", ",\n\t", "", "((INS and TRG) and REW)")%>
  12. Hit Save.
  13. Click on ‘Command on Source’ tab.
  14. Remove the existing code under ‘Command on Source’ tab.
  15. Enter the following code in there.
    select     *
    from    <%=snpRef.getTable("L","INT_NAME","A")%>
    where    IND_UPDATE = 'I'
  16. Hit Save and you are done!
  17. Use our custom IKM, ‘IKM SQL to SQL Incremental Update Custom’ in our Interface to achieve the scenario mentioned earlier.

I hope the steps mentioned above to create my custom IKM can be used as a sample to create other custom KMs.  Go ahead and open up those KMs.  They are there to be tinkered with.

Feel free to drop me a line if you have any questions.

Join the Conversation

December 15, 2014


I have a question.I am new to ODI. I have to create a LKM ,That will load only say for example 10000 records out of 30000 records ...Kindly help me .

March 27, 2015


DO you have a KM that checks for data type mismatch errors and logs the same in an erroe table as well?

March 24, 2017



Could you please suggest. how we can create a KM, which will delete rows from Target tables on certain conditions.




April 24, 2017


we also delete target table in using procedures

Enter your first name. It will only be used to display with your comment.
Enter your email. This will be used to validate you as a real user but will NOT be displayed with the comment.