DB2 10.5 for Linux, UNIX, and Windows

Operation of automatic client reroute for connections to the DB2 for Linux, UNIX, and Windows server from an application other than a Java™ application

Automatic client reroute (ACR) 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 the 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 attempts to execute an SQL statement by using an existing connection and that connection fails.

The client uses the server list that was returned after the last successful connection to identify which server to access and attempts to reconnect to the database.
In an environment other than a 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 you defined an alternate server for the database, the server list 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 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 new connection. The client receives an updated copy of the server list with updated server information. Error SQL30108N is returned to the application to indicate that the failed database connection was recovered and that the transaction was 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 the application to the database, 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 new connection attempt fails. In this case, however, if reconnection is successful, no error is returned to the application to indicate that the failed database connection was recovered. If reconnection fails, error SQL30081N is returned.

A seamless failover connection can be established under the following conditions:

If the connection failure occurs for the first SQL statement in a transaction and a complete result set is returned to the client, the CLI driver can fail over seamlessly when you issue a COMMIT or ROLLBACK statement after the server becomes unreachable. For seamless failover to take place, the following conditions must also be met:
- You must enable both the enableAcr and enableSeamlessAcr keywords.
- No error is reported to the application.
- The cursor must have blocking enabled.
- The cursor must be either read-only or forward-only.
If the connection failure occurs in a transaction that was read-only before the point of connection failure, seamless failover can take place if all the following conditions are met:
- Application is connecting to a DB2 Cancun Release 10.5.0.4 or later server.
- You enable both the enableAcr and enableSeamlessAcr keywords.
- The detectReadonlyTxn keyword is set to true.
- No error is reported to the application.
- The completed statements in the transaction up to the point of connection failure are read-only, and complete result sets from the transaction up to the point of connection failure are available to the client. The failed SQL statement does not have to be read-only, but statements that are issued before the failure must be read-only.
- The transaction does not use the repeatable read (RR) or read stability (RS) isolation level.

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.

Non-seamless ACR environments have one of the following combinations of settings:

The enableAcr keyword is set to TRUE, but the enableSeamlessAcr keyword is set to FALSE.
The enableACR keyword and the enableSeamlessAcr keyword are set to TRUE but the seamless ACR environment cannot be enabled for the following reasons:
- Session resources are created in the transaction.
- The failed SQL statement was not the first SQL statement of the transaction.

In the non-seamless ACR environment, the receive timeout event triggers the ACR connection error (SQL30108N). The receive timeout event occurs when the ReceiveTimeout keyword value is reached

If you set the QueryTimeout keyword in the non-seamless ACR environment, the following behaviors occur:

If the connection failure occurs before the query timeout event, the ACR connection error (SQL30108N, with reason code 1 and failure code 1, 2, or 3) is returned to the application.
If the Interrupt keyword is set to the default value of 1 and the query timeout event occurs, the SQL0952N error is returned to the application.
If the Interrupt keyword is set to the value of 2 and the query timeout event occurs, the ACR connection error (SQL30108N, with failure code 4 and error code 1) is returned to the application.

If the Interrupt keyword is set to the value of 2 and the SQLCancel() API is explicitly called from the application while an SQL statement is being executed, the ACR connection error (SQL30108N, with failure code 4 and error code 2) is returned to the application.

If you set the tcpipConnectTimeout and memberConnectTimeout keywords and the TCPIP connection timeout or the member connection timeout event occurs in the non-seamless ACR environment, the CLI driver reconnects to a new available member. However, the ACR error (SQL30108N) is not returned to the application.