By default, DB2 ODBC connection pooling is on with timeout = 60 sec. That is, a connection will be removed from the pool if the connection is not used for 60 sec. There is no max/min bound for ODBC connection pool; it is unbounded. OLEDB resource pooling for MSDASQL is also on. That means that connection could be pooled on both at OLEDB level and at ODBC level. The behavior of OLEDB resource pool is different to ODBC. It does not have max/min bound either. However, OLEDB will drop the entire pool when there are no active connections. In order to keep the pool, Microsoft suggests having code to keep one connection open (see http://msdn2.microsoft.com/en-us/library/ms810829.aspx).

IBM suggests that we disable OLEDB resource pooling for MSDASQL (see http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0010958.htm) so that it does not interfere with ODBC resource pooling. By default, both OLEDB resource pooling and ODBC connection pooling on.

The following steps are only required for the first time deployment to the server:

1. Configure Environment Variables for DB2 Universal JDBC Drivers. From Admin Console, client Environment/WebSphere Variables. Make sure that the correct node is selected. Configure the following environment variables:

DB2UNIVERSAL_JDBC_DRIVER_PATH

UNIVERSAL_JDBC_DRIVER_PATH

2. Configure JDBC Providers: Click Resources/JDBC Providers. Make sure that the correct node is selected. If DB2 Universal JDBC Driver Provider is not already created, click New. From database type, select DB2. From provider type, select DB2 Universal JDBC Driver Provider. From implementation type, select “Connection Pool Datasource”. Click Next. Accept the default for Class Path and Native Library path. If the class name is not populated, enter “com.ibm.db2.jcc.DB2ConnectionPoolDataSource”.
3. Create J2C Authentication data: Click Security/Global security. Expand JAAS Configuration under Authentication on the right-hand-side. Click J2C Authentication data. Click new to create a new entry and set the follow parameters:

Alias:

Description:

User ID:

Password :

4. Create Data sources: From DB2 Universal JDBC Driver Provider screen, click “Data sources” under “Additional Properties”. Click New to create a new Datasource:

Name:

JNDI Name:

Component-managed authentication alias:

Fill the DB2 Universal data source properties with the information from prerequisite 3. Accept default for the rest of parameters.

Save the configuration. Click test connection to ensure that the DB2 connection is successful.

5. Deploy the enterprise application: Click Applications/Enterprise Applications. Click Install. Browse to the ear file and leave context root empty. Click Next. On override, select “Do not override existing bindings”. On Virtual Host, select “Use default virtual host name for web modules”. Enter the host name from prerequisite 1. Click Next. From installation options, do not pre-compile JSP. Select Distribute Application. Accept the default for the rest and click Next.
6. Map resource reference to Resources: The web service contains resource reference jdbc/Q2WSDatasourceReference. Clip MapResource reference to resources. Select the module, select the resource JNDI name from step 4, select user default method and select alias in step 2 in the authentication data entry.

My web project connects to a database. WebSphere server can provide pooled connection as datasource. In my application, I just simply get the connection by lookup through the context:

   InitialContext ctx = new InitialContext();
   DataSource ds = (DataSource)ctx.lookup(dataSourceUri);
   con = ds.getConnection();

Using this way, I can let container manages the connection and security. I just simply retrieve the connection from my code. Direct resource lookup is deprecated in WebSphere 6 for security reasons. Now we have to look up the datasource through resouce reference (i.e., indirect lookup). Application will specify the resource reference that the application will lookup in the deployment descriptor. The container can link the resource reference to resource by confiugration. This way, a data source is not exposed to any application.

We have to let application tell which resource reference it is going to lookup first. To do this, cllick the Deployment Descriptor in the web project and click the References tab. Add a new resource reference. For data resources, set type to javax.sql.DataSource. Set authentication to container. To make it easier to work with local testing environment, we can set the JNDI name tha the resource reference should bind to under WebSphere Bidings. Set JNDI Login Configuration to None.

In the next part, we will specify how to configure the resouce reference in the admin console.

I created a dynamdic web project using Rational Application Developer (RAD) 6.0. The wizard automatically created an EAR project. The EAR project is for creating the Enterprise Archieve (EAR file). I can right click on my EAR project and select export. I can then deploy the single EAR file to my WebSphere server.

The web project uses some third party jar files such as log4j. These utility jars must be deployed with the web application. So I dropped those jar files in my EAR project. From the web project, I right clicked on the web project, select properties.../ Then I select Java Build Path, click on the Libraries tab and then click “Add JARs...”. I can then select any JAR file that is in my workspace. The project file will use relative path to reference the Jar file so that the workspace is portable from one developer to another.

I have some Java code that can be reused in other projects so that I do not want to place them in my web project. I create seperate projects for the code. The projects need to be compiled into Jar files and then packaged into my EAR file. With RAD, there is an easy way to do it. Just click on the Deployment Descriptor of my EAR project, clicke on the Module tab, under Project Utility JARs, click Add to add all my library projects. When I export my EAR file, RAD will automatically create JAR files from my projects and include them in the EAR file.