DB2 Version 10.1 for Linux, UNIX, and Windows

Operation of the automatic client reroute feature for an application other than a Java application to the DB2 for z/OS server

The automatic client reroute (ACR) feature provides the failover support when an application loses connectivity to a member of the DB2® for z/OS® data sharing group. The ACR feature enables an application to recover from a connection failure by reconnecting to the database through an available member of the DB2 for z/OS data sharing group that has the Sysplex feature enabled.

The ACR feature is enabled by default when the Sysplex workload balancing feature is enabled.

Client support for the ACR feature is available in the IBM® data server clients that have a DB2 Connect™ license. The DB2 Connect server product is not required for the ACR feature.

The following example demonstrates the ACR operation when an application other than a Java™ application connects to the DB2 for z/OS data sharing group with the Sysplex workload balancing feature enabled:

As part of the response to a COMMIT request from the client, the data server returns:
- An indicator that specifies whether transports can be reused. Transports can be reused if there are no session resources remaining, such as held cursors.
- The SET statements that the client can use to replay the connection state during transport reuse. The SET statements are also known as special registers.
If the first SQL statement in a transaction fails:
- No error is reported to the application.
- The failing SQL statement is executed again. The failing SQL statement is executed on the next available member based on the returned server list. The number of times that the statement is retried and the duration between retry attempts are based on the maxAcrRetries and acrRetryInterval parameters.
- The SET statements that are associated with the connection are replayed to restore the connection state.
If an SQL statement that is not the first SQL statement in a transaction fails, and transports can be reused:
- The transaction is rolled back.
- The application is reconnected to the data server.
- The SET statements (special registers) that are associated with the connection are replayed to restore the connection state.
- The SQL30108N error is returned to the application to notify it of the rollback and successful reconnection. The application needs to include a code to retry the failed transaction.
If an SQL statement that is not the first SQL statement in a transaction fails, and transports cannot be reused:
- The special register settings are returned to the last COMMIT point.
- The SQL30081N error is returned to the application to notify it that reconnection was unsuccessful. The application needs to reconnect to the data server, reestablish the connection state, and retry the failed transaction.
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. If you declare any session resources in a translation, seamless ACR connections are changed into non-seamless ACR connections. The session resources include:
- Open and held cursor
- Open cursors with locators
- Declared global temporary table
- Accelerated Query
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 the FetchBufferSize parameter.
If connections to all members of the data sharing member list have been tried, and none have succeeded, a connection is tried using the DB2 group-wide IP address that is associated with the data sharing group, to determine whether any members are now available.

In V10.1 Fix Pack 2 and later fix packs, when connecting to the DB2 for z/OS data sharing group with the workload balance (WLB) feature enabled, the behavior of the non-seamless ACR feature has changed:

The CLI driver does not associate a transport with the connection until the application issues an SQL statement or a SET statement. When the application issues an SQL statement or a SET statement, the CLI driver allocates a transport and sets special registers to the COMMIT point of the last transaction.
The SQL30108N error is returned to the application if the CLI driver fails to reconnect to members of the primary group and must switch to the alternate group. The error is returned a second time when the alternate group is specified in the db2dsdriver.cfg file with the alternategroup parameter and the enableAlternateGroupSeamlessAcr is set to FALSE. The first SQL30108N error with reason code 2 is returned when the existing connection to a member in the current group fails. The second SQL30108N error with reason code 4 is returned when all the connection attempts to all members in the existing primary group fail. The application can then resubmit the SET statement or the SQL statement again for the second time if reconnection to the alternate group is warranted. The CLI driver tracks the failed member on the same connection handle when the ACR connection error (SQL30108N) is returned to avoid resubmitting the statement to the failed member.
Note: The SQL30108N error is not returned twice in the following scenarios:
- When the DB2 Connect server is used as a gateway.
- When the ACR feature is explicitly enabled without enabling the WLB feature.

When connecting to the DB2 for z/OS data sharing group, you should not disable the seamless ACR feature and the WLB feature unless directed by IBM support.

In V10.1 Fix Pack 2 and later fix packs, the following example demonstrates the non-seamless ACR operation when the Sysplex workload balancing feature is enabled:

As part of the response to a COMMIT request from the client, the data server returns the SET statements (special registers) that the client replays when the transport is associated at the next SQL statement submission or the SET statement submission based on the WLB routing decisions.
If the SQL statement in a transaction fails, the ACR connection error (SQL30108N with reason code 2) is returned to the application but the CLI driver does not look for a new transport. Any session resources that were created in the failed connection are dropped. The session resources include:
- Open and held cursor
- Open cursors with locators
- Declared global temporary table
- Accelerated Query
If the application submits the SET statement or the SQL statement, the CLI driver attempts to obtain a new transport to connect to the next available member in the same group.
If the enableAlternateGroupSeamlessACR parameter is set to FALSE and the CLI driver cannot reconnect to any of the members in the primary group, the second ACR connection error (SQL30108N with reason code 4) is returned to the application.
If the application submits the SET statement or the SQL statement again for the second time, the CLI driver attempts to obtain a new transport to connect to the next available member in the alternate group.
When the CLI driver successfully reconnects to a new member, the SET statements (special registers) that were returned from the last COMMIT point are replayed, followed by the SET statement or SQL statement submitted by the application. Any session resources that existed in a previous failed connection that are required must be re-created. If the reconnection is not successful, the communication error (SQL30081N) is returned to the application.

In the non-seamless ACR environment, the ReceiveTimeout event triggers the ACR connection error (SQL30108N).

If the QueryTimeout parameter is set in the non-seamless ACR environment, the following behaviors are observed:

If the connection failure occurs before the query timeout event, the ACR connection error (SQL30108N with reason code 2 or 4, and failure code 1, 2 or 3) is returned to the application.
If the Interrupt parameter 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.

When the Interrupt parameter is set to the value of 2 and the SQLCancel() API is explicitly sent from the application while the SQL statement is being executed, the ACR connection error (SQL30108N failure code 4 and error code 2) is returned to the application.