DB2 9 JDBC configuration with IBM Webspehere 6.1

Configuring a JDBC Provider

It is important to understand the confuguration of DB2 for the successful implementation of J2EE application hosted under IBM Websphere. To access the Websphere administration console that is running as localhost, use the URL: *http://localhost:9060/ibm/console* The default port the server is running is 9060. This is where you will configure JDBC, deploy applications, set environment variables, and define login configurations etc. (see Figure 1). In this example, I am using DB2 9 Enterprise Server Edition to configure a JDBC connection from WAS 6.1.

Select the Resources -> JDBC -> JDBC Providers node to configure a new JDBC provider. A JDBC provider, which is used to access a database, consists of database settings, the JDBC driver type, and the driver class.

Select the server scope (Server=server1) for defining a new JDBC provider. Click the New button to configure a new JDBC provider. In the Configuration window, select DB2 as the database type. Select DB2 Universal JDBC Driver Provider as the provider type. Select the implementation type Connection pool data source. Click Next.

Figure 1. Creating a new JDBC Provider

The Class path field specifies the Jar files in the class path. The environment variable DB2Universal_JDBC_Driver_Path is specified in the class path field of the DB2 JDBC Provider. The db2jcc.jar (which has the DB2 JDBC Type 4 driver) and the db2jcc_license_cu.jar (which is the license file for the DB2 server), are included in the Class path setting.

In the Directory location for the db2jcc.jar field, specify the directory which has the dbj2cc.jar and the db2jcc_license_cu.jar files, which in this example is C:\DB2\JDBC\java directory by default. Click Next to see the summary for the JDBC provider. The environment variable DB2UNIVERSAL_JDBC_DRIVER_Path value is set to the directory that contains the dbj2cc.jar and the db2jcc_license_cu.jar files is C:\DB2\JDBC\java.

The driver class name is com.ibm.db2.jcc.DB2ConnectionPoolDataSource. Click Finish. The settings you specified are applied to the local configuration. Click on Save in the Messages frame to apply the changes to the master configuration. In the JDBC Providers frame, click Save to apply the changes to the workspace to the master configuration. A JDBC provider for the DB2 database gets configured and added to the list of JDBC Providers (see Figure 2).

DB2 Connection Pooling DataSource

DB2ConnectionPoolDataSource.class

The com.ibm.db2.jcc.DB2ConnectionPoolDataSource class extends the com.ibm.db2.jcc.DB2BaseDataSource class, and implements the javax.sql.ConnectionPoolDataSource, java.io.Serializable, and javax.naming.Referenceable interfaces.

DB2ConnectionPoolDataSource is a factory for PooledConnection objects. An object that implements this interface is registered with a naming service that is based on the Java Naming and Directory Interface (JNDI).

The connection pooling properties are defined only for the IBM DB2 Driver for JDBC and SQLJ. Click here for explanations of these properties. The following are the list of different DB2 implementation of data sources.

• com.ibm.db2.jc c.DB2SimpleDataSource, which does not support connection pooling. You can use the implementation with the Universal Type 2 driver or the Type 4 driver.
• com.ibm.db2.jcc.DB2DataSource, which supports connections pooling. You can use this implementation only with the Type 2 driver. With this implementation, connection pooling is handled internally and is transparent to the application.
• com.ibm.db2.jc c.DB2ConnectionPoolDataSource, which supports connection pooling. With this implementation, you must manage connection pooling yourself, either by writing custom code or by using a tool like IBM WebSphere.
• com.ibm.db2.jcc.DB2XADataSource--same as DB2ConnectionPoolDataSource, but supports distributed transactions

Figure 2. Specifying a new JDBC Provider

Configuring a J2C authentication data entry

J2EE connector authentication data entries are used by a JDBC datasource to log in to a database. In this section we'll create a J2C authentication data entry to log in to the DB2 database.

Select the Secure administration, applications, and infrastructure node in the WebSphere administration console. Next, select Java Authentication and Authorization Service -> J2C Authentication data in the Authentication sub-header. A table of J2C authentication data entries gets displayed. Click on the New button to add a J2C authentication data entry. In the J2C authentication data entry configuration frame, specify an alias for the data entry. Specify the user ID and password used to log in to the DB2 database. Click Apply. Click Save to save the workspace changes to the master configuration. A new J2C authentication data entry gets added.

