Transaction Control in ODI

Transaction Control in ODI:

Hii Guys!!!
Today we will be discussing on  how a transaction can be controlled in ODI. Many people specially beginners, always get confused with rollback/commit feature in ODI. So lets get started.

Software Details:

ODI 11.1.1.6
OS: Windows 7 Ultimate

Create two interface to load data from two different source tables to one target table.
Source Table: EMP (14 rows), EMP_TGT2 (12 rows)
Target Table: EMP_TGT3

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

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

42 comments

Thanks for the information bhabani..I didn’t have any idea to use the transaction facility in ODI. Now I am clear on this. I was going through below post. http://odi-ee.blogspot.in/search/label/ODI%20Manual%20Commits There was line that manual commit will degrade performance. Why is it so ?

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.

Hi Bhabani,

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?

Regards,
Ravi

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 i have done according to your post but i got belw error please clarify why i did get it

ODI-1226: Step SELECT_CONTROL fails after 1 attempt(s).
ODI-1232: Procedure SELECT_CONTROL execution fails.
Caused By: org.apache.bsf.BSFException: exception from Jython:
SyntaxError: (“no viable alternative at character ‘\x94′”, (”, 1, 16, ‘raise Exception(\x94 \\n \\n \\n Total count is : 22 \\n \\n \\n \x93)\n’))

Hi Nari,
I think you have copied the code. Please replace the double quote using your keyboard. Then it should work I believe.

I tried and resolved it anyways thank you for responding……………

Good to know its working for you

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.

Hi Bhabani,

I have some questions for you.

1. I want commit steps in interval. suppose if we have 10 lakh records so i want to commit records after interval of 1 lakh.is it possible if yes then please let me know how we ca nachieve this?

Using existing KM, i think you can select max 1000. You can refer my other post using customized KM.
If you need optimization then do some tweaks in the KM.

http://dwteam.in/commit-frequency-in-odi/

Hi Bhabani,

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?

Regards,
Lavanya

I didnt get exactly about your requirement (columns from master and child table). Can you please clarify bit further ?

Hi Bhabani,

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

Regards

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 ?

Hi,

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.

Regards

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.

Hi Bhabani,

Thanks for your response.Could you please explain the steps to change the transaction for the loading steps of the KM.

Regards

It is very easy to modify the KM. However I have added you in gmail. We will discuss there.

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)?

Yes, You can commit the same transaction in another procedure but the transaction level should be same.
Can you please explain further about your second question?

I have a case very similar to the tutorial above:
INTERFACE –> select_count1 –> PROCEDURE –> select_count2

the INTERFACE is identical to your tutorial:
COMMIT = FALSE
Delete target table: Transaction 1, No commit
Insert new rows: Transaction 1, No commit
Commit transaction: Transaction 1, Commit

select_count1 correctly returns Total Count = 0 because the rows are not committed

the PROCEDURE has the following settings (command on target tab):
Transaction: Transaction 1
Commit: Commit
Transaction Isolation:

select_count2 unexpectedly return Total Count = 0

QUESTION: what is still missing to have the INTERFACE insertions committed during (or soon after) the procedure executions and have select_count2 > 0?

Thank you in advance for your help!

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

thanks & Regards,
Raju Minukuri

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

Bhabani,

Thanks for the information sharing. Shall we get the number of records were inserted into target tables with in a package as part of ODI send mail to the receipient?

Yes you can. Just after the interface you have to put substitution api to get insert count. < %=odiRef.getPrevStepLog("INSERT_COUNT")%>

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?

Hi Geeta,

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.

Sir,
I love the topic of this post but unfortunately I can’t see any of the posted pictures. Can anything be done?

Thanks Tom for pointing out. We have fixed the link issue.

Excellent! Thank you for fixing, Well done post!

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.

Also I am using IKM Incremental Update (Merge) for this in all 3 mappings nad also using Transaction 1 for merging step of KM and also in last update procedure…everything is under Transaction 1

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.

Thanks for the useful information.

While extracting data from source, initially data will insert to C$ table and it is auto commit. So I want to know that after how many insertions commit will happen in C$.

In other words frequency of Auto commit in ODI?

Hello,

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.

Hi Bhabani,

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.

Please clarify this.

Hi Bhabani

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.

Please explain.

Leave a replay to raju minukuri Cancel reply

required*

Are you a human? *