Writing execution results to File using Jython in ODI

Writing execution results to File using Jython in ODI

In this post, I am going to show how to write files in customized format by leveraging Jython technology in ODI. Out of the box, ODI provides support for delimted, fixed length, xml and complex files. But if there is a need to produce pdf or custom formated output, we can use Jython to achieve.

To demonstrate this capability, I am taking the example of write execution details of ODI to a custom formated file. The file format will look like this following.

/*------------------------------------------------------------------------------------------------------------------------------------------------*/



                    EXECUTION REPORT FOR SCENARIO : WRITE_EXECUTION_REPORT  ( THU NOV 14 10:34:33 2013 )                    



/*------------------------------------------------------------------------------------------------------------------------------------------------*/



StepNo StepName                                   StepType               BeginningTime     EndTime           Duration      Status     Error_Msg



------ --------------------------------------- ------------------- --------------------- --------------------- ---------- -------------- ----------



0      int_file_table                          Interface           14-NOV-2013 10:34:11  14-NOV-2013 10:34:12  1          Success        None



1      write_execution_report                  Procedure           14-NOV-2013 10:34:12  None                  0          UNKNOWN        None



/*------------------------------------------------------------------------------------------------------------------------------------------------*/

Following are the steps I am going to follow to demonstrate this use case.

1. I will create a procedure which will connect to ODI internal tables and get the execution details for the current session_id

2. I am going to create a file_to_table interface just to have a success scenario and a procedure which raises PL/SQL exception to demonstrate the error scenario.

3. We will create a package and place the interfaces and procedure for success/error scenario and also have the execution report procedure we developed in first step as a last steps for success as well as error scenario to extract the execution details to file with formatting.

Before we write the procedure, Lets understand the ODI internal tables a bit on how they store the execution details.

1. SNP_SESSION - stores the session_id and detailed related to session

2. SNP_SESS_STEP - All interface, package, scenario  and procedure details.

3. SNP_STEP_LOG - This has the steps within each odi object.

Just to put the table relation ships into perspective, showing the sample data for a sample session here. This data is for the below specified package in ODI.

SNP_SESSION  - Has one row per execution at the package level here.

SNP_SESS_STEP - represents objects inside a package with design time details.

SNP_STEP_LOG - represents objects inside a package with run time details (including error_message).

SNP_SESS_TASK - represents the KM, details related to interface,procedure, tools in a package with design time details .

SNP_SESS_TASK_LOG - represents the KM, details related to interface,procedure, tools in a package with run time details (including error message)

In the execution report, I developed I am not showing task level details, instead it stops at the step level. But I wanted to show here that task level details can also be captured in the log.

Execution Report Procedure

Create a procedure and add one command in the details with the following Jython code. Make sure that Jython technology is selected in the target tab. This procedure should be added as a last step in the package so that it takes all the execution details and puts it into formatted file.

This can be used for successful as well as for error conditions for producing the log.

Please find below the complete code for using it in your implementation.

1. It take the connection for current work repository
2. Using the current session_no to retrieve execution details using the query
3. Spools the query data with formatting to a file.

import string
import java.sql as sql
import java.lang as lang
import re
import time
sourceConnection = odiRef.getJDBCConnection("WORKREP")
output_write=open('c:/temp/Execution_Report_<%=odiRef.getSession("SESS_NO")%>.out','w')
sqlstring = sourceConnection.createStatement()
localtime = time.asctime( time.localtime(time.time()) )
print >> output_write, "/*------------------------------------------------------------------------------------------------------------------------------------------------*/\n"
print >> output_write,'%s\n' % (str('  Execution Report for scenario : '+'<%=odiRef.getSession("SESS_NAME")%>'+'  ( '+localtime+' ) ').upper().center(124))
print >> output_write, "/*------------------------------------------------------------------------------------------------------------------------------------------------*/\n"

sqlstmt="select rownum, \
       sess.sess_no, \
       sess.sess_name, \
       step_log.nno, \
       step.step_name, \
       case when step.step_type='F' THEN 'Interface' \
            when step.step_type='VD' THEN 'Variable declaration' \
            when step.step_type='VS' THEN 'Set//Increment variable' \
            when step.step_type='VE' THEN 'Evaluate variable' \
            when step.step_type='V' THEN 'Refresh variable '\
            when step.step_type='T' THEN 'Procedure' \
            when step.step_type='OE' THEN 'OS command' \
            when step.step_type='SE' THEN 'ODI Tool' \
            when step.step_type='RM' THEN 'Reverse-engineer model' \
            when step.step_type='CM' THEN 'Check model' \
            when step.step_type='CS' THEN 'Check sub-model' \
            when step.step_type='CD' THEN 'Check datastore' \
            when step.step_type='JM' THEN 'Journalize model' \
            when step.step_type='JD' THEN 'Journalize datastore' \
            ELSE 'UNKNOWN' END as step_type, \
      to_char(step_log.step_beg,'DD-MON-YYYY HH24:MI:SS') as step_beg, \
      to_char(step_log.step_end,'DD-MON-YYYY HH24:MI:SS') as step_end, \
      step_log.step_dur, \
      case when step_log.step_status='D' THEN 'Success' \
            when step_log.step_status='E' THEN 'Error' \
            when step_log.step_status='Q' THEN 'Queued' \
            when step_log.step_status='W' THEN 'Waiting' \
            when step_log.step_status='M' THEN 'Warning' \
            ELSE 'UNKNOWN' END as step_status, \
      step_log.nb_row, \
      step_log.nb_ins, \
      step_log.nb_upd, \
      step_log.nb_del, \
      step_log.nb_err, \
     dbms_lob.substr( TRANSLATE ( step_log.error_message, 'x'||CHR(10)||CHR(13), 'x'), 600, 1 )  as error_message \
from snp_session sess right outer join snp_sess_step step on sess.sess_no = step.sess_no \
                      inner join snp_step_log step_log on step.sess_no = step_log.sess_no and step.nno = step_log.nno \
where step_log.sess_no  = <%=odiRef.getSession("SESS_NO")%> \
      order by step_log.nno"