Configuring a data source

We will now configure a JDBC data source to access data from the DB2 database. The JDBC provider configured earlier supplies the driver class for the data source. To configure a new data source, select the link for the JDBC provider DB2 Universal JDBC Driver Provider and click on the Data sources link in the Additional Properties header. A table of data sources is displayed. Click on the New button to add a new data source. In the data source configuration frame specify a data source name, and a JNDI name for the data source (see Figure 3). In the Component-managed authentication alias field, select the J2C authentication data entry configured in the previous section. Click Next.

Figure 3. Specifying datasource JNDI names.

In the data source properties frame, specify the database name (DSNQ), driver type (Type 4), server name (os19.in.vbose.com), and port number(4763). The JNDI (Java Naming Directory Interface) is what we will use to look up the data source name from the application code is named as jdbc/SampleDS

Click Next. The summary page displays the data source summary. Click Finish to apply the data source configuration. Click Save to save the data source configuration. A new data source gets added to the data sources table. Select the data source check box and click on Test connection frame to test the JDBC connection with the DB2 database. A message indicates if the JDBC connection with the DB2 database was successful.

Next, configure additional connection pool properties for the data source by selecting the Connection Pool Properties link in the Additional Properties header on the data source page. Specify the connection pool properties. Connection timeout is the maximum number of seconds an application waits for a connection from the connection pool before timing out; the default value is 180 seconds. Maximum connections is the maximum number of connections in the connection pool; the default value is 10. Minimum connections is the minimum number of connections in the connection pool; the default is 1.

Access Managed DB2 Connection via JNDI from the application code

Now, we will use the JNDI name configured above to look up the data source name to access DB2 database. At this point, the database connection and the connection pool are managed by the application server. Since the database connection and the connection pooling are managed by the application server, it is called a managed connection. The application code has to ensure that the connection resource is closed after its use to avoid expensive resource leaks. Closing connection resource is nothing but the connection that the application obtained from the pool via JNDI look up is now returned back to the pool and is available for other requests or threads to use.

In the application code example below, I will use the sophisticated, simple, less invasive and well established industry standard J2EE framework called Spring to obtain the connection via JNDI look up of the application server and the framework closes the same after running a query. I am also using iBatis data mapper framework to map the data to the objects.

Let us now design the business service API named LookUpServiceImpl.java which implements the interface ILookUpService.java

ILookUpService.java


/**
* Created on : Dec 31, 2008
* File : ILookUpService.java
* $ Revision : $
* Copyright © Vigil Bose
* All Rights Reserved
* This is unpublished proprietary source code.
* The copyright notice above does not evidence any actual or
* intended publication of such source code.
*/
package com.vbose.bus;

import java.util.Set;

import com.vbose.domain.EventActions;

/**
* ILookUpService.java : A java business service interface
* that exposes the APIs used by user interface layer to access
* the read only business data
* @author Vigil Bose
*/
public interface ILookUpService {

/**
* The API findEventActionsByActionId() is used to find the EventActions domain object
* for the given actionId
* @param actionId - The event action identifier
* @return an instance of EventActions domain object
*/
public EventActions findEventActionsByActionId(Integer actionId);
}



LookUpServiceImpl.java


/**
* Created on : Dec 31, 2008
* File : LookUpServiceImpl.java
* $ Revision : $
* Copyright © Vigil Bose
* All Rights Reserved
* This is unpublished proprietary source code.
* The copyright notice above does not evidence any actual or
* intended publication of such source code.
*/
package com.vbose.bus.impl;

import java.util.Set;

import org.springframework.transaction.annotation.Transactional;
import org.springframework.stereotype.service;

import com.vbose.bus.ILookUpService;
import com.vbose.domain.EventActions;

