Select connection failover provides failover protection for the following types of connections:
New connections, in the same way as described in "Connection Failover"
Lost connections, in the same way as described in "Extended Connection Failover"
In addition, the driver can recover work in progress because it keeps track of the last Select statement the application executed on each Statement handle, including how many rows were fetched to the client. For example, if the database had only processed 500 of 1,000 rows requested by a Select statement when the connection was lost, the driver would reestablish the connection to an alternate server, re-execute the Select statement, and position the cursor on the next row so that the driver can continue fetching the balance of rows as if nothing had happened.
Performance, however, is a factor when considering whether to use Select mode. Select mode incurs additional overhead when tracking what rows the application has already processed.
The driver only recovers work requested by Select statements. You must explicitly restart the following types of statements after a failover occurs:
Insert, Update, or Delete statements
Statements that modify the connection state, for example, SET or ALTER SESSION statements
Objects stored in a temporary tablespace or global temporary table
Partially executed stored procedures and batch statements
When in manual transaction mode, no statements are rerun if any of the operations in the transaction were Insert, Update, or Delete. This is true even if the statement in process at the time of failover was a Select statement.
By default, the driver verifies that the rows that are restored match the rows that were originally fetched and, if they do not match, generates an error warning your application that the Select statement must be reissued. By setting the Failover Granularity connection option, you can customize the driver to ignore this check altogether or fail the entire failover process if the rows do not match.
When the row comparison does not agree, the default behavior of Failover Granularity returns a SQL state of 40003 and an error message similar to:
Unable to position to the correct row after a successful failover attempt to AlternateServer: 'HOSTNAME=Server4:PORTNUMBER= 1521:SERVICENAME=test'. You must reissue the select statement.
If you have configured Failover Granularity to fail the entire failover process, the driver returns a SQL state of 08S01 and an error message similar to:
Your connection has been terminated and attempts to complete the failover process to the following Alternate Servers have failed: AlternateServer: 'HOSTNAME=Server4:PORTNUMBER= 1521:SERVICENAME=test'. All active transactions have been rolled back.
New connections, in the same way as described in "Connection Failover"