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

To configure Sysplex workload balancing and automatic client reroute (ACR) in a non-Java™ client application that connects directly to a Db2 for z/OS server, 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 group subset 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 CLI and embedded SQL applications, set the QueryTimeout keyword to a value that is less than the required guaranteed response time.
For .NET applications, set the CommandTimeout property to a value that is less than the required guaranteed response time.

For clients other than Java clients, use one of the listed clients or client packages to take advantage of Sysplex workload balancing:

IBM Data Server Client
IBM Data Server Runtime Client
IBM Data Server Driver Package
IBM Data Server Driver for ODBC and CLI

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)¹	The group IP address or domain name of the data sharing group.
	Database port (port)¹	The SQL port number for the Db2 location.
	Database name (name)¹	The Db2 location name that is defined during installation.
Group subset 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)¹	The group IP address or domain name of the data sharing group.
	Database port (port)¹	The port number for the Db2 location alias.
	Database name (name)¹	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.

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. 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. connectionLevelLoadBalancing should be used when the Sysplex distributor IP address is not provided.	true
detectReadonlyTxn	<acr>	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.	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
enableSeamlessFailoverErrorCodes	<acr>	Specifies whether the CLI Driver maps the non-seamless errors, such as SQL30108N, SQL20542N, to the communication error SQL30081N. If the property value is set to false and any non-seamless error happens during the execution, then the driver sends the communication error SQL30081N to the application. This new behavior is seen when the enableWLB option is set to true and the servers are of type Db2 for Linux, Unix, and Windows, or Db2 for z/OS. If the enableSeamlessFailoverErrorCodes property is set to true, then the application receives seamless error codes (SQL30108N, SQL20542N) in case of non-seamless failover. By default, the enableSeamlessFailoverErrorCodes property is set to true on Db2 servers, and false on Db2 for z/OS servers.	true
enableWLB	<wlb>	Specifies whether workload balancing is enabled. By default, the enableWLB keyword is set to false.	true

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):

tcpipConnectTimeout: The tcpipConnectTimeout 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 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).