/**
* LookUpServiceImpl.java : A java business service implementation
* that implements the APIs used by user interface layer to access
* read only data.
* @author Vigil Bose
*/
@Service("lookupService")
public class LookUpServiceImpl
implements ILookUpService{

private ILookUpServiceIBatisDao lookUpServiceIBatisDao;

/**
* A setter method of dependency injection
* @param lookUpServiceIBatisDao the lookUpServiceIBatisDao to set
*/
public void setLookUpServiceIBatisDao(
ILookUpServiceIBatisDao lookUpServiceIBatisDao) {
this.lookUpServiceIBatisDao = lookUpServiceIBatisDao;
}

/**
* The API findEventActionsByActionId() is used to find the EventActions domain object
* for the given actionId
* @param actionId - The event action identifier
* @return an instance of EventActions domain object
*/
@Transactional (readOnly = true)
public EventActions findEventActionsByActionId(Integer actionId) {
return this.lookUpServiceIBatisDao().
findEventActionsByActionId(actionId);
}
}



The business service implementation API has @Transactional annotation which is used by the Spring framework to initiate a read only transaction to the database.

The configuration element declared in spring application context (See Spring application context) will automatically detect the underlying server and choose the best transaction manager available for the platform. This means that you won't have to configure server-specific adapter classes (as discussed in the following sections) explicitly; they will rather be chosen automatically, with the standard JtaTransactionManager as default fallback.

On WebSphere 6.0 and above, the recommended Spring JTA transaction manager to use is WebSphereUowTransactionManager. This special adapter leverages IBM's UOWManager API which is available in WebSphere Application Server 6.0.2.19 or above and 6.1.0.9 or above. With this adapter, Spring-driven transaction suspension (suspend/resume as initiated by PROPAGATION_REQUIRES_NEW) is officially supported by IBM!

The business service API findEventActionsByActionId() shown above delegates the call to the data access interface where the query gets executed and returns the results. With this set up the business service is agnostic about the data access infrastructure. The service does not know whether the underlying connection is obtained via JNDI app server managed connection or local non-managed connection or even using iBatis data mapper framework under the hoods.

ILookUpServiceIBatisDao.java (Data access repository interface)


/**
* Created on : Dec 31, 2008
* File : ILookUpServiceDao.java
* $ Revision : $
* Copyright © Vigil Bose
* All Rights Reserved
* This is unpublished proprietary source code.
* The copyright notice above does not evidence any actual or
* intended publication of such source code.
*/
package com.vbose.bus.dao;

import java.util.Set;
import org.springframework.dao.DataAccessException;
import com.vbose.domain.EventActions;


/**
* ILookUpServiceDao.java : Data Access Interface that exposes
* read only APIs that facilitate the business service to get
* access to the data
* @author Vigil Bose
*/
public interface ILookUpServiceIBatisDao {

/**
* The API findEventActionsByActionId() is used to find the EventActions domain object
* for the given actionId
* @param actionId - The event action identifier
* @return an instance of EventActions domain object
* @throws DataAccessException
*/
public EventActions findEventActionsByActionId(Integer actionId) throws DataAccessException;
}


LookUpServiceIBatisDaoImpl.java (Data access repository implementation)


/**
* Created on : Dec 31, 2008
* File : LookUpServiceIBatisDaoImpl.java
* $ Revision : $
* Copyright © Vigil Bose
* All Rights Reserved
* This is unpublished proprietary source code.
* The copyright notice above does not evidence any actual or
* intended publication of such source code.
*/
package com.vbose.bus.dao.impl;

import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.springframework.dao.DataAccessException;
import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;
import com.vbose.bus.dao.ILookUpServiceIBatisDao;
import com.vbose.domain.EventActions;

/**
* LookUpServiceIBatisDaoImpl : A data access strategy implementation
* based on iBatis Data Mapper framework that implements the APIs of
* {@link ILookUpServiceIBatisDao}
* @author Vigil Bose
*/
public class LookUpServiceIBatisDaoImpl
extends SqlMapClientDaoSupport
implements ILookUpServiceIBatisDao {

/**
* The API findEventActionsByActionId() is used to find the EventActions domain object
* for the given actionId
* @param actionId - The event action identifier
* @return an instance of EventActions domain object
* @throws DataAccessException
*/
public EventActions findEventActionsByActionId(Integer actionId)
throws DataAccessException {
return (EventActions)getSqlMapClientTemplate().
queryForObject("findEventActionsByActionId",actionId);
}
}

