Application programming requirements for high availability connections to the Informix database server

The connection failover for the automatic client reroute feature can be seamless or non-seamless. You must include error handling routine in your application to handle the non-seamless failover to the Informix database server.

If failover is non-seamless, all the work that occurred within the current transaction is rolled back. Your application can include following tasks to the error handling routine for successful non-seamless connection failover:
  • Check the reason code that is returned with the 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 previous transaction.

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

  • The CLI driver, or IBM® .NET data provider is used to connect to the database server.
  • The connection does not have a pending uncommitted statement in a transaction. However, a seamless connection is possible in the following uncommitted statement conditions:
    • The failure occurs on the first SQL statement in a 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 fails over seamlessly when you issue a COMMIT or ROLLBACK statement after the server becomes unreachable.
  • The data server must allow 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 include specifying data at execution time, running compound SQL, or use of array input.
  • The application is not a stored procedure.
  • Autocommit is not enabled.
    Note: Although the seamless connection failover can occur when autocommit is enabled, this situation can potentially cause previously committed SQL statement to be reissued when connection is reestablished. For example, if the connection to the database server is disconnected before acknowledgment of the commit operation is sent back to the client, when the client reestablishes the connection, client replays the previously committed SQL statement. The result is that the SQL statement is executed twice. To avoid this situation, turn autocommit off when you enable seamless failover.