Oracle Data Integrator: ODIInvokeWebService and Java 6

ODI has a built in SOAP client, the ODIInvokeWebService tool. However, there is one caveat when you try using this with Java 6/JDK 6. It simply doesn’t work and you will get the following error:

org.apache.xerces.dom.DocumentImpl.getXmlStandalone()Z

This is documented in note 1085594.1 and the suggested workaround is to fall back to an earlier version of Java. Another option of course is to install another agent that uses Java 5 alongside your Java 6 agent.

The first step we need to take is to install JDK 5. You can download JDK 5 from the Oracle website.

If repositories are running on Oracle we also need to download the compatible Oracle JDBC 5 driver. If you are running your repositories on another RDBMS get the equivalent JDBC driver.

Next, inside the oracledi folder we create a new folder and name it bin_jdk5.

We then copy the content of the bin folder into bin_jdk5.

The next step will be to create a new environment variable and name it ODI_JAVA_HOME2. The variable needs to be pointed to the JDK 5.

As a next step we need to edit the odiparams.bat file in the bin_jdk5 folder. Open the file in Notepad and replace ODI_JAVA_HOME with ODI_JAVA_HOME2

In the same file we also need to replace any occurrence of the word drivers with drivers2

Next we create a new folder drivers2 inside the oracledi folder and copy and paste the downloaded Oracle JDBC 5 driver into it. Alos copy any other drivers from the drivers folder into the drivers folder that you may want to use with this agent

Finally we create a new agent that uses the JDK 5 as a Windows service.

First edit file agentservice.bat and replace bin\odiparams.bat with bin_jdk5\odiparams.bat as per figure below

Then we create a copy of file snpsagent.conf in folder oracldi\tools\wrapper\conf. Replace any occurrence of drivers with drivers2.

We also replace wrapper.working.dir=../../../bin/ with wrapper.working.dir=../../../bin_jdk5/

Next we create the physical and logical agent in Topology Manager.

Finally we install the agent as a listener or scheduler agent.

In one of the next posts I will show how we can make good use of ODIInvokeWebService to query the OBIEE web services API.

Nesting ODI Substitution Methods. Part II.

Today we look at how we can escape quotes when nesting ODI substitution method calls at different levels of the multi pass parser.

As an example we will store the columns of the C$_ datastore and the columns of a target datastore in a two dimensional java array.

,<%=snpRef.getColList("{", "\u0022[COL_NAME]\u0022", ",", "}", "INS")%>};
int k =0;
int l =1;
for (int i = 0; i < ColList.length; i++) {
   for (int j = 0; j < ColList[i].length; j++) {
      out.println(ColList[j][i]);
   }
}
?>

As you can see, we are nesting a call to the ODI substitution API inside a parse block. Normally in Java we escape a quote with \”. In ODI this would throw an error. What we do instead is to use the Unicode representation of the quote character: \u0022

If you want to master scripting in ODI get the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)

Calling custom Java classes and JAR files in ODI via Jython or Java BeanShell

First we create a simple Java class that creates and writes to a text file and save it as FileWrite.java

import java.io.*;

public class FileWrite
{

  public void writeFile()
  {
    FileOutputStream fos;
    DataOutputStream dos;

    try {

      File file= new File("C:\\MyFile.txt");
      fos = new FileOutputStream(file);
      dos=new DataOutputStream(fos);
      dos.writeInt(2333);
      dos.writeChars("Hello World");

    } catch (IOException e) {
      e.printStackTrace();
    }
  }
}

Next we compile the .java class from the command line

c:\javac FileWrite.java

Next we create a .jar file from the class

c:\jar cf FileWrite.jar FileWrite.class

We then copy and paste the .jar archive to the ODI drivers folder

Next we restart the ODI agent.

We can now call methods in this class from either Jython

import FileWrite

fw=FileWrite()
fw.writeFile()

or we can call it from the Java BeanShell

import FileWrite;

FileWrite fw = new FileWrite();
fw.writeFile();

If you want to master scripting in ODI get the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)

ODI and analytic functions (again???)

My friend and colleague Maciej Kocon has come up with the following trick. Rather than use a user defined function to implement an analytic function such as MAX() OVER or SUM() OVER as suggested by note 807527.1 there is another way to work around the getGrpBy bug.

We will take advantage of the ODI multi pass parser and hide the analytic function from getGrpBy in our mappings:

<?out.print(”SUM”);?>(SALES.AMOUNT_SOLD) OVER (PARTITION BY PRODUCTS.PROD_CATEGORY)

