As400

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:

• OS 400 ver 4.5

• Windows 2000 server

• Apache Tomcat/4.1.12-LE-jdk14

• jdk 1.4.1

• Cocoon 2.0.3

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.

• NOTE: You may also find the jt400.jar file on your AS/400. Look in /QIBM/ProdData/HTTP/Public/jt400. The jar you find there will most likely be an older version than the one available from the web site. There are also the DB2 Native drivers that are typically used for local AS/400 connections (i.e. it connected to itself) but they can work. They are located in /QIBM/ProdData/Java400/ext along with some other jars that you may find useful -- DanFeather (didn't properly identify myself at first)

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:

• downloading and installing a JDK 1.4.0 or later from Sun (current successful configurations use version 1.4.1);

• have Tomcat use the upgraded JDK (by changing its $JAVA_HOME);

• have all applications hosted in your Tomcat instance use a 1.4-targeted version of Cocoon.
  Currently, version 2.0.3 has a ready-made, 1.4-targeted binary distribution in the download directory.
  If your applications are Cocoon 2.0.3-based, the following should be enough: 1.# Locate cocoon-2.0.3-vm14-bin.tar.gz or cocoon-2.0.3-vm14-bin.zip in Cocoon's distribution directory and download the one which suits yout platform. 1.# unpack the file in a directory of your choice; 1.# unpack the cocoon.war which has been extracted: it expands to a full webapp directory structure; 1.# locate the WEB-INF/lib directory: it contains the same JARs as your 2.0.3 application's WEB-INF/lib directory, but retargeted at the JDK 1.4; 1.# after making a backup copy :), overwrite your application's Cocoon JARs with these new 1.4 ones; 1.# Restart Tomcat or simply reload your application(s) via the Tomcat Manager.

• If your applications are based on another Cocoon version, you need to manually rebuild Cocoon from the source, targetting at the JDK 1.4, and then start from previous point 4.

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 (?).

1. 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

---