ODI Variables and the Operator Module

ODI Variables and the Operator Module

One of the annoying things in ODI is that you can’t print out the value of an ODI variable to the Operator module without a workaround. In this workaround you throw an exception in Jython or the Java BeanShell and pass the ODI variable as an argument to the exception method,e.g. in Jython you can use raise(‘#name_of_project.name_odi_var’) to achieve this.

What is quite cumbersome, however, is that you need to manually put this piece of code into each package for each variable you want to debug.

I have written a generic procedure that will make this easier. This procedure uses the workaround described above to print out each variable in a package to the Operator module.

We use a mix of JBS and Jython to achieve this. In JBS we connect to the ODI work repository and retrieve the names of the variables used in the package in question. We then pass these values to Jython and raise the exception.

The source code for the procedure is as follows. You need to set the technology to Jython.

<% 
import java.sql.*;
import java.io.*;
BufferedWriter bw = new BufferedWriter(new FileWriter("d:\log.txt"));
StringBuilder strB = new StringBuilder();
Class.forName("oracle.jdbc.driver.OracleDriver"); 
String url = "jdbc:oracle:thin:@bethke:1521:orcl"; 
Connection con = DriverManager.getConnection(url,"odi_dev","odi_dev");
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
bw.write("raise('");
out.print("raise('");
String str = "SELECT c.var_name,d.project_name  FROM snp_package a JOIN snp_step b On (a.i_package = b.i_package) JOIN snp_var c ON (b.i_var = c.i_var) JOIN snp_project d ON (c.i_project = d.i_project) WHERE  UPPER(pack_name) = UPPER('" +odiRef.getOption("PCK_NAME")+"')";
ResultSet rs = stmt.executeQuery(str);
      while(rs.next()){
         String var_name = rs.getString("var_name");
         String project_name = rs.getString("project_name");
         out.print(var_name+" #" +project_name+"."+var_name+"\n ");
         bw.write("#STUFF."+var_name);
      }
bw.write("')");
out.print("')");
rs.close();
bw.close();
stmt.close();
con.close();
%> 

On lines 6-9 we connect to the ODI work repository
On line 11 we print the start of raise method to Jython
The SQL on line 12 will get us the variable and project name for the variables used in our package. Note that we need to pass in the name of the package as an Option to the procedure. So far I have not been able to find a way how to get this at runtime.
On lines 13-19 we iterate over the resultset of the above SQL and pass the name of the variable and package to Jython
Finally we do some clean up work

Let’s see the procedure in action.

First we assign values to two variables
odi_var1

As a last step in the package we execute the ODI debug variable procedure

The result can be seen from the Operator module

odi_var2

odi_var3


Related posts

5 Comments

  • admin on Aug 09, 2011

    Did you see my other post on this: http://sonra.io/?p=790

    I hear that there will be proper debugging for variables in the next release :-(

    Cheers
    Uli

  • Mike Laughton on Aug 09, 2011

    Hi Uli,

    Thanks for your response. I haven’t found anything about a SESS_PARAMS bug in v11 on the Oracle site, but I did come across the Jython ‘raise’ workaround there:

    a = ‘Table = ‘+ ‘#YOURVARIABLE’
    raise(a)

    It’s an ugly solution, but it at least gives me the bare minimum I need for debugging. Thanks for your help.

    Mike

  • admin on Aug 09, 2011

    Mike, thanks for the comment. I have seen this working in ODI 10. There are a lot of smaller bugs in ODI 11 around the Substitution API. Did you check the Oracle support site for this particular issue.

    Regards
    Uli

  • Mike Laughton on Aug 09, 2011

    Craig,

    Regarding the SESS_PARAMS approach, I’ve tried this using ODI 11g and it only prints the final parameter passed in the XML (calling through web service). Can you confirm that you’ve seen it print more than one variable using this technique (and if so, which ODI version?).

    Thanks!
    Mike

  • Craig Stewart on Mar 17, 2010

    Uli
    Another great article – another snippet to follow-up. If the variable has been passed into the execution as a parameter, then if you use odiRef.getSession(“SESS_PARAMS” ) it will printout the values of the parameters. To make this readable in the execution, I usually put in a procedure with a Java Beanshell step, with the code:
    /* */
    As it is in /* */ comments, it is ignored, and does not look like an error/kludge, but prints out neatly in the log.
    Shame there is no similar thing for the variables set dynamically!
    Craig