Tomcat jdbc connection pool configuration - DB2 on iSeries(AS400)

This page describes the process to get Tomcat 6.0 configured with a IBM DB2 Data source on iSeries(AS400) using j400.jar JDBC driver. You can follow the same steps for other databases connecting to any version of Tomcat, except for the fact that you need the specific JDBC driver for that database say MYSQL, SQL Server, Oracle, PostgreSQL, Sybase, etc.

Step 1: Download the JDBC driver jt400.jar

You can extract a copy from the iSeries IFS directory: /QIBM/ProdData/HTTP/Public/jt400/lib/jt400.jar

Step 2: Drop the jt400.jar in the right place

Directly into the Tomcat jar library {catalina_home}/lib/jt400.jar

-- OR --

If you are using eclipse for you project the in the project WebContent/WEB-INF/lib as shown in the picture.

Step 3: Create the context.xml file in the project WebContent/META-INF

Add the following code to the context.xml file

<?xml version="1.0" encoding="UTF-8"?>
<Context reloadable="true">
<Resource auth="Container" 
name="jdbc/mydb2"
type="javax.sql.DataSource" 
driverClassName="com.ibm.as400.access.AS400JDBCDriver"
url="jdbc:as400://{hostname or IP Address};naming=system;errors=full;"
username="{user_id}" 
password="{password}"
maxIdle="10"
maxActive="200"
maxWait="30000"
removeAbandoned="true"
removeAbandonedTimeout="1200"
/> 
</Context>

Step 4: Update the web.xml file in the project WebContent/WEB-INF

Add the follwing code as shown in bold between the <web-app> xml tags

<web-app 
...
...
<resource-ref>
<description>DB2 Datasource</description>
<res-ref-name>jdbc/mydb2</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>
</web-app>

Step 5: Java sample code using the datasource resource using JNDI lookup

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

try {
 Context ctx = (Context) new InitialContext().lookup("java:comp/env");
 conn = ((DataSource) ctx.lookup("jdbc/mydb2")).getConnection();

 //SQL data fetch using the connection


 conn.close();
 conn = null;

} 
catch(Exception e){System.out.println(e);}

finally {
 
 if (stmt != null) {
  try {
   stmt.close();
  } catch (SQLException sqlex) {
   // ignore -- as we can't do anything about it here
  }

  stmt = null;
 }

 if (conn != null) {
  try {
   conn.close();
  } catch (SQLException sqlex) {
   // ignore -- as we can't do anything about it here
  }

  conn = null;
 }
}

All done now !

Recommended Reading

• Install Tomcat 6.0 on iSeries