Operation of workload balancing for connections to DB2 for Linux, UNIX, and Windows

Workload balancing (also called transaction-level workload balancing) for connections to DB2® for Linux, UNIX, and Windows contributes to high availability by balancing work among servers in a DB2 pureScale® instance at the start of a transaction.

The following overview describes the steps that occur when a client connects to a DB2 for Linux, UNIX, and Windows DB2 pureScale instance, and transaction-level workload balancing is enabled:

1. When the client first establishes a connection to the DB2 pureScale instance, the member to which the client connects returns a server list with the connection details (IP address, port, and weight) for the members of the DB2 pureScale instance.

   The server list is cached by the client. The default lifespan of the cached server list is 30 seconds.

2. At the start of a new transaction, the client reads the cached server list to identify a server that has unused capacity, and looks in the transport pool for an idle transport that is tied to the under-utilized server. (An idle transport is a transport that has no associated connection object.)
   • If an idle transport is available, the client associates the connection object with the transport.
   • If, after a user-configurable timeout period (db2.jcc.maxTransportObjectWaitTime for a Java™ client or maxTransportWaitTime for a non-Java client), no idle transport is available in the transport pool and no new transport can be allocated because the transport pool has reached its limit, an error is returned to the application.
3. When the transaction runs, it accesses the server that is tied to the transport.

   When the first SQL statement in a transaction runs, if the IBM® Data Server Driver for JDBC and SQLJ receives a communication failure because the data server drops the connection or the blockingReadConnectionTimeout value was exceeded, the driver retries the SQL statement 10 times before it reports an error. On every retry, the driver closes the existing transport, obtains a new transport and then executes the transaction. During these retries, if the maxRetriesForClientReroute and retryIntervalForClientReroute properties are set, their values apply only to the process of obtaining a new transport during each retry.

4. When the transaction ends, the client verifies with the server that transport reuse is still allowed for the connection object.
5. If transport reuse is allowed, the server returns a list of SET statements for special registers that apply to the execution environment for the connection object.

   The client caches these statements, which it replays in order to reconstruct the execution environment when the connection object is associated with a new transport.

6. The connection object is then dissociated from the transport, if the client determines that it needs to do so.
7. The client copy of the server list is refreshed when a new connection is made, or every 30 seconds, or the user-configured interval.
8. When transaction-level workload balancing is required for a new transaction, the client uses the previously described process to associate the connection object with a transport.