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”) ) { %>
declare
v_count number:=1;
begin
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
commit;
end if;
v_count:=v_count+1;
end loop;
commit;
end;
<% } else { %>
declare
v_count number:=1;
begin
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 (
<%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) <% } %>
<%=odiRef.getJoin(i)%>
<%=odiRef.getFilter(i)%>
<%=odiRef.getJrnFilter(i)%>
<%=odiRef.getGrpBy(i)%>
<%=odiRef.getHaving(i)%>
<%}%>
))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
commit;
end if;
v_count:=v_count+1;
end loop;
end;
<%}%>

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 🙂 .

0

About the author

Bhabani(http://dwteam.in) - Currently Bhabani is working as Technical Lead Product Development at Harman International. He has good expertise on Oracle, ODI, Pervasive Data Integrator, MSBI, Talend, Cloud Integration (AWS, GCP, Azure) , Map-R and Apache Airflow. He is also contributing in ODI-OTN forum for last 7 years. He is from India. If you want to reach him then please visit contact us page. If you have any doubts or concerns on the above article, please put your question here. Dw Team will try to respond it as soon as possible. Also dont forget to provide your comments / suggestions / feedback for further improvement. Thanks for your time.

Similar Posts

30 comments

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

Thanks
sateesh

Its not that much difficult if you do 20 to 30 hours research on this. You can refer for initial understanding
http://odiexperts.com/jython-beginners-guide-for-odi/

Thanks.

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.

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.

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

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,
Hari

I have replied to your email with the actual code. Thanks.

Hi Bhabani,
sorry for the late response. Thanks for the code, its working fine now

cheers,
Hari

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..

Error:

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(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1737)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3467)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350)
at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java:665)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.executeUpdate(SnpSessTaskSql.java:3218)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execStdOrders(SnpSessTaskSql.java:1785)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java:2805)
at com.sunopsis.dwg.dbobj.SnpSessTaskSqlI.treatTaskTrt(SnpSessTaskSqlI.java:68)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2515)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:534)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:449)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1954)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:322)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:224)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:246)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:237)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:794)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:114)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:662)

can you please provide the imk which you built for this functionality.
cimmit frq.

Thanks in advance

Replied to your inbox

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.

Shankar Narayanan / Reply

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

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,
Venkat.

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.

Thanks

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.

Thanks

Hi Bhabani,

Could you please send Commit Customization code to My Mail Id.

Thanks,
Venkatesh

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

Thanks,
Lakshmi

Please provide the code generated for that step. Seems to be some issue with bind variables.

SOURCE CODE :

select
IT.ITEM_NUMBER C2_ITEM_NUMBER,
IT.PRIMARY_DESC C6_PRIMARY_DESC,
IT.SECONDARY_DESC C7_SECONDARY_DESC,
IT.UDA_Name C10_UDA_NAME,
IT.UDA_VALUE C11_UDA_VALUE,
IT.IBC C3_IBC,
IT.SUPPLIER_SITE C4_SUPPLIER_SITE,
IT.PRIMARY_SUPP_IND C5_PRIMARY_SUPP_IND,
IT.MODIFICATION_TYPE C1_MODIFICATION_TYPE,
IT.USER_ID C8_USER_ID,
IT.CREATION_DATETIME C9_CREATION_DATETIME
from #NMC_ITEM_MODIFICATION.V_FILE_PATH/INCOMING//#NMC_ITEM_MODIFICATION.V_FILE_NAME.csv IT
where (1=1)

TARGET CODE :

DECLARE
err_num NUMBER;
err_msg VARCHAR2(300);
BEGIN
insert into ODISTG.C$_0STAGE_ITEM_MOD_SYN
(
C2_ITEM_NUMBER,
C6_PRIMARY_DESC,
C7_SECONDARY_DESC,
C10_UDA_NAME,
C11_UDA_VALUE,
C3_IBC,
C4_SUPPLIER_SITE,
C5_PRIMARY_SUPP_IND,
C1_MODIFICATION_TYPE,
C8_USER_ID,
C9_CREATION_DATETIME
)
values
(
:C2_ITEM_NUMBER,
:C6_PRIMARY_DESC,
:C7_SECONDARY_DESC,
:C10_UDA_NAME,
:C11_UDA_VALUE,
:C3_IBC,
:C4_SUPPLIER_SITE,
:C5_PRIMARY_SUPP_IND,
:C1_MODIFICATION_TYPE,
:C8_USER_ID,
:C9_CREATION_DATETIME
);
exception
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);
end;

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,
Jay

Its there in the post. Click on the link to download it.

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.

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

EIM_CONTACT.
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.

Hi Bhabani,
I have a doubt on COMMIT FREQUECNY STEP.
1.I am using ODI 12c-12.2.1.2.0, 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
(

)
select

from

insert /*+ */ into
(

)

select

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

select

from
where
JRN_FLAG ‘D’ (1=1)

) ODI_GET_FROM

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.

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 .

Thanks
Khirod

Leave a reply

required*

Are you a human? *