The data access service shown above is executed in read only transaction. Before executing the API, this service has access to the two core infrastructure objects that facilitate the query execution. One is sqlMapClient provided by SqlMapClientDaoSupport class and the other one is data source to obtain the connection. The framework handles all the plumbing of code that makes the application code cleaner. The framework opens the connection before the query is executed and closes the connection after the query is executed.

sql-map-config.xmll (iBatis SQL mapper infrastructure configuration)



<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
<settings lazyLoadingEnabled="true" enhancementEnabled="true"/>
<sqlMap resource="com/vbose/domain/ibatis/maps/EventActions.xml"/>
</sqlMapConfig>



EventActions.xml (Data mapper for EventActions.java)



<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="EventActions">

<resultMap id="eventActionsResult" class="com.vbose.domain.EventActions">
<result column="ACTION_ID" property="actionId" jdbcType="INTEGER" />
<result column="ACTION_DESC" property="actionDesc" jdbcType="VARCHAR" />
<result column="SUSP_ID" property="suspensionId" jdbcType="INTEGER" />
</resultMap>

<select id="findEventActionsByActionId" resultMap="eventActionsResult">
SELECT ea.ACTION_ID,
ea.ACTION_DESC,
ea.SUSP_ID
FROM SS_EVENT_ACTIONS ea
WHERE ea.ACTION_ID = #value#
WITH UR
</select>

</sqlMap>



EventActions.java (Domain Object)



/**
* Created on : Dec 31, 2008
* File : EventActions.java
* $ Revision : $
* Copyright © Vigil Bose
* All Rights Reserved
* This is unpublished proprietary source code.
* The copyright notice above does not evidence any actual or
* intended publication of such source code.
*/
package com.vbose.domain;

import java.io.Serializable;

/**
* EventActions.java : A class that represents a row in
* the 'EVENT_ACTIONS' table.
* @author Vigil Bose
*/
public class EventActions implements Serializable {

private static final long serialVersionUID = 1L;
/** The cached hash code value for this instance. Settting to 0 triggers re-calculation. */
private int hashValue = 0;

private Integer actionId;
private String actionDesc;
private Integer suspensionId;

/**
* Simple constructor of EventActions instances.
*/
public EventActions() {

}
/**
* Constructor of EventActions instances given a simple primary key.
* @param eventId
*/
public EventActions(Integer actionId) {
this.actionId = actionId;
}
/**
* @return the actionId
*/
public Integer getActionId() {
return actionId;
}
/**
* @param actionId the actionId to set
*/
public void setActionId(Integer actionId) {
this.hashValue = 0;
this.actionId = actionId;
}
/**
* @return the actionDesc
*/
public String getActionDesc() {
return actionDesc;
}
/**
* @param actionDesc the actionDesc to set
*/
public void setActionDesc(String actionDesc) {
this.actionDesc = actionDesc;
}
/**
* @return the suspensionId
*/
public Integer getSuspensionId() {
return suspensionId;
}
/**
* @param suspensionId the suspensionId to set
*/
public void setSuspensionId(Integer suspensionId) {
this.suspensionId = suspensionId;
}

/**
* Implementation of the equals comparison on the basis of equality of the primary key values.
* @param rhs
* @return boolean
*/
public boolean equals(Object rhs){
if (rhs == null){
return false;
}
if (! (rhs instanceof EventActions)){
return false;
}
EventActions that = (EventActions) rhs;
if (this.getActionId() == null || that.getActionId() == null){
return false;
}
return (this.getActionId().equals(that.getActionId()));
}

/**
* Implementation of the hashCode method conforming to the Bloch pattern with
* the exception of array properties (these are very unlikely primary key types).
* @return int
*/
public int hashCode() {
if (this.hashValue == 0) {
int result = 17;
int actionIdValue = this.getActionId() == null ? 0 :
this.getActionId().hashCode();
result = result * 37 + actionIdValue;
this.hashValue = result;
}
return this.hashValue;
}
}



Now, wire the JDBC infrastructure and the data access object in the Spring application context. See below.

Spring Application Context



<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:jee="http://www.springframework.org/schema/jee"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-2.5.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd">

<jee:jndi-lookup id="sampleDS" jndi-name="jdbc/SampleDS" proxyInterface="javax.sql.DataSource" resourceRef="true"/>

<context:annotation-config>

<tx:annotation-driven order="2"/>

