DB2 10.5 for Linux, UNIX, and Windows

Configuration of Sysplex workload balancing and automatic client reroute for applications other than Java

To configure a client application other than a Java™ application that connects directly to a DB2® for z/OS® server to use Sysplex workload balancing and automatic client reroute (ACR), set keyword values in the IBM® data server driver configuration file (db2dsdriver.cfg). These keyword values specify a connection to an address that represents the data sharing group (for group access) or a subset of the data sharing group (for member-specific access) and that enables Sysplex workload balancing and automatic client reroute.

To configure high availability support, you can configure the Sysplex workload balancing and automatic client reroute features together. When you configure a client to use the Sysplex workload balancing feature, the automatic client reroute feature is enabled by default. If the automatic client reroute feature is enabled with the Sysplex workload balancing feature, you need to change keyword values that are related to automatic client reroute only to fine-tune automatic client reroute operation.

You can enable the workload balancing (WLB) feature by setting the enableWLB keyword to true. If you enable the WLB feature, it is important to set timeout values as follows to help ensure a timely response:
For clients other than Java clients, use one of the listed clients or client packages to take advantage of Sysplex workload balancing:
Important: To establish direct connections to a DB2 for z/OS data sharing group by using the Sysplex workload balancing feature, you need either a DB2 Connect™ server product installation or DB2 Connect server license file in the license directory of the DB2 installation path.

Table 1 describes the basic configuration settings that are necessary to enable Sysplex workload balancing for applications other than Java applications.

Table 1. Basic settings to enable Sysplex workload balancing for applications other than Java applications
Data-sharing access method Client setting Value
Group access The enableWLB keyword in the <wlb> section of the IBM data server driver configuration file (db2dsdriver.cfg) true: The default value that enables the WLB feature.
Database host (host)1 The group IP address or domain name of the data sharing group.
Database port (port)1 The SQL port number for the DB2 location.
Database name (name)1 The DB2 location name that is defined during installation.
Member-specific access The enableWLB keyword in the <wlb> section of the IBM data server driver configuration file (db2dsdriver.cfg) true: The default value that enables the WLB feature.
Database host (host)1 The group IP address or domain name of the data sharing group.
Database port (port)1 The port number for the DB2 location alias.
Database name (name)1 The DB2 location alias that represents a subset of the members of the data sharing group.
Remember: The open source driver utilizes the CLI driver under the covers.
  1. Depending on the DB2 product and driver that you use, connection information can be obtained from one of several possible sources:
    • In a scenario that involves a CLI or open-source application with anIBM data server client, connection information can be obtained from the following sources:
      • If host, port, and database information is provided in the connection string of the application, the CLI driver uses that information to establish a connection.
      • If only database name is specified in the connection string of the application, the CLI driver uses information from the database catalog.
      • If host and port information is not provided in the connection string of the application or database catalog, the CLI driver searches for required information in the db2cli.ini file. If the driver finds that information, it uses it to establish a connection.
      • If host and port information is not provided in the connection string of the application, the database catalog, or the db2cli.ini file, the CLI driver uses information in the IBM data server driver configuration file (db2dsdriver.cfg).
    • In a scenario that involves a CLI or open-source application with an IBM data server driver, connection information can be obtained from following sources:
      • If host, port, and database information is provided in the connection string of the application, the CLI driver uses that information to establish a connection.
      • If host and port information is not provided in the connection string of the application, the CLI driver searches for the required information in the db2cli.ini file. If the driver finds that information, it uses it to establish a connection.
      • If host and port information is not provided in the connection string of the application or the db2cli.ini file, the CLI driver uses information in the IBM data server driver configuration file (db2dsdriver.cfg).
    • In a scenario that involves a .NET application with a IBM data server client, connection information can be obtained from following sources:
      • If host, port, and database information is provided in the connection string of the application, the .NET data provider uses that information to establish a connection.
      • If host, port and database information is provided through .NET object properties, the .NET data provider uses that information to establish a connection.
      • If only database name is specified in the connection string of the application, the .NET data provider uses information from the database catalog.
      • If host and port information is not provided in the connection string of the application, .NET object properties, or the database catalog, the .NET data provider uses information in the IBM data server driver configuration file (db2dsdriver.cfg).
    • In a scenario that involves a .NET application with an IBM data server driver, connection information can be obtained from following sources:
      • If host, port, and database information is provided in the connection string of the application, the .NET data provider uses that information to establish a connection.
      • If host, port, and database information is provided through .NET object properties, the .NET data provider uses that information to establish a connection.
      • If host and port information is not provided in the connection string of the application, the .NET data provider uses information in the IBM data server driver configuration file (db2dsdriver.cfg).

