It is a common scenario within ODI to want to know the results of the execution of a package or interface (aka ELT) and execute additional logic based on the initial result. For instance, you might want to execute interface X only if there were no inserts made into the target table of interface Y. Another scenario could be you want to send an email notification to an Admin if there were more than n number of inserts or updates made to the target table of the interface. Or, you may have a situation where you execute a package and you expect it to do one or more inserts or updates to the target tables of the interfaces. If there are none, you then want to raise an exception. There are plenty of other use-cases where you could be interested in capturing your ODI interface’s execution results.
My scenario below is similar to the third scenario I mentioned above. The use-case is as follows: After the successful execution of a package with multiple interfaces, if there are no inserts or updates made to either of those interfaces I want to raise an exception. Luckily for us, the ODI interface execution results are stored in the Repository tables and for my example all of the information I need is in the SNP_SESSION table.
1) First, I create a numeric variable, Odi_SessionId, with the following refresh SQL code. This grabs the session id of the package this variable is refreshed on.
SELECT <%=odiRef.getSession(“SESS_NO”)%> FROM DUAL
2) To check if there were any inserts in a package, I created a numeric variable, Odi_Log_Ins, with the following refresh SQL code.
SELECT NVL(NB_INS,0) FROM SNP_SESSION WHERE SESS_NO = ‘#Odi_SessionId’
3) To check if there were any updates made in a package, I created a numeric variable, Odi_Log_Upd, with the following refresh SQL code.
SELECT NVL(NB_UPD,0) FROM SNP_SESSION WHERE SESS_NO = ‘#Odi_SessionId’
4) I created a package, CHECK_LOGS_PKG.
5) In the first step on the CHECK_LOGS_PKG package, I display the input variables to this package. In ODI 11.1.1.5 you do not have visibility into the values of your variables in the execution logs, but fret not. Fortunately, there is a work-around. I created a procedure, ‘Input Params’, with the following Jython command./* <%= odiRef.getSession(“SESS_PARAMS”) %> */It is important to check the radio box ‘Ignore-Errors’. This procedure shows a warning in the execution logs which is normal. In the ‘Code’ tab of the execution logs, the Input Params values are displayed. In our case, there is only one input variable, Odi_SessionId.
6) In the second step of the CHECK_LOGS_PKG package, I declare the input variable of this package, Odi_SessionId. This is the variable that holds the value of the session id of the package that is to be called using this package. It does NOT hold the value of the session id of the package CHECK_LOGS_PKG. In your other packages (for example Sample_PKG shown below) that will execute this package, you will add the step Odi_SessionId Refresh Variable (variable created in Step 1) before you call the CHECK_LOGS_PKG package.
7) In the third step of the CHECK_LOGS_PKG package, I refresh the variable Odi_Log_Ins (variable created in Step 2).
8) In the fourth step of the CHECK_LOGS_PKG package, I evaluate the variable Odi_Log_Ins > 0. If true, we stop. If false, we move to step five of the package.
9) In the fifth step of the CHECK_LOGS_PKG package, I refresh the variable Odi_Log_Upd (variable created in Step 3).
10) In the sixth step of the CHECK_LOGS_PKG package, I evaluate the variable Odi_Log_Upd > 0. If true, we stop. If false, we move to step seven of the package.
11) In the seventh step of the CHECK_LOGS_PKG package, I raise my exception in the form of a call to a web service which sends the email to an Admin (the web service logic is not covered in this blog).
I hope the use-case used above to capture your ODI package execution results can be used as a sample to create others. My use-case used SNP_SESSION table, but there are other session related tables in the repository that hold information you may need. Feel free to leave a comment if you have any questions.