Example of enabling Db2 for z/OS Sysplex workload balancing and automatic client reroute in non-Java client applications

Before you can use Sysplex workload balancing and automatic client reroute in applications other than Java™ applications that connect directly to Db2 for z/OS servers, you need to update the db2dsdriver.cfg configuration file with the appropriate settings, and connect to a data sharing group.

Before you can set up the client, you need to configure the listed server software:

WLM for z/OS
For workload balancing to work efficiently, Db2 work needs to be classified. Classification applies to the first non-SET SQL statement in each transaction. Among the areas by which you need to classify the work are:
- Authorization ID
- Client info properties
- Stored procedure name
  The stored procedure name is used for classification only if the first statement that is issued by the client in the transaction is an SQL CALL statement.
For a complete list of classification attributes, search IBM® documentation for the classification attributes topic in your specific Db2 for z/OS server version.
Db2 for z/OS, set up for data sharing

Automatic client reroute capability is enabled in a client by default when the Sysplex workload balancing (WLB) feature is enabled. At the first successful connection to the server, the client obtains a list of all available servers (server list) from the connected group. The server list contains all available servers with capacity to run work. The server list may not contain all members of the Db2 data sharing group as only the available servers with capacity to run work are included in the list. Other than the case where server list is used to connect to the alternate servers, connection to a Db2 data sharing group uses the z/OS Sysplex distributor 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 connection among the Db2 subsystems within the Sysplex environment. The z/OS Sysplex feature ensures the high availability of a Db2 data sharing group.

You can fine-tune default automatic client reroute feature by modifying these items:

Automatic client reroute characteristic	db2dsdriver.cfg configuration keyword	Desired value
Number of times to try connecting to the alternate server	maxAcrRetries	< 6
Number of seconds to wait between tries	acrRetryInterval	0 (default)

The example demonstrates how to set up a client application other than a Java application to take advantage of Sysplex and automatic client reroute high availability support.

Create a db2dsdriver.cfg file with the basic settings for Sysplex support and automatic client reroute. When you set enableWLB and enableAcr to true, you enable Sysplex workload balancing and automatic client reroute capabilities.

<configuration>
  <dsncollection>
    <dsn alias="DSGROUP1" name="DSGROUP1" 
      host="db2a.sysplex1.ibm.com" port="446">
    </dsn>
  </dsncollection>
  <database name="DSGROUP1" host="db2a.sysplex1.ibm.com" port="446">
   <!-- database-specific parameters -->
   <wlb>
      <!-- Enable Sysplex workload balancing to get
      automatic client reroute
      functionality -->
      <parameter name="enableWLB" value="true" />
      <!-- maxTransports represents the maximum number of transports -->
      <parameter name="maxTransports" value="80" />
   </wlb>
   <acr>
      <parameter name="enableAcr" value="true">
      </parameter>
      <parameter name="maxAcrRetries" value="5">
      </parameter>
      <parameter name="acrRetryInterval" value="0">
      </parameter>
   </acr>
  </database>
</configuration>

Suppose that database name DSGROUP1 represents a data sharing group that is set up for group access. In a CLI application, you can use the following sample code to connect to the data sharing group:

...
   SQLHDBC         hDbc    = SQL_NULL_HDBC;
   SQLRETURN       rc      = SQL_SUCCESS;
   SQLINTEGER      RETCODE = 0;
   char            *ConnStrIn =
                   "DSN=DSGROUP1;PWD=mypass";
                /* dsn matches the database name in the configuration file */
   char            ConnStrOut [200];
   SQLSMALLINT     cbConnStrOut;
   int             i;
   char            *token;
...
  /*****************************************************************/
  /* Invoke SQLDriverConnect                                       */
  /*****************************************************************/
   RETCODE = SQLDriverConnect (hDbc                 ,
                               NULL                 ,
                               (SQLCHAR *)ConnStrIn ,
                               strlen(ConnStrIn)    ,
                               (SQLCHAR *)ConnStrOut,
                               sizeof(ConnStrOut)   ,
                               &cbConnStrOut        ,
                               SQL_DRIVER_NOPROMPT);
...