Commit Frequency in ODI

Commit Frequency in ODI:

Hello Everybody. I hope you all are doing well. Today I am going to demonstrate about the 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 this. But that’s up to you. You can do your own optimization. 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 update the Incremental update codes once I am done with it. It is quite simple to develop it in your system.
To down the KM click here. If you are facing problem in downloading then copy pest below codes  in to the insert new rows step of the KM and create a new option called COMMIT_FREQUENCY.

/*In case of copy pest 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 Sr Development Engineer at Harman International. He has good expertise on Oracle, Oracle Data Integrator, Pervasive Data Integrator, MSBI, Talend and Java. He is also contributing in ODI-OTN forum for last 5 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

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

Leave a replay to Samarendra Cancel reply

required*

Are you a human? *