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:

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

About Bhabani 86 Articles
Bhabani has 12 plus years of experience in Data warehousing and Analytics projects that has span across multiple domains like Travel, Banking and Financial, Betting and Gaming Industries. Solution areas he focuses on designing the data warehouse and integrating it with cloud platforms like AWS or GCP. He is also a Elite level contributor at OTN forum more than 9 years. He loves to do experiment and POC on different integration tools and services. Some of his favorite skills are Redshift, Big Query, Python, Apache Airflow, Kafka, HDFS, Map Reduce ,HIVE, Habse, Sqoop, Drill, Impala.


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

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


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

  2. 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’))

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

  4. 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 it possible if yes then please let me know how we ca nachieve this?

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


      • 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


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


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

  6. Hi Bhabani,

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


      • Hello Bhabani, I’m facing the same error and I din’t figure out a way to set transaction 1 to the interfaces. I’ve done some tests creating a custom KM, however, I’ve done some tests without sucessfull. Could you help me about it? Thanks a lot.

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

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

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

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

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

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

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

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

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

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

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

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

  19. Hi Bhabani,

    I would like to express my thanks to you for putting up this article.

    Been almost a year since I took over support for a ODI project. I spent half a day trying to understand how/why the records were inserted into the table when the COMMIT was set to ‘False’.

  20. Hi Bhabani,

    One doubt on parallel execution…

    We have 6 interface in package and all set to go in parallel and if any of these interfaces failed then nothing should be committed …how to set this mechanism…commit should happen after all success execution of all interfaces in the package else no commit …
    Please suggest on this…Thanks.

  21. Hi Bhabani,

    One doubt on parallel execution…

    We have 6 interface in package and all set to go in parallel and if any of these interfaces failed then nothing should be committed …how to set this mechanism…commit should happen after all success execution of all interfaces in the package else no commit …
    Please suggest on this…Thanks.

Comments are closed.