Pentaho on Oracle's App Server (OC4J)

Wow! It has really been 4 months since my last post?? Moving over to development has cut into the time I had for blogging, documenting, communicating, you name it! We are coding like crazy.

Well, I'm back because I am heading out to ODTUG Kaleidoscope next week, and in preparation for the show, I decided to setup Pentaho on Oracle's Java Edition App Server, which is OC4J, which is based on the Orion app server. I was pleased that I managed the migration in less than a day, and I wanted to share the steps with all those folks who are too impatient to wait for this to get into our J2EE deployment distribution :)

Mind you, it takes a bit of tweaking, but it is certainly very do-able, and all server features are stable (minus the portal stuff, I didn't get a chance to address moving the portal over). Here is the repro of where I started, what I tweaked and what I came out with. Enjoy!

Where I Started

I started out by downloading a Pentaho J2EE deployment distribution from the Pentaho downloads site. The version I used for this exercise was pentaho_j2ee_deployments-1.5.4.716-0.zip. This distribution is found on our downloads page under Pentaho Open BI Suite Web Archive (.war). I know, the name implies a .war distribution, but trust me, its the deployments zip file.

Unpack this distribution to a work directory of your choosing. This distribution has an Ant build script that lets you build several different .war files and .ear files configured for different app servers and RDBMSs. Next, I'll detail the tweaks I had to make to get the Orion build target working, which is sufficient to buid an .ear file appropriate for Oracle's OC4J.

I also downloaded the sample solutions distribution and the sample data (Hypersonic) distribution, so I would have stuff to test against. You can get both of these distributions from the Pentaho downloads site as well.

What I Tweaked

About two years ago, we had started to incorporate build scripts for the Orion application server in anticipation of great community demand for this build. However, with the multitude of projects we have taken on, and a surprising lack of banging from the community, we never found time nor priority to finish that build, until now.

I really want to be able to demo our stuff on Oracle at the user conference, so I took on the task of cleaning up and repairing the Orion build. NOTE that you will be able to get these fixes in a near future build, as soon as we get our M4 release out the door and I can check this stuff in.

The Build

For the brave and impatient, here's what it needs out of the gate:

1. We'll call the root of your work directory [pentaho_j2ee_deployments]. In this directory, you will find a build.xml file. Open that file in your favorite text editor.
   
2. Find the ant target named "build-orion2.0.5-ear". Delete that target entirely. Replace the target with the following XML:
   
   <!-- ===================================================================
   target: build-orion2.0.5-ear
   =================================================================== -->
   <target name="build-orion2.0.5-ear" depends="zip-pentaho-style-war">
   
   <antcall target="war-pentaho-tomcat">
   <param name="rdbms" value="hsqldb" />
   </antcall>
   <mkdir dir="${build.ears.dir}/orion" />
   
   <ear destfile="${build.ears.dir}/orion/pentaho.ear"
   appxml="${ear.dir}/application.xml">
   <manifest>
   <attribute name="Implementation-Title"
   value="${impl.title}" />
   <attribute name="Implementation-Version"
   value="${impl.version}" />
   <attribute name="Implementation-Vendor"
   value="${impl.vendor}" />
   </manifest>
   <fileset dir="${build.wars.dir}">
   <include name="pentaho-style.war" />
   <include name="sw-style.war" />
   </fileset>
   <fileset dir="${build.wars.dir}/tomcat/hsqldb"
   includes="pentaho.war" />
   <metainf dir="pentaho-res/orion" includes="*.xml" />
   </ear>
   
   </target>
   
3. Save and close the build.xml file.
4. In the same directory, open the override.properties file. Add the following line to this file:
   ear.dir=pentaho-res/ear
5. Save and close the override.properties file.

Those few steps fix up the build files so that you can build a pentaho.ear for OC4J. You of course will have to have Ant in your system path, and a JDK available of at least 1.4.2. I will assume if you are climbing this mountain, those are easy steps you already know how to set up:)

Don't run the build yet! There are several configuration file tweaks that have to be added in order for this app to be configured properly.

The web.xml File

We need to make a few minor changes in the Pentaho web.xml.

First, the OC4J container's default application is running on port 8888. Pentaho's default port is set to 8080. So I changed Pentaho's default port to be 8888, since that seemed to be the easiest road. NOTE that you want to add your changes to two web.xml files, since it is duplicated in the deployer distribution.

You need to modify BOTH

- [pentaho_j2ee_deployments]/pentaho-precompiled-jsps/pentaho.war/WEB-INF/web.xml

