Saturday, June 18, 2005

Trying out OWB Java API

I have been working with OWB since a year. And the learning is still to take a back seat. During the initial days with OWB the main attraction was exploring various operators (pivot, match merge, sort, etc.), trying out all the possible things one can do from the mapping editor, configuring various objects from OWB Client, implementing things like SCD, exporting the OLAP metadata for use with BI Beans, process flows etc.

Latter on, the focus shifted to Runtime and Design time browser. Both the browsers were significant features of the OWB. RAB (Runtime Audit Browser) is the great place to see the audit logs, the error messages etc. etc. Design time has two great piece (Impact diagram and Linage diagram), which could be of great help while analyzing the impact of any change in the OWB mappings.

Then came the exposure to the various scripts under /owb/rtp/sql. The scripts were of great help understanding runtime platform and how to manage the RTP service. Apart from that the scripts like sql_exec_template.sql, abort_exec_request.sql and list_requests.sql were really handy in managing the individual runs of mappings.

Then came the time to explore various tables/views in the design time and runtime repositories. Later on I managed to get a chance trying my hands on OMBPlus. And man, it was just too cool working with OMBPlus. OWB Client (gui) is extremely unusable if there is some repetitive task to be done. For example you want to substr all the attributes to 30 characters. And assume that there are 50 such attributes which needs to be sub-string. What does one do? Drop an expression operator. Get all this attributes into the input group. Then one by one add 50 attributes in output group. Then change the expression property of all this 50 attributes to do the substr() of the corresponding input attributes. Now this is heck of work. At OMB side its just one small script which you got to write to do the whole thing. So if there is anything repetitive and tedious OMBPlus is the answer. Even taking backup of the repositories in to MDL is repetitive. One can write a small bat job using OMBPlus to take care of it. Apart from this, there could be many more things which OMBPlus accomplishes for you like creating template mapping, deploying the object, synchronizing the objects, importing the metadata etc.

So, over the period I kept learning all the different pieces of OWB, which I feel is an extensive suite and provides a comprehensive capabilities for any ETL and data integration task. And still I had few more left out. One of this was Java API to manipulate the metadata. Oracle exposed a public Java API to manipulate OWB design and runtime metadata, deploying and running the mapping, etc. Apart from all the capabilities of OMBPlus, Java API has some additional capabilities. In reality OMBPlus in turn uses Java API to do the various manipulation to the metadata.

So today was the day to get my hands dirty with Java API which came along with OWB 10g1 and onwards. The first thing I did was to find out some document and all I was able to manage was http://download-west.oracle.com/docs/html/B12155_01/index.html, the Javadoc for the API. Typically the Javadoc just has the description of all the classes, interfaces, methods etc. The doc is not a step-by-step tutorial on how to write a simple Java program using the OWB Java API.

The API is very big and so is the doc. The above link leads you to a list of 25 odd java packages to do various things. However there is no hint on where to start.

I brought up my Eclipse (www.eclipse.org) workbench and created a simple Java project named OWBApi. There was a bit struggle in locating where the jar file for the Public API resides. I managed to locate it under /owb/lib/int/publicapi.jar. I added this jar to the build path of the OWBApi project by going to menu Project->Properties -> Java Build Path -> Libraries and Add External Jars.

Back to the Java doc for the OWB Java API:
oracle.owb.connection was the first package I hit. Everything has to start with connection first. After bit of scramble here and there I managed to put together following piece of code in ConnectOWB.java


import oracle.owb.connection.OWBConnection;
import oracle.owb.connection.RepositoryManager;

