5.3 Creating and configuring DB2 Data Source

---

5.2 Creating and configuring DB2 JDBC Provider

IBM DB2 UDB family products are seamlessly integrated with WebSphere Application Server. As mentioned in "DB2 JDBC providers" on page 107, DB2 UDB V8 supports the legacy JDBC Type 2 driver and the universal JDBC driver that combines Type 2 and Type 4 JDBC implementations. In addition, DB2 UDB V8 also supports a Type 3 driver, although this driver is deprecated.

For more information about DB2 UDB's support for JDBC and SQLJ, please refer to the DB2 UDB V8 application development support home page for Java at:

http://www-3.ibm.com/software/data/db2/udb/ad/v8/java/

Here we describe some of the major DB2 JDBC providers supported in WebSphere Application Server V5. We also show the supported data source classes and their required properties.

DB2 Universal JDBC Driver Provider

The DB2 Universal JDBC Driver is an architecture-neutral JDBC driver for distributed and local DB2 access. Because the Universal Driver architecture is independent of any particular JDBC driver connectivity or target platform, it allows both Java connectivity (Type 4) or Java Native Interface (JNI) based connectivity (Type 2) in a single driver instance to DB2. Starting with Version 5.0.2, the Websphere Application Server product now supports both Type 2 and Type 4 JDBC drivers. To use the Type 4 driver, you must install DB2 Version 8.1 or a later version. To use the Type 2 driver, you must install DB2 Version 8.1 FixPak 2 or a later version.

The DB2 Universal JDBC Driver only supports one-phase transactions. This JDBC driver allows applications to use both JDBC and Structured Query Language in Java (SQLJ) access.

The DB2 Universal JDBC Driver Provider supports one-phase data source:

 com.ibm.db2.jcc.DB2ConnectionPoolDataSource 

The following JDBC driver files are required to use the DB2 Universal JDBC Driver Provider:

• db2jcc.jar

  After you install DB2 UDB, you can find this jar file in the sqllib/java directory. For Type 4 JDBC driver support from a client machine where DB2 is not installed, copy this file to the local machine. If you install any fixes or upgrades to DB2, you must update this file as well. You should also remember to set the DB2UNIVERSAL_JDBC_DRIVER_PATH environment variable to point to the db2jcc.jar file if that WebSphere environment variable is used.

• db2jcc_license_cu.jar

  This is the DB2 Universal JDBC driver license file that allows access to the DB2 Universal database on Linux, UNIX and Windows.

• db2jcc_license_cisuz.jar

  This is the DB2 Universal JDBC driver license file that allows access to the following databases:

  • DB2 Universal Database for Linux, UNIX and Windows

  • DB2 for iSeries

  • DB2 UDB for z/OS

Note 

You must use the correct license jar file to access a specific backend database.

The required DataStoreHelper class is as below:

 com.ibm.websphere.rsadapter.DB2UniversalDataStoreHelper 

The required properties for the DB2 Universal JDBC Driver Provider include:

• databaseName

  This is an actual database name if the driverType is set to 4, or a locally cataloged database name if the driverType is set to 2.

• driverType

  The JDBC connectivity type of a data source. There are two permitted values: 2 and 4. If you want to use Universal JDBC Type 2 driver, set this value to 2. If you want to use Universal JDBC Type 4 driver, set this value to 4.

• serverName

  The TCP/IP address or host name for the Distributed Relational Database Architecture (DRDA) server. Provide a value for this property only if your driverType is set to 4. This property is not required if your driverType is set to 2.

• portNumber

  The TCP/IP port number where the DRDA server resides. Provide a value for this property only if your driverType is set to 4. This property is not required if your driverType is set to 2.

DB2 Universal JDBC Driver Provider (XA)

The DB2 Universal JDBC Driver (XA) is an architecture-neutral JDBC driver for distributed and local DB2 access. In WebSphere Application Server Version 5.0.2, this driver only supports Java Native Interface (JNI) based connectivity (Type 2) in a single driver instance to DB2. To use this driver, you must install DB2 Version 8.1 FixPak 2 or a later version. This driver supports two-phase transactions and allows applications to use both JDBC and SQLJ access.

The DB2 Universal JDBC Driver Provider (XA) supports the two-phase data source:

 com.ibm.db2.jcc.DB2XADataSource 

The required JDBC driver files list and DataStoreHelper class to use the DB2 Universal JDBC Driver Provider (XA) are same as those required by DB2 Universal JDBC Driver Provider, you could refer to "DB2 Universal JDBC Driver Provider" on page 136 for more details.

The required properties for the DB2 Universal JDBC Driver Provider (XA) include:

• databaseName

  This is a locally cataloged database name.

• driverType

  The JDBC connectivity type of a data source. The only permitted value is 2.

DB2 legacy CLI-based Type 2 JDBC Driver

