OS/400 Edition
Volume 2, Number 49 -- June 26, 2002

Connection Pooling with Tomcat

Hey, David:

First of all, let me thank you for a very good article in the February 14 issue of Midrange Guru, OS/400 Edition (see "Installing and Configuring Tomcat on iSeries"). Your article helped me to understand quite a few things.

I would like to ask you a question about the following statement you made: "Tomcat does provide a mechanism to pool connections…." Could you please explain what that mechanism is and how it could be accessed?

At my workplace, we installed Tomcat and are trying to begin development. We have to deal with AS/400 databases, and connection pooling remains a very unclear point, yet that was one of the major selling points to get Tomcat in our shop in the first place. Help us, please.

-- Leo

I am glad to hear that the Tomcat article was helpful. Connection pooling in Tomcat is not that difficult once you find the documentation and have an example. I have used Tomcat for a number of projects and so far it has been easy to set up, stable, and fast. You do not mention which version of Tomcat you are using, but I would recommend that you use the latest version (4.0.4 today) and run Tomcat standalone.

Your first question asks what mechanism Tomcat uses for connection pooling. That mechanism is the open source Tyrex package. Among other things, the Tyrex package provides a full-featured JDBC pool implementation. At this point, it appears that Tomcat 4.1 will be switching to the Jakarta commons DBCP package.

The Tomcat JNDI Resources HOW-TO gives an overview of Tomcat's connection pool support. That page describes the steps you must follow to set up Tomcat's connection pooling. One thing missing from that page is an iSeries example and step-by-step instructions.

The first thing you need to do is to install Tomcat. Next, I would download the latest version of JTOpen and install the JAR files; today that is JTOpen 3.1 (which requires a user name and password). You might also want to review the Midrange Guru article, "Avoiding a Sign-On with Java ."

After you have Tomcat, Java, and JTOpen set up, you are ready to set up your connection pool. Start by configuring a JNDI resource by adding the following lines to Tomcat's server.xml file, as in this example:

<Context path="" docBase="/appdir" debug="0"/>
  <Resource name="jdbc/db2"
            auth="Container"
            type="javax.sql.DataSource"/>
  <ResourceParams name="jdbc/db2">
                                 
    <parameter>
      
      <name>driverClassName</name>

      <value>com.ibm.db2.jdbc.app.DB2Driver</value>

    </parameter>
           
    <parameter>
            
      <name>driverName</name>

      <value>jdbc:db2://localhost;naming=system;
           libraries=,lib1,lib2;translate binary=true</value>
    </parameter>
           
  </ResourceParams>
        
</Context>

In that example, I assigned the name jdbc/db2. You can use any name you would like and can create different data sources for different drivers and JDBC URLs. The JDBC URL I used in that example specifies the native driver. If you want to use the toolbox driver (for testing from your PC for example), you would change the URL to something like the following example:

jdbc:as400://yourhost.com;naming=system;libraries=,lib1,lib2

In the next step, declare the JNDI name in your web.xml application deployment descriptor. The web.xml file is in the WEB-INF directory of your application (where the Context docBase in server.xml points). That declaration should look something like the following example:

<resource-ref>
  <description>
    Resource reference to java.sql.Connection
    factory defined in server.xml
  </description>
  <res-ref-name>jdbc/db2</res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
</resource-ref>

Finally, you will need to build retrieve connections from the pool. You can do this in each class that needs a connection, but a better alternative is to set up a class that returns connections. I use a static method to retrieve pool entries. Here is an example:

package com.yourdomain.db;

import com.yourdomain.util.ApplicationError;
import java.sql.Connection;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

/**
 * Class Pool Returns a connection from a JNDI data source.
 * 
 * @author David Morris
 */
public class Pool {

    /**
     * Method Pool create a new connection pool
     */
    public Pool() {
        System.err.println("DBUtil instance created.");
    }

    /**
     * Method getConnection.
     * @return Connection New connection from the pool
     */
    public static Connection getConnection() {

        Connection conn = null;

        try {
            Context ctx = (Context) new InitialContext().
                 lookup("java:comp/env");
            conn = ((DataSource) ctx.lookup("jdbc/db2")).
                 getConnection();
        }
        catch (Exception e) {
            e.printStackTrace(System.err);
            throw new ApplicationError(e);
        }

        return conn;
    }
}

You use the Pool class to retrieve connections in your applications using something like this example:

Connection conn = Pool.getConnection();
try {
    PreparedStatement ps = 
        conn.prepareStatement("select * from file");
    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
        ...
    }
}
catch (SQLException e) {
    throw new ApplicationError(e);
}
finally {
    try {
        conn.close();
    }
    catch (SQLException e2) {
        throw new ApplicationError(e2);
    }
}

Hopefully this will get you started.

-- David