DB2 10.5 for Linux, UNIX, and Windows

Configuration of DB2 for Linux, UNIX, and Windows automatic client reroute support for applications other than Java

For connections to DB2® for Linux, UNIX, and Windows databases, the process for configuration of automatic client reroute support for applications other than Java™ is the same for DB2 pureScale® environment and other environments.

Automatic client reroute capability is enabled by default. You must connect to a DB2 pureScale instance in a DB2 pureScale environment or to the primary server in other environments.

At the first successful connection to the server, the client obtains a list from the server of all the available alternate servers, which the client stores in memory. If the first connection fails, the client checks for a list of alternate servers that is defined in the db2dsdriver.cfg file, in the <acr> section under the <alternateserverlist> tag. If the db2dsdriver.cfg file has no alternate servers that are defined in the <acr> section, upon the first successful connection to the server, the client creates a local cache file, srvrlst.xml. The client updates the file with the server's list of available alternate servers. This file is refreshed whenever a new connection is made and the server's list differs from the contents of the client srvrlst.xml file.

When a client uses the srvrlst.xml file to locate an alternate server, it writes a record in the db2diag.log file. You can monitor this log to determine how frequently initial server connections fail.

Table 1 describes the basic settings to establish a connection for applications other than Java.

Table 1. Basic settings to establish a connection to a DB2 for Linux, UNIX, and Windows database for applications other than Java
Client setting	Value for a DB2 pureScale environment	Value for other environments
Database host (host)¹	The IP address of a member of a DB2 pureScale instance²	The IP address of the primary server
Database port (port)¹	The SQL port number of a member of a DB2 pureScale instance²	The SQL port number of the primary server
Database name (name)¹	The database name	The database name
Note: Depending on the client that you use, connection information is defined in one of several possible sources: If you are connecting with a CLI application, or an open source application that uses the IBM® Data Server Client or IBM Data Server Runtime Client, you can define connection information as follows: If you provide host, port, and database information in the connection string in an application, the CLI driver and the open source driver uses that information. If host, port, and database information is not provided in the connection string in an application but the information is in the db2cli.ini file, the driver uses the information in that file. If host, port, and database information is not provided in the connection string in the application or the db2cli.ini file, the driver uses information in the IBM data server driver configuration file (db2dsdriver.cfg). If you are using a .NET application with the IBM Data Server Client or the IBM Data Server Runtime Client, you can define connection information in the database catalog, connection string, db2dsdriver.cfg file, or .NET object properties. If you are using an embedded SQL application with the IBM Data Server Client or the IBM Data Server Runtime Client, connection information can be defined in the database catalog, connection string, or db2dsdriver configuration file. Alternatively, you can use a distributor, such as WebSphere® Application Server Network Deployment or multihomed DNS, to establish the initial connection to the database: For a distributor, you specify the IP address and port number of the distributor. The distributor analyzes the current workload distribution and uses the information to forward the connection request to one of the members of the DB2 pureScale instance. For multihomed DNS, you specify an IP address and port number that can resolve to the IP address and port number of any member of the DB2 pureScale instance. Multihomed DNS processing selects a member based on criteria, such as simple round-robin selection or member workload distribution.

You can set configuration keywords or registry variables in the IBM data server driver configuration file (db2dsdriver.cfg) to modify the automatic client reroute behavior. The configuration keywords that you can use to control automatic client reroute are in Table 2. The keywords are described for the case in which client affinities are not enabled.

