DB2 Version 9.7 for Linux, UNIX, and Windows

Operation of automatic client reroute for connections to DB2 for Linux, UNIX, and Windows from non-Java clients

Automatic client reroute provides failover support when an IBM® data server client loses connectivity to the primary server for a DB2® for Linux, UNIX, and Windows database. Automatic client reroute enables the client to recover from a failure by attempting to reconnect to the database through an alternate server.

If automatic client reroute is enabled for a connection to a database, the following process typically occurs when a client encounters a connection failure with an existing connection:

The client attempts to execute an SQL statement using an existing connection and encounters a failure.
The client uses the server list that is returned after the last successful connection to identify the server to access, and attempts to reconnect to the database.
In a non-DB2 pureScale® environment, the server list contains two entries: one for the primary server and one for the alternate server.

In a DB2 pureScale environment, the server list contains an entry for each member of the DB2 pureScale instance. In addition, if an alternate server is defined for the database, the server list also contains an entry for that alternate server. An entry for a member of the DB2 pureScale instance includes capacity information. If connection-level workload balancing is enabled at the client, the client uses that information to connect to the server with the highest unused capacity. The entry for an alternate server has no capacity information. A connection to the alternate server is attempted only if connections to all of the DB2 pureScale members fail.
If the automatic client reroute process can reconnect the application to the database, the client reconstructs the execution environment for the newly established connection. The client receives an updated copy of the server list with updated server information. The error SQL30108N is returned to the application to indicate that the failed database connection has been recovered and that the transaction has been rolled back. The application is then responsible for further recovery, including repeating any work that was rolled back.
If the SQL statement that fails is the first SQL statement in the transaction, automatic client reroute with seamless failover is enabled, and the client is CLI or .NET, the driver replays the failed SQL operation as part of automatic client reroute processing. If the connection is successful, no error is reported to the application, and the transaction is not rolled back. The connectivity failure and subsequent recovery are hidden from the application.
If automatic client reroute is unable to reconnect to the database, the error SQL30081N is returned to the application. The application is then responsible for recovering from the connection failure (for example, by attempting to connect to the database by itself).

Automatic client reroute is also used when a client encounters a connection failure with a new connection. In this case, however, if reconnection is successful, no error is returned to the application to indicate that the failed database connection has been recovered. If reconnection fails, the error SQL30081N is returned.

In DB2 Version 9.7 Fix Pack 6 and later, if all the data, including the end of file (EOF) character, is returned in the first query block or in a subsequent fetch request, the CLI driver can perform seamless failover when you issue a COMMIT or ROLLBACK statement after the server becomes unreachable. For seamless failover to take place, the following conditions must be met:

You must enable both the enableAcr and enableSeamlessAcr parameters.
The cursor must have blocking enabled.
The cursor must be either read only or forward only.

If the application turned off the automatic closing of cursors at the server side by setting the SQL_ATTR_EARLYCLOSE attribute to OFF, the CLI driver can perform seamless failover only if the application processed the entire result set.

You can configure the FetchBufferSize keyword to ensure that the size of the result set that the CLI driver prefetches is sufficient to include the EOF character in the first query block. See the Related reference section for further details on FetchBufferSize.