Blog

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

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.

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.

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.

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)

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

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.

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.

Disclosure

The views expressed on this blog are those of the author and do not necessarily reflect the views of Oracle. All content and s/w code on this site are offered without any warranty, or promise of operational quality or functionality.