print >> output_write, "StepNo StepName                                StepType            BeginningTime  EndTime        Duration   Status  Error_Msg\n"
print >> output_write, "------ --------------------------------------- ------------------- --------------------- --------------------- ---------- -------------- ----------\n"
result=sqlstring.executeQuery(sqlstmt)
while (result.next()):
    rownum=result.getInt("rownum")
    nno=result.getInt("nno")
    step_name=result.getString("step_name")
    step_type=result.getString("step_type")
    step_beg=result.getString("step_beg")
    step_end=result.getString("step_end")
    step_dur=result.getInt("step_dur")
    step_status=result.getString("step_status")
    error_message=result.getString("error_message")
    print >> output_write,'%7s%40s%20s%22s%22s%11s%15s%s\n' % (str(nno).ljust(7),step_name.ljust(40),step_type.ljust(20),str(step_beg).ljust(22),str(step_end).ljust(22),str(step_dur).ljust(11),step_status.ljust(15),str(error_message))
print >> output_write, "/*------------------------------------------------------------------------------------------------------------------------------------------------*/\n"
sourceConnection.close()
output_write.close() 

The only restriction in this method is that we will not able to get the status of the execution report into the file since query is executed while it is run.

If we don't need this procedure details, we can restrict it in the where clause so that you wont see it in the report.

Sample Examples.

I will try to show a successful run as well as error run samples files here.

1. In this package, I am running an interface and on successful run, it produces the log.

/*------------------------------------------------------------------------------------------------------------------------------------------------*/

                    EXECUTION REPORT FOR SCENARIO : WRITE_EXECUTION_REPORT  ( THU NOV 14 10:34:33 2013 )                    

/*------------------------------------------------------------------------------------------------------------------------------------------------*/

StepNo StepName                                StepType            BeginningTime  EndTime        Duration   Status  Error_Msg

------ --------------------------------------- ------------------- --------------------- --------------------- ---------- -------------- ----------

0      int_file_table                          Interface           14-NOV-2013 10:34:11  14-NOV-2013 10:34:12  1          Success        None

1      write_execution_report                  Procedure           14-NOV-2013 10:34:12  None                  0          UNKNOWN        None

/*------------------------------------------------------------------------------------------------------------------------------------------------*/

2. For a error run a sample report is shown here. In this case, I run the interface, run a PL/SQL block which raises exception to produce error and on error run the execution report.

Lets look at the log for the error.

/*------------------------------------------------------------------------------------------------------------------------------------------------*/

                    EXECUTION REPORT FOR SCENARIO : WRITE_EXECUTION_REPORT  ( THU NOV 14 14:50:17 2013 )                    

/*------------------------------------------------------------------------------------------------------------------------------------------------*/

StepNo StepName                                StepType            BeginningTime  EndTime        Duration   Status  Error_Msg

------ --------------------------------------- ------------------- --------------------- --------------------- ---------- -------------- ----------

0      int_file_table                          Interface           14-NOV-2013 14:50:08  14-NOV-2013 14:50:15  7          Success        None

1      proc_call_stored_procedure              Procedure           14-NOV-2013 14:50:15  14-NOV-2013 14:50:16  1          Error          ODI-1226: Step proc_call_stored_procedure fails after 1 attempt(s).ODI-1232: Procedure proc_call_stored_procedure execution fails.ODI-1228: Task proc_call_stored_procedure (Procedure) fails on the target ORACLE connection oracle_xe_hr.Caused By: java.sql.SQLException: ORA-20001: This is a custom errorORA-06512: at "HR.RAISE_EXCEPTION", line 15ORA-06512: at line 2 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:457) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:889) at oracle.jdbc.driver.T4CTTIfun.receive(T

2      write_execution_report                  Procedure           14-NOV-2013 14:50:16  None                  0          UNKNOWN        None

/*------------------------------------------------------------------------------------------------------------------------------------------------*/