AND

- [pentaho_j2ee_deployments]pentaho-webapp/WEB-INF/web.xml !!

1. Open both web.xml files.
2. Find the base-url param-name, with the value http://localhost:8080/pentaho/. Change the value to http://localhost:8888/pentaho/.

We also need to make sure the server can find the Pentaho solutions directory. If you haven't yet, unpack the solutions distribution that you downloaded to a work directory of your choice.

1. Find the param-name pentaho-solutions, and replace the value with the absolute path to the pentaho-solutions directory that you just unpacked. The value should look something like d:\work\pentaho-solutions.
2. Save and close both web.xml files.

The application.xml File

The application.xml file lives in the [pentaho_j2ee_deployments]/pentaho-res/ear directory.

1. Open the application.xml file.
2. Delete all modules under the comment <!-- additional web apps --> , as well as the web-uri module for the sw-style.war above the comment. The only modules your application.xml should have left is the pentaho.war and the pentaho-style.war.
3. Save and close the application.xml file.

Note that the sw-style.war provides the styles for the Steel Wheels samples in the solutions, but I did this quick and dirty, so I left out as much extras as possible. You may want to include the sw-styles.war, and see if it works. I left it out.

The orion-web.xml File

The orion-web.xml file is non-existent, you need to create one.

1. Add an orion-web.xml file to [pentaho_j2ee_deployments]/pentaho-webapp/WEB-INF.
2. Add the following XML to the file:
   <orion-web-app>
   <resource-ref-mapping location="jdbc/HibernateDS" name="jdbc/Hibernate"/>
   <resource-ref-mapping location="jdbc/SampleDataDS" name="jdbc/SampleData"/>
   <resource-ref-mapping location="jdbc/QuartzDS" name="jdbc/Quartz"/>
   <resource-ref-mapping location="jdbc/SharkDS" name="jdbc/Shark"/>
   <resource-ref-mapping location="jdbc/SampleDataAdminDS" name="jdbc/SampleDataAdmin"/>
   <resource-ref-mapping location="jdbc/SampleDataDS" name="jdbc/datasource1"/>
   <resource-ref-mapping location="jdbc/SampleDataDS" name="jdbc/datasource2"/>
   <resource-ref-mapping location="jdbc/SampleDataDS" name="jdbc/datasource3"/>
   <resource-ref-mapping location="jdbc/SampleDataDS" name="jdbc/datasource4"/>
   <resource-ref-mapping location="jdbc/SampleDataDS" name="jdbc/solution1"/>
   <resource-ref-mapping location="jdbc/SampleDataDS" name="jdbc/solution2"/>
   <resource-ref-mapping location="jdbc/SampleDataDS" name="jdbc/solution3"/>
   <resource-ref-mapping location="jdbc/SampleDataDS" name="jdbc/solution4"/>
   </orion-web-app>
3. Save and close the orion-web.xml.

Build the pentaho.ear File

You've got everything you need now to build the pentaho.ear file. Go to a command prompt, navigate to your [pentaho_j2ee_deployments] directory, and execute:

ant build-orion2.0.5-ear

OC4J Server Configuration Changes

Now that you have your .ear file, you will obviously want to deploy it through OC4J Server Console. First, shut down your OC4J instance, because we have a few mods that we need to make to the server configuration.

TopLink Conflicts

Now, I know this is not nice, but since we have no use for Toplink with Pentaho, and the antlr.jar conflicts with our hibernate3 library, you will need to delete the Toplink directory under [OC4J_Home]. This causes no harm as long as you're not using Toplink for some other reason. If so, then maybe you can investigate further with Oracle how to avoid the library conflicts.

This will cause the java_sso webapp in the default module deployed in the server to fail. I simply undeployed the app.

Out of Memory Errors

Pentaho is a big engine, and as such uses a bit of memory. I found that I was running out of memry early and often. So I modified the JVM_ARGS parameter in the [OC4J_Home]/bin/oc4j.cmd file to include:

set JVMARGS=%OC4J_JVM_ARGS% -Xms128m -Xmx512m -XX:MaxPermSize=128m

Add the hsqldb.jar to the [OC4J_Home/lib] Directory

Since we will be testing against the Hypersonic sample data, we need to add the JDBC driver for Hypersonic to the [OC4J_Home/lib] directory. You can find the hsqldb.jar in [pentaho_j2ee_deployments]/pentaho-third-party.

Note that when you deploy the .ear file, you MUST set an additional entry on the classloader's classpath for this jar!