The DB2 legacy CLI-based Type 2 JDBC Driver Provider is built on top of DB2 CLI (Call Level Interface). It uses the DB2 CLI interface to communicate with DB2 UDB servers.

DB2 legacy CLI-based Type 2 JDBC Driver supports one-phase data source:

 COM.ibm.db2.jdbc.DB2ConnectionPoolDataSource 

The required driver file to use the DB2 legacy CLI-based Type 2 JDBC Driver is db2java.zip. (Note: If you run SQLJ in DB2 UDB V8, db2jcc.jar is also required.)

The required DataStoreHelper class, as shown below, is different from the one required by DB2 Universal JDBC Driver Provider:

 com.ibm.websphere.rsadapter.DB2DataStoreHelper 

The databaseName is the only required property. databaseName is the name of the database from which the data source obtains connections.

DB2 legacy CLI-based Type 2 JDBC Driver (XA)

The DB2 legacy CLI-based Type 2 JDBC Driver (XA) is built on top of DB2 CLI. It uses the DB2 CLI interface to communicate with DB2 UDB servers.

DB2 legacy CLI-based Type 2 JDBC Driver (XA) supports two-phase data source:

 COM.ibm.db2.jdbc.DB2XADataSource 

The required driver files, DataStoreHelper class and the properties are all the same as those required by DB2 legacy CLI-based Type 2 JDBC Driver.

In addition to the DB2 JDBC providers covered above, WebSphere Application Server V5 also support other DB2 JDBC providers such as DB2/390 JDBC Providers and DB2 UDB for iSeries JDBC Providers (including non-XA and XA support). For the more complete DB2 JDBC providers supported by WebSphere Application Server V5, please refer to the section Vendor-specific data sources minimum required settings in WebSphere Application Server V5 InfoCenter.

5.2.1 The steps to create and configure DB2 JDBC Providers

In this section the general steps to create and configure DB2 JDBC Providers in the WebSphere Application Server V5 environment are provided. Creating the JDBC Provider is a prerequisite to access a database. For WebSphere Application Server V5, we could create and configure a JDBC provider using the administrative console or using the Java Management Extensions API. In the example shown below, we use the WebSphere Application Server Administrative Console to create the JDBC Providers. The basic steps are shown as below:

1. Log into WebSphere Application Server Administrative Console via the URL http://kanaga:9090/admin, then the Console login page, as shown in Figure 5-2 on page 124, is displayed. As the security is disabled by default, here you could input a user ID without a password, and the user ID does not need to be a user in the local user registry. It is only used to track user-specific changes to configuration data.

2. After logging into the administrative console, click Resources -> JDBC Providers, then choose the Scope of your definition and click New to create a new JDBC Provider, as the Figure 5-6 on page 140 illustrates.

   
   Figure 5-6: Creating JDBC Providers in WAS Administrative Console

3. Use the drop-down list to select the type of JDBC provider.

   If your desired JDBC provider is not displayed in the list of supported DB2 JDBC provider types, you could also select the User-Defined JDBC Provider to create your own JDBC Provider. In our case, the existing DB2 Universal JDBC Driver Provider is chosen for demonstration purposes (see Figure 5-7 on page 141).

   
   Figure 5-7: Choosing the Type of DB2 JDBC Providers

4. Enter the properties for your DB2 JDBC provider.

   As illustrated in the Figure 5-8 on page 142, you could provide a name for the JDBC provider, specify a list of paths or JAR file names, which together form the location for the resource provider classes in the classpath block. Be aware that classpaths could contain variable (symbolic) names, which you can substitute using a variable map. In our case, the variable DB2UNIVERSAL_JDBC_DRIVER_PATH is used. We need to define it before running an application with the JDBC Provider. Regarding how to manage variables in WebSphere Application Server environment, refer to the next step for more details. Click OK to return to the JDBC providers page, where your new JDBC provider appears in the list.

   
   Figure 5-8: Setting Properties for DB2 Universal JDBC Providers

5. Apply and save your changes. As Figure 5-9 on page 143 displays, we can find that the changes we just made are saved to the file named resources.xml, which is located under the <USER_INSTALL_ROOT>cells/kanaga/nodes/kanaga subdirectory, where USER_INSTALL_ROOT is the home directory for the WebSphere Application Server V5 installation.

   
   Figure 5-9: Saving the changes

6. Define required WebSphere Variable.

   Click Environment -> Manage WebSphere Variables (as shown in Figure 5-10), and scroll down to the desired variable. If it has been there then assign the value for it, or you could click New to create a new WebSphere variable if the required variable has not yet been shown in the list.

   
   Figure 5-10: Managing WebSphere variables

Up to this point, the DB2 Universal JDBC Provider has been created and ready to create a DB2 Data Source on top of that.

---