Table 2. Settings to control automatic client reroute behavior
Element in the <acr> section of the db2dsdriver configuration file	Value
acrRetryInterval keyword	Specifies the number of seconds to wait between consecutive connection retries. The value of the DB2_CONNRETRIES_INTERVAL registry variable overrides this value. The range of the acrRetryInterval keyword is 0 - the maximum integer value (`MAX_INT`). If you do not set the DB2_CONNRETRIES_INTERVAL registry variable, the default value of the acrRetryInterval keyword is no wait (0).
alternateserverlist keyword	Specifies a set of server names and port numbers that identify alternate servers to which a connection is attempted if a failure occurs on the first connection to the database. The alternate server list is not used after the first connection. In a DB2 pureScale environment, the entries in the list can be members of a DB2 pureScale instance. In other environments, the list contains an entry for the primary server and an entry for the high availability disaster recovery (HADR) standby server.
detectReadonlyTxn keyword	Specifies whether a connection to a new member can seamlessly fail over with the automatic client reroute feature enabled, even if the failed statement is not the first SQL statement in a transaction. The detectReadonlyTxn keyword is valid when you are connecting to a DB2 Cancun Release 10.5.0.4 or later server. The default value of the detectReadonlyTxn keyword is true for connection to supported DB2 for Linux, UNIX, and Windows servers. The true value forces the connected server to return the current values of special registers whenever they are modified. Restriction: You cannot set the detectReadonlyTxn keyword to true in a transaction with the repeatable read (RR) or read stability (RS) isolation level.
enableAcr keyword	Specifies whether automatic client reroute is in effect. The default is true.
enableAlternateGroupSeamlessACR keyword	Specifies seamless or non-seamless failover behavior across groups. The default is false. You must define this keyword in the <alternategroup> element in the <acr> section. To set this keyword to true, you must also set the enableSeamlessACR keyword to true. Setting the enableAlternateGroupSeamlessACR keyword to true does not affect the setting of the enableSeamlessACR keyword. If a successful connection is established to a server in the `alternategroup` section, the rules for seamless or non-seamless behavior still apply.
enableAlternateServerListFirstConnect keyword	Specifies whether there is an alternate server list that is used only if a failure occurs on the first connection to the data server. The default is false. When the value of the enableAlternateServerListFirstConnect keyword is true, automatic client reroute with seamless failover is implicitly enabled, regardless of the other settings that you specify for automatic client reroute in the db2dsdriver.cfg file. To use this feature, you also require a <alternateserverlist> element in the db2dsdriver.cfg file.
enableSeamlessAcr keyword	Specifies whether seamless failover can occur. If the enableAcr keyword is set to true, the default for the enableSeamlessAcr keyword is true. The enableSeamlessACR keyword applies only to the members within a group or cluster.
maxAcrRetries keyword	Specifies the maximum number of connection attempts for automatic client reroute. The valid range is 0 - the maximum integer value (`MAX_INT`). The value of the DB2_MAX_CLIENT_CONNRETRIES registry variable overrides the value of the maxAcrRetries keyword. If you do not set the DB2_MAX_CLIENT_CONNRETRIES registry variable or maxAcrRetries keyword, by default, the connection is tried again for 10 minutes. However, if you defined alternate groups, by default, a connection is attempted for 2 minutes. Setting the maxAcrRetries keyword value to 0 disables automatic client reroute.

In case of changes to the db2dsdriver.cfg file, your CLI application can issue the SQLReloadConfig function to validate the entries for all alternate servers within the <acr> section.

The registry variables in Table 3 control the retry behavior of automatic client reroute.

Table 3. Registry variables to control automatic client reroute retry behavior
Registry variable	Value
DB2_CONNRETRIES_INTERVAL	The number of seconds between consecutive connection retries. The default is 10 if you set the DB2_MAX_CLIENT_CONNRETRIES registry variable.
DB2_MAX_CLIENT_CONNRETRIES	The maximum number of connection retries for automatic client reroute. The default is 30 if you set the DB2_CONNRETRIES_INTERVAL registry variable.

If you do not set either registry variable and also do not set the maxAcrRetries and acrRetryInterval keywords, automatic client reroute processing tries the connection to a database again for up to 10 minutes, with no wait between retries.

For CLI, OLE DB, ADO.NET, and embedded SQL applications, there are two connection timeout keywords, which you can set in the IBM data server driver configuration file (db2dsdriver.cfg):

tcipipConnectionTimeout: The tcipipConnectionTimeout keyword specifies the number of seconds before an attempt to open a socket fails. Do not use this keyword with automatic client reroute.
ConnectionTimeout: The ConnectionTimeout keyword specifies the number of seconds that a client application waits for a connection to a DB2 for z/OS® data sharing group to be established.

The ConnectionTimeout keyword setting takes precedence over the tcpipConnectTimeout and MemberConnectTimeout keyword settings.

For embedded SQL, CLI, OLE DB, and ADO.NET applications, you can set a connection timeout value by setting the ConnectionTimeout keyword. That value specifies the number of seconds that the client application waits for a connection to a database to be established. You must set the connection timeout value to a value that is equal to or greater than the maximum time that it takes to connect to the server. Otherwise, the connection might time out and be rerouted to the alternate server by client reroute. For example, if on a normal day it takes approximately 10 seconds to connect to the server and on a busy day it takes approximately 20 seconds, you should set the connection timeout value to at least 20 seconds.