Wednesday, June 1, 2011

ODI - Manual Commits

--

In this post, we will look at one of the ways to manually commit data in ODI.
There are cases during data loads where you want to commit data when all your interfaces have run successfully. If anyone fails , commit should not be issued.

This is in context of a database only.

Most of the KM's provide a "COMMIT" option which can be either set to true or false.

What happens when the COMMIT option is set to false ? Does it commits the data or it does not?

Note: All the cases below have interfaces with COMMIT option set to false.

Case 1)  Executing an Interface or scenario for an Interface -

Lets a take simple example of a KM - IKM SQL Control Append applied to an Interface EMP_LOAD.


Note: TRUNCATE option is set to true.

Executing the scenario for the interface EMP_LOAD.


The COMMIT task was not executed.  The only task that were executed were Truncate and Insert.
But the data in the table was still populated.


This clearly indicates that ODI will issue COMMIT at the end of a successful session.

Case 2) Where an Interface successfully completes and other interface fails in an ODI Package - 

Here is an example of an ODI package with three interfaces

Dropped the target table for interface JOBS_LOAD so that the package fails.

Executing the package

DEPT_LOAD interface executed sucessfully and the JOBS_LOAD interface failed. The session failed.
The data was inserted into Department table by DEPT_LOAD interface but it was not committed as the session failed.


Case 3) Where all Interface successfully completes in an ODI Package -

Executing the package after fixing the Interface.

The ODI package executed successfully , no commits were issued by any of the Interface.
But looking into any of  the table, data is populated. As seen in Case 1 , ODI issues commit at end of an successful session.


Conclusion - Manual Commits can be achieved by having all the interfaces within an ODI package with COMMIT option set to false.

Few things to consider:
1) Adding scenarios of the interface to the package will not work as ODI will launch a new session for each
    scenario.
2) DDL's cannot be reversed.
3) Performance can degrade, infact manual commits should be avoided whenever possible.


--