Transaction Control in ODI:
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.
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.
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 🙂 .