Application programming requirements for high availability connections to Db2 servers

A connection failover with the automatic client reroute feature can be seamless or non-seamless.

If the connection failover is non-seamless, all the work that occurred within the current transaction is rolled back. To handle a non-seamless failover, you must include an error-handling routine in your application. The error-handling routine must include the following steps:
  • 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.
  • Reset any special register values that are not current.
  • Rerun all uncommitted SQL operations that occurred during the failed transaction.

The following conditions must be satisfied for the failover connections to the Db2 server to be seamless:

  • You must use the CLI driver or IBM® Data Server Provider for .NET to connect to the database server.
  • You must enable both the enableAcr and enableSeamlessAcr keywords.
  • If the connection has uncommitted statement or statements in a transaction and meets one of 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.
    • If the connection failure occurs in a transaction that was read-only before the point of connection failure, a seamless connection is possible if all the following conditions are met:
      • Application is connecting to a Db2 Cancun Release 10.5.0.4 or later server.
      • 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 transaction-level load balancing is enabled, the data server allows transport reuse at the end of the previous transaction.
  • All global temporary tables are closed or dropped.
  • There are no open or held cursors.
  • If the CLI driver is used, the application cannot perform any actions that require the driver to maintain a history of previously called APIs in order to replay the SQL statement. Examples of action that requires the driver to maintain a history include specifying data at execution time, running compound SQL statements, or use of array input.
  • The application is not a stored procedure.

Avoid enabling autocommit. Although seamless connection failover can occur when autocommit is enabled, this situation might cause previously committed SQL statements to be reissued.