To fine-tune Sysplex workload balancing, additional keywords are available. The additional keywords for applications other than Java applications are listed in Table 2.

Table 2. Properties for fine-tuning Sysplex workload balancing for direct connections from applications other than Java applications to DB2 for z/OS
Keyword in the IBM data server driver configuration file (db2dsdriver.cfg) Section in the IBM data server driver configuration file (db2dsdriver.cfg) Description
maxRefreshInterval <wlb> Specifies the maximum elapsed time in seconds before the server list is refreshed. The default is 10. The minimum supported value is 0.
maxTransportIdleTime <wlb> Specifies the maximum elapsed time in seconds before an idle transport is dropped. The default is 60. The minimum supported value is 0.
maxTransports <wlb> Specifies the maximum number of physical connections (or transports) that the requester can make to the data sharing group. A maximum of one transport per member can be opened for each application connection. For example, 25 application connections that access a six-member group can open a maximum of 150 transports to the group. That means that six transports, one to each member, can be opened for each application connection.

The default value of the maxTransports keyword is 1000. The value of -1 specifies the use of as many transports as necessary based on the number of application connections and members in use.
maxTransportWaitTime <wlb> Specifies the number of seconds that the client waits for a transport to become available. The default value is 1 second. Specifying the value as -1 means unlimited wait time. The minimum supported wait time is 0.

Automatic client reroute capability is enabled in a client by default when the WLB feature is enabled. At the first connection to the server, the client obtains a list of servers from the connected group. The server list contains all available servers with the capacity to run work. The server list might not contain all members of the DB2 data sharing group because only the available servers with capacity to run work are included in the list. Other than the case where the server list is used to connect to the alternate servers, a connection to a DB2 data sharing group uses the z/OS Sysplex distributor that is configured with the distributed dynamic virtual IP address. The z/OS Sysplex distributor uses the dynamic virtual IP address (VIPA) and automatic VIPA takeover to distribute incoming connections among the DB2 subsystems within the Sysplex environment. The z/OS Sysplex workload balancing feature helps ensures the high availability of a DB2 data sharing group.

To control automatic client reroute behavior, you can set configuration keywords and registry variables in the IBM data server driver configuration file (db2dsdriver.cfg) . The keyword descriptions in Table 3 are for the case in which client affinities are not enabled. If the IBM data server driver configuration file (db2dsdriver.cfg) changes, your CLI application can invoke the SQLReloadConfig function to validate the entries for all alternate servers within the <acr> section.

