Hello everyone.

As this is a feature that comes from 10g but still present at 11g and pretty useful, we decide to discuss it as “11g part”. You will see that it’s really useful in several situations.

We all have seen the Sunopsis Memory Engine but have never used it or played with it. This post is all about it and using it smartly for various activities, without using target RDBMS for temporary activities.

If you are wondering what actually is Sunopsis Memory Engine , its nothing but the HyperSQL , which is an 100% java database. The Demo environment  runs on the HyperSQL.

[ Note : HyperSQL stores its data in memory ,so once the ODI is closed and restarted the data and the tables are flushed out ,so HyperSQL is great for storing or processing data for a particular session ]

You can find more about it here http://hsqldb.org/ and Documentation at this link – http://hsqldb.org/web/hsqlDocsFrame.html

we believe that Sunopsis Memory engine can be great for creating temporary table or storing data temporarily for ODI logic implementation.

Lets look at various implementation uses and method.

For ODI 10G ,  Technology  – Sunopsis Engine and Schema – SUNOPSIS_MEMORY_ENGINE

For ODI 11G , In-Memory Engine and Schema – SUNOPSIS_MEMORY_ENGINE

create table temp_table ( id INTEGER, name varchar(20))
insert into temp_table values ( 1 ,’abc’)
insert into temp_table values ( 2 ,’abcd’)
insert into temp_table values ( 3 ,’abcdef’)

Calling it in the Variable

Similar to any RDBMS you can call these tables into the Variable too , just select the SCHEMA as SUNOPSIS_MEMORY_ENGINE and write your query.

For example select id from temp_table.

Similar to Oracle , you can read it via Jython or Java and fetch the required information.

If you ever need to read the data from File via jython or Java and write into Sunopsis Memory. Here are the codes.

Jython.

import java.sql as sql
import java.lang as lang
sourceConnection = odiRef.getJDBCConnection("SRC")
sqlstring = sourceConnection.createStatement()
read_file=open('c://table_name.txt','r')
read_line=read_file.readline()
while read_line:
     sqlstring.executeUpdate("INSERT INTO TABLE_NAME VALUES ('"+read_line+"')")
     read_line=read_file.readline()

sourceConnection.close()
read_file.close()

Java

<@
import java.io.*;
import java.sql.*;
sourceConnection = odiRef.getJDBCConnection("SRC") ;
sqlstring = sourceConnection.createStatement();
FileInputStream fstream = new FileInputStream("C:/table_name.txt");
DataInputStream in = new DataInputStream(fstream);
BufferedReader br = new BufferedReader(new InputStreamReader(in));
String strLine;    
while ((strLine = br.readLine()) != null)   {
            sqlstring.executeUpdate ("INSERT INTO table_name values('"+strLine+"')") ;
}    
in.close();
sourceConnection.close();
@>

Temporary Data storage.

Some time we would like to store the data into Temporary purpose for testing or debugging purpose.

We can also reverse engineer the table using the select reverse too.

If you want to play with via  external Sql editor ,you can access the jar file located at  <ODI_HOME>oraclediagentlib   hsqldb.jar  for ODI 11g

Now you can use it as you wish!!!

Please, keep visintig us!