Mod the data-sources.xml File

And finally, we need to add our datasources to the [OC4J_Home]/j2ee/home/config/data-sources.xml file. Add the following xml to the file, before the <datasources/> end tag:

<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="Hibernate"
location="jdbc/HibernateDS"
xa-location="jdbc/xa/HibernateDS"
ejb-location="jdbc/Hibernate"
connection-driver="org.hsqldb.jdbcDriver"
username="hibuser"
password="password"
url="jdbc:hsqldb:hsql://localhost/hibernate"
inactivity-timeout="30"
/>
<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="SampleData"
location="jdbc/SampleDataDS"
xa-location="jdbc/xa/SampleDataDS"
ejb-location="jdbc/SampleData"
connection-driver="org.hsqldb.jdbcDriver"
username="pentaho_user"
password="password"
url="jdbc:hsqldb:hsql://localhost/sampledata"
inactivity-timeout="30"
/>
<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="SampleDataAdmin"
location="jdbc/SampleDataAdminDS"
xa-location="jdbc/xa/SampleDataAdminDS"
ejb-location="jdbc/SampleDataAdmin"
connection-driver="org.hsqldb.jdbcDriver"
username="pentaho_admin"
password="password"
url="jdbc:hsqldb:hsql://localhost/sampledata"
inactivity-timeout="30"
/>
<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="Quartz"
location="jdbc/QuartzDS"
xa-location="jdbc/xa/QuartzDS"
ejb-location="jdbc/Quartz"
connection-driver="org.hsqldb.jdbcDriver"
username="pentaho_user"
password="password"
url="jdbc:hsqldb:hsql://localhost/quartz"
inactivity-timeout="30"
/>
<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="Shark"
location="jdbc/SharkDS"
xa-location="jdbc/xa/SharkDS"
ejb-location="jdbc/Shark"
connection-driver="org.hsqldb.jdbcDriver"
username="sa"
password=""
url="jdbc:hsqldb:hsql://localhost/shark"
inactivity-timeout="30"
/>

Problem in the Solutions' XSLTs

I don't get this at all, but Oracle has some sort of built-in XSLT processor that complains when your XSLT tries to reference a Java class as the value for a namespace. So what you need to do is go through the [pentaho-solutions]/system/custom/xsl directory, and check every xsl for this occurrence (I would say at least 20 of them have it) and make the following mod to those XSLTs:

1. Open the xslt file.
2. Find the java class reference in the namespace declaration. They are always at the top of the file, and look simialar to xmlns:msg="org.pentaho.messages.Messages" . Note that xmlns:msg isn't the only possibility, just an example for you to follow.
3. Prepend the java classname value with http://www.oracle.com/XSL/Transform/java/. So the fixed example would be xmlns:msg="http://www.oracle.com/XSL/Transform/java/org.pentaho.messages.Messages" .
4. Repeat the steps above for every xslt file in that directory that has the namespace occurence.

There is one syntax problem in one of the xslt's as well. JBoss and Xalan don't seem to care about it, but OC4J will error out, so we need to fix that.

1. Open the file [pentaho-solutions]/system/custom/xsl/files-list.xml.
2. Search for the following lines of XML, and delete them. Note they are not right next to each other.
   <xsl:param name="target" />
   <xsl:with-param name="target" select="$target"/>
   

Deploying the Pentaho .ear File

Finally!! You can deploy your .ear file now. Start your OC4J server back up, and use the Server Console to deploy. Here are a few notes on the deployment options:

1. You do not need to set up a security provider in the deployer wizard. Pentaho uses the standard J2EE security via ACEGI, and no custmo extensions to the configuration are necessary.
2. Make sure that you map a path on the classloader to the hsqldb.jar that you moved into OC4J's lib directory!
3. You may see some log4j exceptions on start-up, but as long as you see a statement toward the end of the console log that says "Pentaho BI Platform ready", then you should be ready to go!

Navigate in your browser to http://localhost:8888/pentaho/Home, and test it out!

What I've Got - Pentaho on OC4J!

So, now I'm stoked because I can talk intelligently to Oracle tools users about Pentaho on Oracle. I think I might even dive in and use Kettle to move our sample data over to Oracle Express.

Very soon all this should be doc'ed and available through our SVN repository... hopefully any early comers who use this tip will let meknow how it goes for them:)

And please, if you will be at the conference in Daytona next week, stop by the Pentaho booth and say hey! Can't wait to meet you all!

Gretchie