Connection to As 400

This document explain the specific aspect of connecting Cocoon to a IBM AS 400 (iseries).
For a general explanation on database connection please refer to the official documentation.
The connection has been set up on:

Downloading the jdbc drivers

The open source jdbc drivers are mantained by the jtopen project. They permit many other thing than sql queries or update. They can be downloaded here.
Drop the jt400.jar file in $COCOON_HOME/web-inf/lib.

Setting Web.xml

In the WEB-INF directory you will find the web.xml file. Open it and look for this rows

<init-param>
  <param-name>load-class</param-name> 
    <param-value>
<!-- 
 For IBM WebSphere:
        com.ibm.servlet.classloader.Handler ..-->

here you have to add the following line:

<!--  For as400 Driver:   --> 
  com.ibm.as400.access.AS400JDBCDriver 

Setting cocoon.xconf

search for: <datasources> add into this element theese lines:

  <jdbc name="as400">
    <pool-controller min="5" max="10"/>
    <dburl>jdbc:as400://YourServerName</dburl>
    <user>YourUsername</user>
    <password>YourPassword</password>
  </jdbc>

Setting sitemap.xmap

When you want to use the connection you just call the jdbc name inside the pipeline in this way:

<map:match pattern="as400sqlquery">
  <map:generate src="sql/as400sqlquery.xml"/>
  <map:transform type="sql">
    <map:parameter name="use-connection" value="as400"/>
  </map:transform>
  <map:transform type="xslt" src="../stylesheets/transformation.xslt"/>
  <map:serialize type="svg2jpeg"/>
</map:match>

Obviously you have to adapt this sample to fill your need.
SpecificDatabaseConnection

Comments from the readers


A JDK issue

Connecting with the com.ibm.as400.access.AS400JDBCDriver in the plain standard way described above unfortunately seems to only work with JDK 1.4.0 or later.

If you need all Cocoon SQL functionality

If you need all Cocoon SQL functionality (XSP and SQLTransformer) you probably need to switch to JDK 1.4, which means:

If you don't need/use SQLTransformer and are happy with ESQL

Then maybe you could stick with JDK 1.3 without changing other applications, and retain your current Tomcat setup except for minor changes.

The trick is to not use Cocoon's built-in connection pooling facility (provided by Avalon), but rather use your servlet container's one. Currently, this has been tested on Tomcat, which uses connection pooling services provided by Apache Commons DBCP.

You need to:

  1. Let the com.ibm.as400.access.AS400JDBCDriver be accessible by Tomcat at its startup by copying jt400.jar to $TOMCAT_HOME/common/lib;

  2. Add to Tomcat's server.xml configuration file (located in $TOMCAT_HOME/conf) the desired JDBC connection as a JNDI resource:

    <Resource name="jdbc/as400" auth="Container" type="javax.sql.DataSource"/>
    <ResourceParams name="jdbc/as400">
            <parameter>
              <name>username</name>
              <value><$YOUR_USERNAME></value>
            </parameter>
            <parameter>
              <name>password</name>
              <value><$YOUR_PASSWORD></value>
            </parameter>
            <parameter>
              <name>driverClassName</name>
              <value>com.ibm.as400.access.AS400JDBCDriver</value>
             </parameter>
        <parameter>
          <name>url</name>
          <value>jdbc:as400://<$YOUR_HOST>/<$YOUR_COLLECTION></value>
        </parameter>
    </ResourceParams>

    1.#Please note that you can append a ;trace=true option to the JDBC URL, as well as any other options documented in JTOpen documentation. The trace option obviously kills performance, but allows you to debug connection problems by writing a quite detailed log to System.out, which in Tomcat default configuration gets logged to $TOMCAT_HOME/logs/catalina.out. 1.#Please also note that the snippet above must be inserted inside the relevant <Context/> element for your application. If you didn't define one (just dropped something into the webapps directory), putting it in the <GlobalNamingResources> could work (?).

  3. Add to your application's cocoon.xconf file a reference to the J2EE connection you have just defined:{{{<datasources>

    • ..

      <j2ee name="$CONNECTION_NAME">

      • <dbname>as400</dbname>

      </j2ee>

    • ..

</datasources>}}} Please note that $CONNECTION_NAME is the name you want to use in your Cocoon application.
The <dbname/> string is, instead, the name you chose in server.xml's resource definition.

Note that server.xml's resource name is jdbc/as400, where <dbname/> only contains as400. This is because database connections are usually looked up inside the jdbc/ hierarchy.

This latter JNDI configuration is obviously only a "desperation-workaround", since it has shown problems with SQLTransformer.
Switching to JDK 1.4 should be seen as the best option, since it seems to allow all Cocoon SQL functionality to be used on databases stored on our AS/400 boxes.

A final note

Please note that all the above has been tested on Tomcat 4.1.12.

(Lorenzo)

Thank you Lorenzo. I'll correct my part specifying the versions of jdk and Tomcat I have used. If you want we could divide this page in two: one page for jdk 1.4 and one for the case you described -- Gabridome


As400 (last edited 2009-09-20 23:40:11 by localhost)