Reverse engineer above three tables and create normal interfaces. Here I have taken IKM Sql Control Append and CKM Oracle as KMs.

Interface 1:

Interface 2:
Data present in EMP table
Data present in EMP_TGT2 table.
For 1st interface I have set the commit to FALSE.
For second interface commit is set to true ( default value ).
Save everything and create a new ODI procedure. Command on source : SELECT COUNT(*) COUNT FROM EMP_TGT3
Command on target using Jython Technology: raise Exception(” \n \n \n Total count is : #COUNT \n \n \n “)
This is just to log the count of target table to verify whether the commit is working properly or not.
Next create a package and put them as given in below screen-shot and execute it.
Go to operator and expand the session steps. Open the select_count procedure. Here you can see the count(*) of target table is zero it means data was not committed on the fly.
Now open the last step which is the same select_count procedure. You can see the total count is 26 [12+14] means the data is committed at the end of execution.

Well the reason behind this is pretty simple. If you will open the KM you can see the transaction was set to Transaction1. So in case of 1st interface this transaction commit was set to false but in second it was set to true. Hence data got committed. You can also commit the transaction 1 in another procedure after the interface as well.

So what we observed here is that, we have the flexibility to commit the transaction at the end of execution instead committing after every interface. This is required if we have dependency between interfaces and we have to rollback in case of an interface fails.

Now what will happen if we will set the COMMIT to false in second interface. Interesting right ?? Two tests needs to be carried out to come out with a conclusion.

1. Set the commit to false in both interface. Do not commit the transaction through out the package. Make sure the package will fail at the last step ( lets say you have odiFileCopy where ODI doesnot find the file that needs to be copied.)

2. Set the commit to false in both interface. Do not commit the transaction through out the package. Make sure the package will execute successfully ( Do not put any wrong step at the end of package like #1).

You will notice in 1st case, there are no records available in target but in case of second it is.

The reason being whenever a session is successful , ODI always initiates a commit. If it fails then it won’t.
Thats all for today.
Thanks Guys 🙂 .

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 Ren, Thanks for visiting. What Suraj said is correct. If you wont commit then you might face problem when you will be dealing with millions of records. You may get error java.sql.SQLException: ORA-30036: unable to extend segment by 4 in undo tablespace . So try avoiding manual commit.

I Have A question for u.
i have a package which consists of several objects like interfaces,procedures,variables.when running the package if it is throwing error, i want to rollback the previously executed objects work. can we achieve this?

yes we can. But make sure you are ready to handle undo tablespace. Dont set the commit to false in all interfaces. same thing for procedures also ( i.e all dml operation ). At the end if your session will be successful everything will be committed.

Hi Bhabani,
in my case, i am trying to execute one scenario but after successfull execution of it i came to know that i want to run previous version of scenario.so how i can roll-back same scenario????Can you help me for same?

I guess you mean rollback the transaction. If it is then you canot do it after the successful execution. The only thing that you can do is you have to delete the new records inserted into target table based on some audit columns. Then execute the old scenario again. This will update the existing records and insert if new records are available.

i have a package with multiple interfaces.i need to reference one of the columns from my master table to its child tables.which when tried by setting commit option as false across the interfaces is resulting an error.
How to achieve this?

In my package i have 4 interfaces and procedures,there are Foriegn keys on the target tables.Now when i execute the package in same transaction (transaction 1) with no commit i am getting join error in the child table because the data is not found in its parent table.
But as the package is getting executed in a single session ideally this error should not come.Please clarify

I agree, you shouldnot face such error. Are you using the same KM over all the interface ? Are you sure the transcation is set to Transcation1 for all the interfaces ? Also doesit work when you set the commit to true for all the interfaces ?

i am using the same KM across all the intrerfaces.I have set commit to false in all the interfaces(by default all the km’s execute on transaction 1.please correct me if this is not the way it works) also i have set the transaction as transaction 1 in my procedures.
Yes if i give commit for invidual interfaces it works.

Ok. If I understand you properly you might be getting the error in loading step ( If LKM is used then error will come in Load Data Step, if only IKM is used like the above article then error might be coming in insert flow into I$ table). If it is true then you have to change the transaction to transaction 1 for those loading step in your KM. Hope you got my point. LKM uses a different transaction to load data to C$ or I$ and hence causing the issue.

What if I want to commit the transaction 1 in a later procedure instead of a later interface?
What are the settings on the committing procedure in terms of “Transaction”, “Transaction Isolation” and “Commit” (in the ‘Commit on Target’ tab)?

I believe you did a typo in 7th line ( should have been no commit). Am I right?

The approach looks good to me. Can you open interface logs from operator and find the number of insert during insert new rows step?
Also send me some screenshot of procedure and interface (only for commit/no commit steps.).

Hi babani
i am raju i have been working on odi from 2 years
i have a requirement of trial mode in which records wont be inserted in to table but we have to know the count of updated and inserted records in I$ table can u tel me how we can achieve this

For trial mode you better keep a dedicated km. You can open the insert and update (to target table from IKM) steps and if you scroll down you can see a option to check and uncheck.
If you have opened “insert new rows step”, you can uncheck the INSERT option. This will evaluate the condition to FALSE. During execution ODI will skip that step.

You can check the code generation in SIMULATION mode as well.
Let me know if I answer your question.

Hi babaani,
thank you so much for response
but in our requirement
first of all i have file ,staging table and final table
file which have 10 records loaded to staging table using interface1
then from staging table we have to load the data to final table
here we have real time table having the field value as real or trial
real means 10 records pushed from file to staging and then staging to final table
of trial means 10 records pushed from file to staging and then no record should be inserted in to final table but we have to know the error ,insert and updates in the data

for this perpose i have created a variable with the name of mode_status
name runmode
type alphanumeric
keephistory latest value

in refreshing

query is

select runmode_status from dev3_oim.runmode

created an option in ikm mode_custom in which i have entered the variable

but how to use the variable in km in if loop
how to use variables in KMS
if u have any idea please help me

right now am thinking of customizing km
insert new rows and update existing rows options in km\
i want use an if loop to compare variable if it is real then insert but i dont know how to compare the variable value inside km please if you know kindly help me
it’s very urgent

Hi Bhabani,
I am using IKM SQL to File Append and the Transaction is set to Autocommit and I have not set the Commit frequency. This IKM is taking lots of time when its writing the data to a mounted drive than locally. Does autocommit means commiting the records at the end of the whole transaction?

Can you try to create a file using OdiOutFile and check how much time it is taking? This is not actually the issue with ODI rather at OS level call.
You can better create the file in local and then move to shared location.

Hi Bhabani,
I am facing a similar issue as Lavanya mentioned in the above comments. Scenario is like this:
I have a parent table A and its child tables B and C and for these 3 I have 3 mappings in ODI. I want the whole run in the same transaction that’s why I have put commit to be False in all the mappings and in the last there is a ODI procedure that do some updates in the base tables and then the whole transaction is committed over there. but my tables B and C are dependent on A’s data so how we can achieve this? Please suggest.

Can you post the code generated and the step name where it fails? Check the count of LKM and IKM insert steps. If you are using LKM and IKM make sure all of them are using T1. Try to print the count of each table like I am printing in operator after every interface for debugging purposes.

I dont think that you got 0 lines because you have set commit to false, but because the select query have not the same transaction number, if you try to set Transaction 1 in the procedure query you’ll get row number even commit is set to false.

As my understanding,
When the Commit was set to false in both interfaces and package was successful then records are available in target because we have set the ‘Commit transaction’ step in KM as Commit.
If we have set the ‘Commit transaction’as No commit in KM then there will be no records in target is this the case ..??

You have mentioned
“whenever a session is successful , ODI always initiates a commit. If it fails then it won’t.” depends on Commit transaction step in KM or it is irrespective of that.

I have a requirement in which I am having two interfaces, both interfaces are loading the data into two different target tables.
Now suppose the first interface executed successfully and has committed the transaction. But the second interface is in error. So, can we rollback the transaction committed by the first interface.

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.