Table 3. Settings to control automatic client reroute behavior
Keyword in the <acr> section of the db2dsdriver.cfg configuration file Value
acrRetryInterval Specifies the number of seconds to wait between consecutive connection attempts. The valid range is 0 - maximum integer value. The value of the DB2_CONNRETRIES_INTERVAL registry variable overrides the value of the acrRetryInterval keyword. The default is 0 (no wait) if you do not set the DB2_CONNRETRIES_INTERVAL registry variable. If you enable automatic client reroute to a DB2 for z/OS data sharing group, the default value of no wait is recommended.
detectReadonlyTxn Specifies whether a connection can seamlessly fail over to a new member when the automatic client reroute feature is enabled, even if the failed statement is not the first SQL statement in a transaction. You can specify the detectReadonlyTxn keyword for a connection to DB2 for z/OS Version 11 in new function mode (NFM) with the automatic client reroute and Sysplex workload balancing features enabled. The default value of the detectReadonlyTxn keyword is false for connection to supported DB2 for z/OS servers.
Setting the detectReadonlyTxn keyword to true forces the connected server to return the latest values of special registers and session global variables 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 Specifies whether automatic client reroute is in effect. The default is true.
enableSeamlessAcr 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. When you enable automatic client reroute to a DB2 for z/OS data sharing group, you must ensure that the enableSeamlessACR keyword is set to the default value of true and that the application can handle the SQL30108N exception.
maxAcrRetries Specifies the maximum number of connection attempts for automatic client reroute. The valid range is 0 - 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, one retry of each server in the server list and the group IP address is attempted. Setting the value of the maxAcrRetries keyword to 0 disables ACR.

The registry variables in Table 4 control retry behavior for automatic client reroute.

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

When enabling automatic client reroute in a connection to a DB2 for z/OS data sharing group, set the maxAcrRetries keyword. If you do not set both the DB2_MAX_CLIENT_CONNRETRIES and DB2_CONNRETRIES_INTERVAL registry variables or do not set both the maxAcrRetries and acrRetryInterval keywords, automatic client reroute attempts to connect to a z/OS group for up to 10 minutes, with no wait between attempts.

For CLI, OLE DB, ADO.NET, and embedded SQL applications, there are three connection timeout keywords, which you can set in the IBM data server driver configuration file (db2dsdriver.cfg):
MemberConnectTimeout
The MemberConnectTimeout keyword specifies the number of seconds that a client application waits before being routed to the next IP address in the server list. When you enable automatic client reroute for connections to a DB2 for z/OS data sharing group, you should use the MemberConnectTimeout keyword to manage the time to wait before rerouting. The default value of the MemberConnectTimeout keyword is 1 second. In most cases, the default value is adequate, but you might require a higher MemberConnectTimeout value to prevent frequent network timeouts.
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.

If you must use the Sysplex workload balancing feature but your applications cannot handle the errors that are returned for automatic client reroute processing, set the following keywords in the IBM data server driver configuration file (db2dsdriver.cfg).

Table 5. Keywords for enabling only Sysplex workload balancing for connections from applications other than Java applications to DB2 for z/OS
Keyword in the IBM data server driver configuration file (db2dsdriver.cfg) Section in the IBM data server driver configuration file (db2dsdriver.cfg) Description Value to set
connectionLevelLoadBalancing <database> Specifies whether connection-level load balancing is in effect. By default, if the enableWLB configuration keyword is set to true, the connectionLevelLoadBalancing keyword is set to true. Otherwise, the default value of the connectionLevelLoadBalancing keyword is false. true
enableAcr <acr> Specifies whether automatic client reroute is enabled. For CLI or .NET applications, enabling automatic client reroute automatically enables seamless failover. By default, if the enableWLB keyword is set to true, the enableAcr keyword is set to true. Otherwise, the default of theenableAcr keyword is false. If your application cannot handle the seamless failover exception (SQL30108N), set the enableAcr keyword to false if you set the enableWLB keyword to true. true
enableAlternateGroupSeamlessACR <acr> Specifies seamless or non-seamless failover behavior across groups. The default is false. To set this keyword to true, you must also set the enableSeamlessACR configuration keyword to true. Setting the enableAlternateGroupSeamlessACR keyword to true does not affect the setting of the enableSeamlessACR keyword. If a connection is established to a server in the alternategroup subsection, the rules for seamless or non-seamless behavior still apply. true
enableSeamlessAcr <acr> Specifies whether seamless failover is enabled. Seamless failover is supported only for Java, CLI, and .NET applications. By default, the enableSeamlessAcr keyword is set to the same value as the enableAcr keyword. true
enableWLB <wlb> Specifies whether workload balancing is enabled. By default, the enableWLB keyword is set to false. true
Related concepts:
IBM data server driver configuration file