Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes

In the first part of this series we’ve had a high level look at the ODI HSQLDB in memory databasase aka the Sunopsis Memory Engine (SME).

As promised we’ll today have a look at one of the use cases for the SME, in-memory low data volume transformations. This could be particularly useful for temp (yellow) interfaces where you don’t want to physically set down your data.

Before we do this, however, I wanted to introduce another use case for the SME that came to my mind the other day. As HSQLDB allows you to use Java methods as function calls in SQL transformations you could make use of this in ODI interfaces to extend functionality not readily available in your target RDBMS. Data encryption and other security related use cases, e.g. generating MD5 hashes come to my mind.

Below is a screenshot of how this can look like in ODI. You need to make sure to either execute the call to the Java method on the Staging Area, or if you don’t reference a source column on the Target.

sme1

For more information on how to achieve this refer to the HSQLDB documentation. I believe it is possible to use  your own Java methods this way. You need to compile them in class/jar files and reference them in the odiparams.bat classpath. I haven’t tried this out myself but I believe that’s the way it should work.

Let’s move on to our other use case now though. Below I have created a simple temp interface that uses the Oracle sh.sales table to load data into a table in the Sunopsis Memory Engine.

sme2

We then execute this interface a couple of times.

On first execution the memory allocated to the ODI agent JVM jumps to 81.6 MB.

sme3

The next execution brings this up to 123.7 MB

sme4

Yet another execution brings memory usage to 178.6 MB. From the Private Bytes Memory graph you can see that the memory allocation grows linearly as we insert into the s_sales table in the SME.

sme5

We execute the interface a few more times until we finally hit the memory limit specified in odiparams.bat.

sme7

Subsequent executions of the interface will throw an error along the lines of the figure below.

sme6

As you can see we need to clean up after ourselves when using the in memory engine. A simple DROP TABLE in a procedure will drop the in memory table and release memory.

sme9

As you can see from the figure below memory is allocated when the table is loaded and then de-allocated when it is dropped

sme8

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)