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.