Under the hood of the Sunopsis Memory Engine. Part 2
Posted: February 1st, 2010 | Author: Uli Bethke | Filed under: Oracle Data Integrator (ODI) | Tags: hsqldb, Oracle Data Integrator and hsqldb, Sunopsis Memory Engine | 3 Comments »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.
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.
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.
The next execution brings this up to 123.7 MB
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.
We execute the interface a few more times until we finally hit the memory limit specified in odiparams.bat.
Subsequent executions of the interface will throw an error along the lines of the figure below.
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.
As you can see from the figure below memory is allocated when the table is loaded and then de-allocated when it is dropped
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
Very interesting. This seems to raise the issue that when you use the memory engine in a session, the resources are not released on session completion. As there is no process/ memory tooling inside the agent, it may be easy to get in a mess. I wonder if there are any options like “DropOnDisconnect” that we have with the XML driver that could be used for the memory engine. An area for further reading. Thanks
Thanks for the input Craig. I had a quick look in the HSQLDB documentation and you are right. There is a property you can specify that shuts down the database once the last connection is closed: jdbc:hsqldb:file:/opt/db/testdb;shutdown=true. I will try this out over the next couple of days and monitor memory allocation etc.
[...] 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 [...]