The above SUM gets only generated once getGrpBy has been parsed and as a result no GROUP BY clause is generated, which is the correct behaviour.

A brilliant yet simple trick.

Cheers Maciej.

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)

ODI and Integrated Security/Authentication with Microsoft SQL Server

Setting up integrated security with the SQL Server JDBC driver can be tricky. You often end up with the message “This driver is not configured for integrated authentication”. Below are the steps that you need to perform to get this to work.

Note: This will only work where the ODI agent has been installed on a Windows OS.

1. Download the Microsoft SQL Server JDBC Driver 2.0 from the Microsoft website. This is compatible with versions 2000, 2005, and 2008 of MS SQL. You will need to be up and running on JDK 5.0 or later with your ODI installation. If you haven’t done so already now is the time to install JDK 5.0 or even better JDK 6.0.
2. Unzip the content of the download.
3. The driver comes with a separate dll (sqljdbc_auth.dll) that will allow you to connect to SQL Server via your Windows login. Copy the sqljdbc_auth.dll to the \oracledi\drivers folder.
4. Copy the sqljdbc4.jar file to the \oracledi\drivers folder
5. Open odiparams.bat and add the following entry: set ODI_ADDITIONAL_JAVA_OPTIONS=”-Djava.library.path=\oracledi\drivers”, e.g. set ODI_ADDITIONAL_JAVA_OPTIONS=”-Djava.library.path=c:\oracle\oracledi\drivers”

6. If you have set up the ODI agent as a Windows service you need to re-install the agent. Stop the agent and uninstall the agent with the agentservice –r command. Next you need to edit the “snpsagent.conf” file in the \tools\wrapper\conf directory.

7. Re-install the ODI agent with the “agentservice -i” command.
8. Open Topology Manager module and create Physical Server for MS SQL technology. Leave username and password blank

Go to JDBC tab and enter

jdbc:sqlserver://<name_of_server>:<port>;databaseName=<db_name>;integratedSecurity=true;

9. One last thing. Of course, your ODI agent must not run as the Local System account but use a network account that has the appropriate MS SQL privileges to log into the SQL Server.

If you want to master scripting in ODI get the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)

What has ODI encryption got to do with clearing the OBIEE cache???

As part of a data mart refresh I am using nqcmd to clear the cache for the corresponding subject area in OBIEE. Thanks to John Minkjan for his post on properly documenting how the OBIEE cache can be cleared.

Basically I am executing nqcmd -d AnalyticsWeb -u Hans -p Zipfel -s d:\obiee\scripts\Purge_XXX_Cache.txt in an ODI procedure with technology set to Operating System.

I have currently installed an agent on the box for the BI server to launch nqcmd. However, I think that alternatively you can perform a custom install of the BI ODBC driver and the System Management component on the server where your ODI agent is running.

The major problem I have with the above solution is that the password for the Administrator password is sent across as clear text. Any ODI Operator module user would be able to view this. A gaping security hole. My first instinct when looking for a solution was to look for an encryption mechanism for this in OBIEE itself. Unfortunately no such thing is provided (as far as I know).

ODI to the rescue

I was rather frustated and ready to give up when I remembered that ODI has an encryption mechanism of its own.

Below are the steps I took to encrypt the password.

Right click on the procedure and select Encrypt. In the popup select Get a new encryption key

Save the key in a secure location as you will need it should you ever wish to decrypt the procedure (yes it can be easily lost…).

When you now execute the procedure only gobbledygook (what a great word) will show up in the Operator module.

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)

Nesting ODI substitution method calls. Part 1.

I am not getting around doing too much blogging on ODI these days. I have recently started a new project on OBIEE and ODI and it must be two years or so since I have last used OBIEE. I can tell you that there is a lot of catching up to do. Anyway, today’s post is about why I love ODI and one of the features that makes it the best ETL tool on the planet.

First we will have a look at nesting ODI methods at the same parse level. This is fairly straightforward:

What we do here is create a procedure with just one step. For this procedure we create an Option SESSION_PARAM. We use this option to make a call to the getSession method. As value for SESSION_PARAM we could pass in SESS_NO or SESS_NAME.

--<%=odiRef.getSession("" + odiRef.getOption("SESSION_PARAM") + "")%>

We just use double quotes and the plus sign to append the second substitution method call. The first double quote is used to as part of getSession and the second pair is used to allow us make a nested call to the substitution API.

Note: The double minus — is used to comment out the output of our API calls in the Oracle technology as otherwise we would throw an error.

Output of the above step is as follows ( I passed SESS_NO as the value of the option).

