Commit Frequency in ODI

Commit Frequency in ODI:

Hello Guys. I hope you all are doing well. Today I am going to demonstrate about commit intervals in ODI for a given number of records. I have just replicated the PL/SQL codes in to KM supported script. I believe there is still lot of room to enhance it. But that’s up to you. You can do your own optimisation. This is just a POC. Let me know if you have got something  better than  this, I will definitely update it with your idea and approach.

Right now I have designed only for IKM Sql Control Append. I will add the Incremental update KM once I am done with it.
Click here to download the KM. If you are facing problem in downloading then copy paste below codes  in to the insert new rows step of the KM and create a new option called COMMIT_FREQUENCY.

/*In case of copy paste you may face problem with the single and double quotes. So just replace it using your keyboard.*/

<%if ( odiRef.getUserExit(“FLOW_CONTROL”).equals(“1”) ) { %>
v_count number:=1;
for rec in (select <%=odiRef.getColList(“”, “[COL_NAME]”, “,\n\t”, “”, “((INS and !TRG) and REW)”)%>
<%=odiRef.getColList(“,”, “[EXPRESSION]”, “,\n\t”, “”, “((INS and TRG) and REW)”)%>
from <%=odiRef.getTable(“L”,”INT_NAME”,”A”)%> ) loop
insert into <%=odiRef.getTable(“L”,”TARG_NAME”,”A”)%>
<%=odiRef.getColList(“”, “[COL_NAME]”, “,\n\t”, “”, “((INS and !TRG) and REW)”)%>
<%=odiRef.getColList(“,”, “[COL_NAME]”, “,\n\t”, “”, “((INS and TRG) and REW)”)%>
) values( <%=odiRef.getColList(“”, “rec.[COL_NAME]”, “,\n\t”, “”, “((INS and !TRG) and REW)”)%>
<%=odiRef.getColList(“,”, “[EXPRESSION]”, “,\n\t”, “”, “((INS and TRG) and REW)”)%> ) ;
if mod( v_count,<%=odiRef.getOption(“COMMIT_FREQUENCY“)%>)=0 then
end if;
end loop;
<% } else { %>
v_count number:=1;
for rec in (
<%=odiRef.getColList(“”, “[COL_NAME]”, “,\n\t”, “”, “((INS and !TRG) and REW)”)%>
<%=odiRef.getColList(“,”, “[EXPRESSION]”, “,\n\t”, “”, “((INS and TRG) and REW)”)%>
<%for (int i=0; i < odiRef.getDataSetCount(); i++){%>
<%=odiRef.getDataSet(i, “Operator”)%>
select <%=odiRef.getPop(“DISTINCT_ROWS”)%>
<%=odiRef.getColList(i,””, “[EXPRESSION] [COL_NAME]”, “,\n\t”, “”, “((INS and !TRG) and REW)”)%>
from <%=odiRef.getFrom(i)%>
where <% if (odiRef.getDataSet(i, “HAS_JRN”).equals(“1”)) { %>
JRN_FLAG <> ‘D’ <%} else {%> (1=1) <% } %>
insert into <%=odiRef.getTable(“L”,”TARG_NAME”,”A”)%>
<%=odiRef.getColList(“”, “[COL_NAME]”, “,\n\t”, “”, “((INS and !TRG) and REW)”)%>
<%=odiRef.getColList(“,”, “[COL_NAME]”, “,\n\t”, “”, “((INS and TRG) and REW)”)%>
) values ( <%=odiRef.getColList(“”, “rec.[COL_NAME]”, “,\n\t”, “”, “((INS and !TRG) and REW)”)%>
<%=odiRef.getColList(“,”, “[EXPRESSION]”, “,\n\t”, “”, “((INS and TRG) and REW)”)%>
) ;
if mod( v_count,<%=odiRef.getOption(“COMMIT_FREQUENCY“)%>)=0 then
end if;
end loop;

After downloading, import it in to the project and provide the commit value to a number you want. I have tested it using 100K .
Now you are good to go.
Thats All for today 🙂 .

About Bhabani 86 Articles
Bhabani has 12 plus years of experience in Data warehousing and Analytics projects that has span across multiple domains like Travel, Banking and Financial, Betting and Gaming Industries. Solution areas he focuses on designing the data warehouse and integrating it with cloud platforms like AWS or GCP. He is also a Elite level contributor at OTN forum more than 9 years. He loves to do experiment and POC on different integration tools and services. Some of his favorite skills are Redshift, Big Query, Python, Apache Airflow, Kafka, HDFS, Map Reduce ,HIVE, Habse, Sqoop, Drill, Impala.


  1. Hi Bhabani,
    i am new to learn ODI,i know plsql procedures
    and idont know jython how i can learn


  2. Hi Bhabani,

    What is the use of this Commit Frequency.

    if i want to load let’s say 1 million records , and after 100,000 records it’s failed and table got commited.

    when i restart this load agin , then will it skip this 100,000 records and load rest of records.

    • Hi Samarendra,
      Unfortunately it wont skip the 1st 1 million record. For this you have to do some customization in the KM. For example put some exception handling that can handle such exception. Next time when you restart try to find out the key difference and load the new key records into target table. For example find out the max audit date column or activity date column from target table and then load the records from source table where source audit/activity date> the audit/activity date column of target table. Even if you got some duplicate records then it will be updated in the target.

  3. Hi Bhabani,

    Can we use Minus Operator to load that records,
    let’s A is my source and B is my Target then can i load Table B with A-B data, so those data already at my Target that won’t load.

  4. hi babani,
    when i try to use this code in my KM, i am facing this error..
    i just copied and pasted in KM’s Command on Target.

    Error: ODI-1228: Task INF_TRG_EMP_COMMIT (Integration) fails on the target ORACLE connection DS_ORA_SYSTEM.
    Caused By: java.sql.SQLException: Non supported SQL92 token at position: 116

    Thanks in advance

    • Hari, you might have problem with double quote and single quote. Just replace it using your keyboard. There are some special characters causing this issue. Let me know if it works or not otherwise i will mail you.

      • Hi Bhabani,
        How are you ?
        Sorry to say, the KM code is still not working in my INF. Please help me regarding this..
        I have typed every thing from from our Blog, i have no chance to download in my Organization.

        Thanks in Advance,

  5. hi

    I have one more option in my ikm but still its not working its throwing error:

    see the error.
    and can you please share the ikm isteslf which you built..


    ODI-1228: Task test1 (Integration) fails on the target ORACLE connection OBIEE_STAGING.
    Caused By: java.sql.SQLException: Missing IN or OUT parameter at index:: 1
    at oracle.jdbc.driver.SQLStateMapping.newSQLException(
    at oracle.jdbc.driver.DatabaseError.newSQLException(
    at oracle.jdbc.driver.DatabaseError.throwSqlException(
    at oracle.jdbc.driver.DatabaseError.throwSqlException(
    at oracle.jdbc.driver.DatabaseError.throwSqlException(
    at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(
    at com.sunopsis.sql.SnpsQuery.executeUpdate(
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.executeUpdate(
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execStdOrders(
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(
    at com.sunopsis.dwg.dbobj.SnpSessTaskSqlI.treatTaskTrt(
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(
    at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(
    at oracle.odi.runtime.agent.processor.task.AgentTask.execute(

  6. Hi Bhabani,

    I have small doubt regarding the commit frequency,i think this option is work between loading the data from I$ to target table,If i am wrong please correct.

  7. Did you have the incremental version of the same working? The initial was exactly i was looking for to process 8M+ records.

  8. Glad, about the update regarding the control append for initial load on commit frequency.
    Well, we are looking out for an Incremental update on the commit frequency. Can you please help me out with a solution or the ways in deriving a solution..

    Thanks in advance,

    • Hi Venkat,
      I could not post on incremental update bcz I didnt get enough time to design another one. I will try to make by this weekend and will update you once this is done. Sorry for being lazy on this.


      • Thank you very much, for the response.

        Well on working with the Control append which is attached above, I found the log counter not working in the Insert new rows.
        I tried the every possibility like “Log counter: Insert” & “Log Final command: Enable” etc.

        But we are certainly unable to get the no.of rows & no.of Inserts count. As it was very much required for the client to check the data available.

        Kindly once look in to this issue, as we owe you alot for the work which you did.


  9. Hi Bhabani,

    I am new to ODI. I am getting following error while executing package :

    ODI-1226: Step DATA_LOAD_STAGE fails after 1 attempt(s).
    ODI-1240: Flow DATA_LOAD_STAGE fails while performing a Loading operation. This flow loads target table STAGE_ITEM_MOD_SYN.
    ODI-1228: Task SrcSet0 (Loading) fails on the target ORACLE connection ORCL_NMC_INFC.
    Caused By: java.sql.SQLException: Missing IN or OUT parameter at index:: 12

    Could you please tell why this error occurs and how to resolve it


      • SOURCE CODE :

        IT.UDA_Name C10_UDA_NAME,
        IT.IBC C3_IBC,
        IT.USER_ID C8_USER_ID,
        where (1=1)

        TARGET CODE :

        err_num NUMBER;
        err_msg VARCHAR2(300);
        insert into ODISTG.C$_0STAGE_ITEM_MOD_SYN
        when others then
        err_num := SQLCODE;
        err_msg := SUBSTR(SQLERRM, 1, 300);
        insert into odistg.ITEM_MODFCTN_ERROR(ITEM_NUMBER,ERR_MSSG) values( :C1_ITEM_NUMBER,err_msg);

  10. Hi Bhabani,

    I am in need of the IKM that you have built here, basically i need to commit after every 100,000 records as my source data runs into tera bytes. Please can you email me the IKM that you modified?

    Thanks in advance,

  11. I am curious when the LKM SQL to Oracle is executed, what query it is processing at source. Is it copying the data file or it is using the Java code to pull the data and insert.

    And how much is the commit frequency when it is inserting thr I$ table. There is a possibility that there is not commit frequncy when loading data from source.

  12. when i am uisng this code in ikm control append after 2 hours its shows error like this

    ODI-1298: Serial task “SERIAL-MAP_MAIN- (60)” failed because child task “SERIAL-EU-OCHBASELINE_UNIT (90)” is in error.
    ODI-1298: Serial task “SERIAL-EU-OCHBASELINE_UNIT (90)” failed because child task “Insert new rows-KM Oracle Control Append_commit_intaerval- (120)” is in error.
    ODI-1228: Task Insert new rows-KM Oracle Control Append_commit_intaerval- fails on the target connection APSPSREP.
    Caused By: java.sql.SQLException: ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS2’

    After using this code what is the use of this commit interval.during loading i will check paralley for atelease 10000 records are inserted or not

    Pleas reply me this asap.

  13. Hi Bhabani,
    I have a doubt on COMMIT FREQUECNY STEP.
    1.I am using ODI 12c-, and I am unable to import the KM .It is asking for –“Please specify an Upgrade Key for converting Object ID’s to Unique Global ID’s.”. Kindly suggest on the Same.
    2.If I am creating a task of COMMIT FREQUENCY in the Current KM- IKM Oracle Control APPEND. Then shall I insert the code as it is (commas /quotes to be ignored) provided by You and block the code of “insert new rows” task. As I am confused why 2 times the same insert into Target table command will execute.
    Below is the KM code for INSERT NEW ROWS —

    insert /*+ */ into



    insert /*+ */ into



    FROM (
    <%for (int i=odiRef.getDataSetMin(); i


    JRN_FLAG ‘D’ (1=1)


    Please suggest,as I have to implement the same .Thanks in advance.

    • Hi Swadhin,
      For #1. You need a upgrade key so that any future import from 11g will match with the guid for 12c. You can import it in duplicate mode.
      For #2. Both statements are required as the same KM can be used for journalized as well.

      Just a note, see if any of the substitution apis are impacted. I have not validated it in 12c.

  14. Hi Bhabani,

    Thanks for putting this together. quick question in case i set the Commit frequency to 100000 and then the total number of record to be inserted is less than the commit frequency set value ex: 90k then does it going to commit or not.

    we have this requirement came recently as we are pushing some data extract from Hyperion to Oracle ERP , currently was a work around we are managing by a process control table in the target system. if this customized KM meet our requirement then that will be great to implement .


  15. Hi Bhabani,

    Thanks for the script. It’s working fine.
    I have a small request. Is it possible to update the no of inserts in IKM.


  16. If I want to replicate the commit frequency in IKM Oracle insert where there is only target command and no source command then how it would be?

Comments are closed.