Under the hood of the Sunopsis Memory engine. Part 3.
Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes
This is the final part in our series on the HSQLDB in memory engine. In the first part of this series we’ve had a look at the Sunopsis Memory Engine (SME) from a high level. In the second part we then looked at two use cases namely using Java methods directly in SQL transformations and using the in memory database for simple, low volume transformations in memory. Craig Stewart commented on this last post and mentioned it would be useful to set a JDBC property that would clean up memory after a disconnect, similar to the drop_on_disconnect JDBC property of the ODI XML driver. I had a look in the HSQLDB documentation and came across the SHUTDOWN property. This property shuts down the database after the last session has been disconnected. The problem is that it’s not available yet in version 1.7.3 (the one that ODI uses). As a result we need to clean up objects ourselves or upgrade the HSQLDB to a higher version. If I find some spare time I will blog on how to do this. Also the other day I came across a corrupted HSQLDB. Another subject I may blog about some time soon.
Ok. Let’s have a look at today’s topic. The SME can be very useful to act as a temporary data store, e.g. you can store a Jython variable in the SME, from there assign it to an ODI variable and then pass it on to another scenario. In today’s post I will show you how to achieve this.
We will first create a procedure using Jython technology. In this procedure we will take a Jython variable and store it in a table in the Sunopsis Memory Engine. In this example we grab the OS username of the logged on user and store it in a table named TTEMP in the in memory database. Thanks to Maciej Kocon for the code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
#matshyeq:20100207 import os, re, time import java.sql as sql import java.lang as lang import os currentuser = os.environ['USERNAME'] lang.Class.forName("org.hsqldb.jdbcDriver") url = "jdbc:hsqldb:." myCon = sql.DriverManager.getConnection(url,"sa","") mySQL="create table TTEMP (osuser VARCHAR(30), val TINYINT, ts VARCHAR(30))" try: prepStmt = myCon.prepareStatement(mySQL) prepStmt.execute() except: pass mySQL="INSERT INTO TTEMP VALUES('%s',1,NOW())" % currentuser prepStmt.close() prepStmt = myCon.createStatement() prepStmt.executeQuery(mySQL) prepStmt.close() myCon.close() |
Next we create an ODI variable V_OSUSER that gets its value from TTEMP.OSUSER.
SELECT osuser FROM ttemp
We take this variable and pass it into another scenario.
In this scenario we print the variable to the Operator log to verify that the value of the variable was passed on.
From the screenshot we can see that the OS user uli was printed out, which means that we successfully passed on our Jython variable value to another scenario.
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)
Uli Bethke
Uli has been rocking the data world since 2001. As the Co-founder of Sonra, the data liberation company, he’s on a mission to set data free. Uli doesn’t just talk the talk—he writes the books, leads the communities, and takes the stage as a conference speaker.
Any questions or comments for Uli? Connect with him on LinkedIn.
Follow Uli Bethke: 
