Application programming requirements for automatic client reroute for connections to Db2 for z/OS servers

Connection failover for the automatic client reroute feature can be seamless or non-seamless. To handle non-seamless failover to a Db2 for z/OS data sharing group, you must include an error-handling routine in your application.

If failover is a candidate for seamless, a connection will be reestablished with the server, Db2 Connect driver can throw SQLCODE -20542 to the calling application due to various reasons such as the target Db2 Server being down, or hitting system or profile parameter threshold. It is recommended that repeated failures with SQLCODE -20542 be investigated by Db2 administrators to determine the underlying cause for the server-side abends or errors when processing the related SQL.

If failover is not seamless and a connection is reestablished with the Db2 for z/OS data sharing group, the ACR connection error (SQL30108N) is returned to the application. All work that occurred within the current transaction is rolled back. The application must perform the following steps:
  1. Check the reason code that is returned with the SQL30108N error to determine whether special register settings on the failing data sharing member are carried over to the new (failover) data sharing member.
  2. Reset any special register values and global variables that were set after the last commit point.
  3. Re-create any session resources that were created in the previous failed connection. The session resources can include the following ones:
    • Open cursors with cursor-hold behavior
    • Open cursors with locators
    • Declared global temporary tables
    • Accelerated queries
  4. Rerun all uncommitted SQL operations that occurred during the previous transaction.
Failover connections to a Db2 for z/OS data sharing group are seamless when the following conditions are satisfied:
  • The CLI driver or IBM® Data Server Provider for .NET is used to connect to the Db2 for z/OS data sharing group.
  • If the connection has uncommitted statement or statements in a transaction and meets one of the following conditions:
    • The failure occurs on the first SQL statement in a transaction.
    • The first SQL statement is using chaining with MRI or retry on MRI enabled, and number of rows cannot exceed 100.
    • In a read-only transaction that uses the CLI driver, all the data in a result set is returned to the client, and a COMMIT or ROLLBACK statement is issued after the server becomes unreachable.
    • For an application that is connected to Db2 for z/OS Version 11 in new function mode (NFM), the transaction was read-only before the point of connection failure, and the detectReadonlyTxn keyword is set to true. The transaction cannot use the repeatable read (RR) or read stability (RS) isolation level.
  • The data server must allow transport reuse at the end of the previous transaction, with one exception. The exception is if transport reuse was not granted because you set the KEEPDYNAMIC keyword to YES.
  • There are no open cursors with cursor-hold behavior.
  • There are no open cursors with locators.
  • There are no declared global temporary tables.
  • There are no accelerated queries.
  • If the application uses the CLI driver, the application cannot perform actions that require the driver to maintain a history of previously called APIs to replay the SQL statement. Examples of such actions include specifying data at execution time (except when interleaved putdata is enabled), running a compound SQL statement, or using array input chaining.
  • With Db2 11.5.8 and later, seamless failover can also happen when the following conditions are met::
    • The COLUMWISE MRI CHAINING option is enabled for the driver.
    • CLI packages are bound with the KEEPDYNAMIC option on the target Db2 for z/OS server.
    • MRI is attempted on large object (LOB) data or XML data