Next we look at some more interesting stuff and I’ll show you how you can nest calls to the substitution API at different levels of the ODI multi pass parser.

Recently I’ve had a requirement to create indexes on the fly for temporary interfaces. I was able to solve this by using the UD5 marker and nesting of ODI substitution calls.

We will first modify the IKM SQL Control Append and insert a step at the end of the knowledge module.

<? i=0; ?>

BEGIN

<%=odiRef.getColList("\t", "\tEXECUTE IMMEDIATE 'CREATE INDEX IDX_" + odiRef.getInfo("TARG_NAME") + "_" +

"<? i=i+1;out.print(i);?>" +

" ON " + odiRef.getInfo("TARG_NAME") + "([COL_NAME])" +

"'", ";\n", ";","UD5")%>

END;

In the above step we are nesting calls to the API at two different levels. Calls at the <% %> level are always executed before calls to the API at level <? ?>. As you can see we are using the UD5 marker to filter out columns where the UD5 checkbox has been selected.

Note: Inside the delimiters we are using the Java BeanShell scripting language.

When we use the knowledge module in a temp interface, the result of the first parse pass at runtime is as follows:

<? i=0; ?>
BEGIN
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_<? i=i+1;out.print(i);?> ON SALES_TARGET(PROD_ID)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_<? i=i+1;out.print(i);?> ON SALES_TARGET(CUST_ID)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_<? i=i+1;out.print(i);?> ON SALES_TARGET(TIME_ID)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_<? i=i+1;out.print(i);?> ON SALES_TARGET(CHANNEL_ID)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_<? i=i+1;out.print(i);?> ON SALES_TARGET(PROMO_ID)';
END;

This is then passed on to the next level where at runtime this results in:

BEGIN
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_1 ON SALES_TARGET(PROD_ID)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_2 ON SALES_TARGET(CUST_ID)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_3 ON SALES_TARGET(TIME_ID)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_4 ON SALES_TARGET(CHANNEL_ID)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_5 ON SALES_TARGET(PROMO_ID)';
END;

Eventually this is executed by the Oracle technology as dynamic SQL. You need to be careful when you nest different parse levels with escaping quotes. I will deal with this in the next part of this series.

Once you get your head around this stuff you can literally meet any requirement that is thrown at you. What a great tool.

Don’t you feel the love yourself?

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)

ODI – getSession(”SESS_PARAMS” )

In response to my article on the ODI variable debug procedure Craig Stewart has pointed out an undocumented parameter of the getSession method that does the same with one big limitation.

The parameter SESS_PARAMS will print out any variables that have been passed into a scenario generated from a package. Unfortunately this is the limitation of this method. Variables that you dynamically assign inside the package will not print out to the Operator. The reason for this is the inner workings of the ODI multi-pass parser. Basically, ODI prints out the parsed command text of your procedure, interface etc. to the Operator before it assigns a value to a variable. As a result this value only becomes available at execution time and we need to force a printout by raising an execption.

Anyway, let’s try out Craig’s suggestion. I will adapt the debug procedure from the previous article and include the following command:

Next we generate a scenario from this package and encapsulate this inside a parent package and pass in a value for the v_raise variable

Let’s verify that the value was printed out in the Operator Module.

This is very neat and works because we pass the value into our child scenario and as a result the value of the variable is already available before ODI writes out to the Operator module.

Thanks to Craig for sharing this information with us.

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)

ODI snippets: Query to retrieve source and target columns in an interface

The following query can be used to retrieve the source columns/tables and target columns/tables for interfaces in a specified project

SELECT
   c.table_name AS source_table,
   e.col_name AS source_col,
   f.table_name AS target_table,
   a.col_name AS target_col
FROM
   snp_pop_col a
   LEFT OUTER JOIN snp_src_set b ON (a.I_SRC_SET = b.I_SRC_SET)
   LEFT OUTER JOIN snp_source_tab c ON (b.i_src_set = c.i_src_set)
   LEFT OUTER JOIN snp_txt_crossr d ON (a.i_txt_map = d.i_txt)
   LEFT OUTER JOIN snp_col e ON (d.i_col = e.i_col)
   JOIN snp_pop f ON (a.i_pop = f.i_pop)
WHERE
   a.i_pop IN (
      SELECT
         i_pop
      FROM
         snp_pop
      WHERE i_folder IN (
         SELECT
            i_folder
         FROM
            snp_folder
         WHERE
            i_project = project_id
      )
   )
ORDER BY
   a.i_pop;

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)

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

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

The result can be seen from the Operator module