7 Replies Latest reply on Jun 29, 2009 10:02 AM by Guru Sankar

    ODI rollback

    rameshchandra85-JavaNet
      Hi,

      Whether ODI session will rollback the transcation if it failed at half way ? For example: If the ODI fails after the ODI deleting the records in target , will the ODI rollback that transaction ?

      Thanks,
      Ramesh
        • 1. Re: ODI rollback
          Sutirtha Roy
          Hi,

          At the end of any Session, a COMMIT statement is sent on all transactions involved in the execution.

          If an error is detected during the execution, an explicit ROLLBACK will be sent on the Transaction chanel in error... but ONLY IF the Task in error is NOT in "Ignore error" mode.

          More precisely, if the connections to Master and/or Work Repositories are lost during the running of a Task set with "Ignore error" mode, this event will be considered as a Warning and a ROLLBACK statement WILL NOT be performed. Subsequently, once the end of Session is reached, the default COMMIT is performed resulting in inconsistent data.

          Thanks,
          Sutirtha
          • 2. Re: ODI rollback
            rameshchandra85-JavaNet
            Hi Sutirtha,

            If I have a separate ODI procedure for deleting the records with some particular condaition, how can I rollback that transaction?

            Thanks,
            Ramesh
            • 3. Re: ODI rollback
              Guru Sankar
              Hi Ramesh,

              In Integration KMs ODI hit commit only if all the preceding steps are passed without errors only. Means, if you see IKM SQL Control append, Commit step will happen after Insert /delete steps sucess and if you see each and every step the transcation level is 1 and Commit option as No Commit. So at run time delete will fire the query and doesn't commit till Commit step is reached without any errors. You need to assume that commit not only for insert its for delete, truncate and so on...

              So coming to your case, you can fire your delete script by setting Transaction as 1 and commit as No commit untill your check point step and do a Commit afterward (same like IKM SQL Control Append).

              Makes sense?

              Thanks,
              G
              1 person found this helpful
              • 4. Re: ODI rollback
                rameshchandra85-JavaNet
                Hi,

                I opened the knowledge modules in my project. All are set to default "Auto Commit". Does that means roll back is not happening ? Also whats the difference between Transaction level 0 to 6 means ? Also if I set transaction level 1 and "no commit" in my custon procedure will the ODI commit that procedure at the end of transaction ?

                Thanks,
                Ramesh

                Edited by: rameshchandra85 on Jun 29, 2009 2:33 AM

                Edited by: rameshchandra85 on Jun 29, 2009 2:35 AM
                • 5. Re: ODI rollback
                  Guru Sankar
                  Hi Ramesh,

                  Yes, you are right and got in to the lane. Probably all DDL statements are set to Auto Commit except Delete/insert steps as it DML statements where u can roll back.

                  Yes in your custom procedure if you (have) set Delete / insert / update step to Transaction 1 and Commit to No Commit till your desired step where u want to commit all the transactions.

                  You can make a replica of IKM SQL Control append.

                  Thanks,
                  G
                  • 6. Re: ODI rollback
                    rameshchandra85-JavaNet
                    Hi Guru,

                    So if I set Transaction 1 and Commit to No Commit in my custom procedure, when the ODI session comes to DDL statements, my procedure also auto commits , right ?

                    Thanks,
                    Ramesh
                    • 7. Re: ODI rollback
                      Guru Sankar
                      Hi Ramesh,

                      If that way, you should have a COMMIT statement executed after your DDL statements/steps. That statement should set to Transaction 1 and Commit.

                      Thanks,
                      G