DB2 Version 9.7 for Linux, UNIX, and Windows

Example of enabling DB2 for z/OS Sysplex workload balancing and automatic client reroute in Java applications

Java™ client setup for Sysplex workload balancing and automatic client reroute includes setting several IBM® Data Server Driver for JDBC and SQLJ properties.

The following examples demonstrate setting up Java client applications for Sysplex workload balancing and automatic client reroute for high availability.

Before you can set up the client, you need to configure the following server software:

WLM for z/OS®
For workload balancing to work efficiently, DB2® work needs to be classified. Classification applies to the first non-SET SQL statement in each transaction. Among the areas by which you need to classify the work are:
- Authorization ID
- Client info properties
- Stored procedure name
  The stored procedure name is used for classification only if the first statement that is issued by the client in the transaction is an SQL CALL statement.
For a complete list of classification attributes, see the information on classification attributes at Classification attributes (DB2 Performance).
DB2 for z/OS, set up for data sharing

Example of setup with WebSphere Application Server

This example assumes that you are using WebSphere® Application Server. The minimum level of WebSphere Application Server is Version 5.1.

Follow these steps to set up the client:

Verify that the IBM Data Server Driver for JDBC and SQLJ is at the correct level to support the Sysplex workload balancing by following these steps:
1. Issue the following command in a command line window
```
java com.ibm.db2.jcc.DB2Jcc -version
```
2. Find a line in the output like this, and check that nnn is 3.50 or later.
```
[jcc] Driver: IBM Data Server Driver for JDBC and SQLJ Architecture nnn xxx
```
In the WebSphere Application Server administrative console, set the IBM Data Server Driver for JDBC and SQLJ data source property enableSysplexWLB to true, to enable Sysplex workload balancing. Enabling Sysplex workload balancing enables automatic client reroute by default.

In the WebSphere Application Server administrative console, set other properties for which the defaults are unacceptable. Modify these WebSphere Application Server connection properties. The following settings are recommended when enableSysplexWLB is set to true:

Connection property	Recommended setting	Description
Reap Time	0	Specifies the interval, in seconds, between runs of the pool maintenance thread. The Reap Time interval affects performance. Because connections are not physical connections, disabling pool maintenance by setting this value to 0 is recommended.
Aged Timeout	0	Specifies the interval in seconds before a physical connection is discarded. Setting Aged Timeout to 0 allows connections to remain in the pool indefinitely.
Purge Policy	FailingConnectionOnly	Specifies how to purge connections when a stale connection or fatal connection error is detected. Because Sysplex workload balancing isolates WebSphere Application Server from stale connections or fatal connections errors, FailingConnectionOnly is the recommended setting.

The Maximum Connections value does not need to be changed. Member Connections specifies the maximum number of physical connections that you create in your pool. It does not control the number of physical connections to a data sharing group. With Sysplex workload balancing, connections are logical, and use transports to associate a connection to a data sharing member. Physical connections are managed by transport pools in the driver. The maxTransportObjects property controls the maximum number of connections to the group.

Optional: Set IBM Data Server Driver for JDBC and SQLJ configuration properties to fine-tune workload balancing for all DataSource or Connection instances that are created under the driver. Beginning with versions 3.63 and 4.13 of the IBM Data Server Driver for JDBC and SQLJ, the default values should work for most environments.
Set the configuration properties in a DB2JccConfiguration.properties file by following these steps:
1. Create a DB2JccConfiguration.properties file or edit the existing DB2JccConfiguration.properties file.
2. Set the configuration properties in the DB2JccConfiguration.properties file:
```
property=value
```
3. Add the directory path for DB2JccConfiguration.properties to the WebSphere Application Server IBM Data Server Driver for JDBC and SQLJ classpath.
4. Restart WebSphere Application Server.

Example of setup for DriverManager connections

This example assumes that you are using the DriverManager interface to establish a connection.

Follow these steps to set up the client:

Verify that the IBM Data Server Driver for JDBC and SQLJ is at the correct level to support the Sysplex workload balancing and automatic client reroute by following these steps:
1. Issue the following command in a command line window
```
java com.ibm.db2.jcc.DB2Jcc -version
```
2. Find a line in the output like this, and check that nnn is 3.50 or later. A minimum driver level of 3.50 is required for using Sysplex workload balancing and automatic client reroute for DriverManager connections.
3. ```
[jcc] Driver: IBM Data Server Driver for JDBC and SQLJ Architecture nnn xxx
```
Set the IBM Data Server Driver for JDBC and SQLJ Connection property enableSysplexWLB to enable workload balancing. Enabling Sysplex workload balancing enables automatic client reroute by default. Set any other properties for which the defaults are unacceptable. For most users, the default values do not need to be changed.
```
java.util.Properties properties = new java.util.Properties();
properties.put("user", "xxxx");
properties.put("password", "yyyy");
properties.put("enableSysplexWLB", "true");
java.sql.Connection con = 
  java.sql.DriverManager.getConnection(url, properties);
```
Optional: Set IBM Data Server Driver for JDBC and SQLJ configuration properties to fine-tune workload balancing for all DataSource or Connection instances that are created under the driver. Beginning with versions 3.63 and 4.13 of the IBM Data Server Driver for JDBC and SQLJ, the default values should work for most environments. Set the configuration properties in a DB2JccConfiguration.properties file by following these steps:
1. Create a DB2JccConfiguration.properties file or edit the existing DB2JccConfiguration.properties file.
2. Set the configuration properties in the DB2JccConfiguration.properties file:
```
property=value
```
3. Include the directory that contains DB2JccConfiguration.properties in the CLASSPATH concatenation.