<!--JTA appserver managed distributed transaction manager reference-->
<tx:jta-transaction-manager />

<!-- SqlMap setup for iBATIS Database Layer -->
<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="configLocation" value="classpath:/com/vbose/domain/ibatis/maps/sql-map-config.xml"/>
<property name="dataSource" ref="sampleDS"/>
</bean>

<bean id="lookUpServiceIBatisDao" class="com.vbose.bus.dao.impl.LookUpServiceIBatisDaoImpl">
<property name="sqlMapClient" ref="sqlMapClient"/>
</bean>


</beans>


RAR Deployment

Since Spring 2.5, it is possible to deploy a Spring ApplicationContext as a RAR file, encapsulating the context and all of its required bean classes and library JARs in a J2EE RAR deployment unit. This is the equivalent of bootstrapping a standalone ApplicationContext, just hosted in J2EE environment, being able to access the J2EE server's facilities. RAR deployment is intended as a more 'natural' alternative to the not uncommon scenario of deploying a headless WAR file - i.e. a WAR file without any HTTP entry points, just used for bootstrapping a Spring ApplicationContext in a J2EE environment.

RAR deployment is ideal for application contexts that do not need any HTTP entry points but rather just consist of message endpoints and scheduled jobs etc. Beans in such a context may use application server resources such as the JTA transaction manager and JNDI-bound JDBC DataSources and JMS ConnectionFactory instances, and may also register with the platform's JMX server - all through Spring's standard transaction management and JNDI and JMX support facilities. Application components may also interact with the application's server JCA WorkManager through Spring's TaskExecutor abstraction.

Access DB2 Connection locally using Apache DBCP

Now, I will show how the application code can access DB2 connection locally using Apache DBCP connection pool mechanism. The application code remains unchanged and all I have to do is use a local connection in the Spring application context instead of JNDI connection and JTA transaction manager. This set up is handy when the application is not running under IBM Websphere or any appserver environment.



<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:jee="http://www.springframework.org/schema/jee"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-2.5.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd">

<!-- Transaction manager for a single JDBC DataSource (alternative to JTA) -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
p:dataSource-ref="sampleDS"/>

<bean id="sampleDS" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"
p:driverClassName="com.ibm.db2.jcc.DB2Driver"
p:url="jdbc:db2://os19.in.vbose.com:4763/DSNQ:currentFunctionPath=SAMPLE;IGNORE_DONE_IN_PROC=true;currentSchema=SAMPLE;"
p:username="xxxxxx"
p:password="xxxxxxx"
p:maxActive="3"
p:maxIdle="3"
p:validationQuery="SELECT DTS FROM DT_TM_TS WITH UR"
p:testWhileIdle="true"
p:timeBetweenEvictionRunsMillis="1800000"
/>


<context:annotation-config>

<tx:annotation-driven order="2"/>


<!-- SqlMap setup for iBATIS Database Layer -->
<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="configLocation" value="classpath:/com/vbose/domain/ibatis/maps/sql-map-config.xml"/>
<property name="dataSource" ref="sampleDS"/>
</bean>

<bean id="lookUpServiceIBatisDao" class="com.vbose.bus.dao.impl.LookUpServiceIBatisDaoImpl">
<property name="sqlMapClient" ref="sqlMapClient"/>
</bean>


</beans>



In the above configuration, I changed two bean configurations. One is datasource and the other is the transactionManager. The datasource and connection pool are obtained via Apache database connection pool mechanism. Since it is not managed by application server, it is called non-managed connection. Here, the application's business layer remains unchanged. This set up does not require application server environment and it also enables unit testing without the dependency on the application server.

References

1. Spring Framework 2.5.6 Reference Document
2. IBM Websphere RedBooks
3. iBatis 2.3.4 Reference Document
   

About The Author

Vigil Bose works as Principal System Architect (Java/J2EE) at OCTO, Washington DC Government. OCTO develops, implements and maintains the District's IT and Telecommunications infrstructure; develops and implements major city wide applications. Mr. Bose primarily focuses on light-weight architectures and open source frameworks to build J2EE solutions for his clients. Mr. Bose has designed and built successful enterprise software solutions using Spring, and other open source tools and implemented them in production for his clients. He enjoys creating business value by applying simple techniques and principles to application architecture.