public class ConnectOWB {
public static void main(String[] args) {
try{
oracle.owb.connection.RepositoryManager rm=oracle.owb.connection.RepositoryManager.getInstance();
OWBConnection owbconn = rm.openConnection("dtrep",
"dtrep","localhost:1521:orcl",rm.MULTIPLE_USER_MODE);

if (owbconn != null ){
System.out.println("Connection Establishied..");
}

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


When running the above, I got following error message:

java.lang.NoClassDefFoundError: oracle/wh/repos/impl/foundation/CMPException
at ConnectOWB.main(ConnectOWB.java:23)
Exception in thread "main"

This means the build path is missing some jar which contains oracle/wh/repos/impl/foundation/CMPException. Now how to find this. Tried searching for this error on OWB forum at OTN, etc. but of no help. Finally an idea clicked to add all the jar under /owb/lib/int. Doing so I was able to get rid of the NoClassFound error message but ended up with one more error message saying that
unable to located Compatibility.properties file.

Where to go now? I tried to search this file under OWB home and was able to locate it under /owb/bin/admin. Inorder to make this file avilble to my java program I added one more entry in the build path for this directory. Adding a folder to the build path is as good as adding a Jar but instead of selecting the Add External Jar one has to click Add Class folder. Spicify the directory: /owb/bin/admin.

That’s it. I tried compling and running the program again. And it’s through. I was able to establish the connection to design time rep.

After oracle.owb.connection, the next hit was oracle.owb.project. I manged to do some more things like getting the list of project, creating a project, setting the active project etc. Following program displays the list of Projects in the design time repository.

import oracle.owb.connection.OWBConnection;
import oracle.owb.connection.RepositoryManager;
import oracle.owb.project.*
;

public class ConnectOWB {
public static void main(String[] args) {

try{
oracle.owb.connection.RepositoryManager rm=oracle.owb.connection.RepositoryManager.getInstance();
OWBConnection owbconn = rm.openConnection("dtrep",
"dtrep","localhost:1521:orcl",rm.MULTIPLE_USER_MODE);

if (owbconn != null ){
System.out.println("Connection Establishied..");
}

ProjectManager pmgr = ProjectManager.getInstance();
String[] projlist=pmgr.getProjectNames();
for(int i = 0 ;i<projlist.length;++i)
{
System.out.println(projlist[i]);
}

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


And the list goes on. Java API seems to be a good option to write the striped down interface like OWB for some special set of users who really don’t need the whole OWB client to

Java language is a proven language for writing the GUI application. It has a rich set of libraries for accessing developing user interfaces, network programming, database programming.

One can use this API and end up writing a browser-based interface to manipulate the OWB metadata. Or may be even creating new mappings with some specific templates and stuff. Or atleast writing an interface to run a job, deploy a mapping, change some metadata etc. This really reminds of a new feature called Expert, which is coming along with OWB Paris.


Yeah, so getting back to my learnings in OWB I have some more in list. Next would be exploring the Appendix:C of the user guide. The appendix talks about extracting the data from XML data sources. Next is to use the AQ (Advance Queues) and build up the understaning of pulling the data from Appilcations (SAP). So still long way to go.


10 comments:

raj kumar said...

Hi Umesh,

i gone thru ur Blog. i have a small question for u.u said sql_exec_template.sql is real handy for running OWB mapping. can u send a clear information to me at syraj_2k@yahoo.com.

-Thankq
raj kumar

subbu said...

Hi Umesh,

I want to use public Java API to manipulate OWB design and runtime metadata, deploying and running the mapping.

I am unable find which jar i need to download.
Pls tell me the jar file.

Thanks
Subhashini

subbu said...

Hi Umesh,

Can u provide me the java code for putting data into database from a flat file.

Thanks,
Subhashini

Umesh Kakkad said...

Shubu,
I don't have the code handy.. But the sample I have posted in the blog does get you started with connecting the repos and another one also tells you how to get a list of projects. As far as the Jar files goes you can locate them [owb_home]/owb/lib/int/publicapi.jar. Infact add all the jar file in that folder into your class path. Also add [owb_home]/owb/bin/admin directory in your class path.

You can't download the API from OTN. You got to download the OBW and set it up to retrieve the jar files from the said location.

Btw, are you running the java project on the same box where OBW components are installed?

john.heaton said...

Hi Umesh

Thanks for the information, very informative. I am trying to include the JAVA API's into a program that I am writing. However coming across the following issue:
java.lang.NoClassDefFoundError: com/objectspace/jgl/HashMap

A couple of questions
1. Do you know if the API will be supported from OWB 10.2 onwards as the last docs I can find are 10.1

2. Have you come across the above issue, if so any ideas.

Thanks
john

sindhoora said...

Hi Umesh,
Im working with OWB 10gR2.Can you please tell me how to connect to the design repository browser and runtime audit browser.When I am connecting to the repository browser in OWB,Im able to view only the control centre reports.

nirabhra said...

Hi Umesh,

I want to extract metadata from OWB through OWB JAVA API. can you please provide me some code else a guideline regarding that?

Thanks
Nirabhra

hajlaoui said...

I followed the steps in your existing blog to connect the environment java JBuilder 10 to Oracle Warehouse Builder repository. So I have a class according to my connection settings.

But when I run this class, the following message appears and the connection is not established.
Message :
Initializing the backend controllers.
Controller Initializer : oracle.wh.repos.pdl.APIController
JVM LIMIT DISABLED : 256 M
Java.lang.NoClassDefFoundError: com/objectspace/jgl/HashMap
at (list of classes of OWB's JAVA API.
Plese, can you help me and send me the answer to my e-mail address:
h.jaleleddine@yahoo.fr

Babar said...

I started with the above code and went on writing ant tasks but eventually realized that even though it is published API but it cannot work out of the box. Very very disappointing. The code in the API makes hard coded references to the OWB installation and uses hard coded relative paths that only work when you run from $OWB_HOME/owb/bin/unix or any directory 2 level down from $OWB_HOME/owb. It appears the API also has lot of code writing directly to standard err and out. You may not get these errors on just connecting. I started to get errors when I attempted to import an MDL file. Following are examples are errors I got

CNV0002-0038(ERROR): Cannot write to file ./../../reposasst/converter.properties

CNV0002-0031(ERROR): Cannot find specified file ..\..\mdl\MDLImportRules.txt

In a nutshell the library is not usable.

anupam said...

Hi I need to check whether thee deployed package defination is sync with OWB Maps how to check it
Anupam