Example of enabling Db2 workload balancing support in non-Java clients

Db2 workload balancing requires a Db2 pureScale® environment. Before you can use Db2 workload balancing support in CLI, .NET, or embedded SQL applications, you need to update the db2dsdriver.cfg configuration file with the appropriate settings, and connect to a member of the Db2 pureScale environment.

The following example demonstrates setting up a CLI client to take advantage of Db2 workload balancing support.

Before you can set up the client, you need to configure a Db2 pureScale instance.

These steps demonstrate client setup:

1. Create a db2dsdriver.cfg file that enables transaction-level workload balancing. In this example:
   • If the first connection to the database fails, the connection needs to be tried on alternate servers.

     Note that if you do not define a list of alternate servers in the db2dsdriver.cfg configuration file, at the first successful connection to the server, the client obtains from the server a list of all available alternate servers. The client stores the list in memory and also creates a local cache file, srvrlst.xml that contains the server's list of 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.

   • For transaction-level workload balancing for this database, the maximum number of physical connections needs to be 80.
   • Connections can use the defaults for all other transaction-level workload balancing parameters.

   The db2dsdriver.cfg file looks like this:

   <configuration>
     <dsncollection>
       <dsn alias="LUWDS1" name="LUWDS1" host="luw1ds.toronto.ibm.com"
         port="50000">
       </dsn>
     </dsncollection>
     <databases>
       <!-- In this example, the host and port represent a member of a 
            Db2
   pureScale instance -->
       <database name="LUWDS1" host="luw.ds1.ibm.com" port="50000">
          <!-- database-specific parameters -->
          <wlb>
             <!-- Enable transaction-level workload balancing -->
             <parameter name="enableWLB" value="true" />
             <!-- maxTransports represents the maximum number of physical
                  connections -->
             <parameter name="maxTransports" value="80" />
          </wlb>
          <acr>
            <!-- acr is already enabled by default -->
            <!-- Enable server list for application first connect -->                    
            <parameter name="enableAlternateServerListFirstConnect"
              value="true" />
            <alternateserverlist>
              <!-- Alternate server 1 -->
              <server name="server1" hostname="luw2ds.toronto.ibm.com" port="50001">
              </server>
              <!-- Alternate server 2 -->                    
              <server name="server2" hostname="luw3ds.toronto.ibm.com" port="50002">
              </server>
              <!-- Alternate server 3 -->                    
              <server name="server3" hostname="luw4ds.toronto.ibm.com" port="50003">
              </server>
            </alternateserverlist>          
          </acr>
       </database>
     </databases>
   </configuration>

2. Suppose that the database name LUWDS1 represents a Db2 pureScale instance. In a CLI application, use code like this to connect to the Db2 pureScale instance:

   ...
      SQLHDBC         hDbc    = SQL_NULL_HDBC;
      SQLRETURN       rc      = SQL_SUCCESS;
      SQLINTEGER      RETCODE = 0;
      char            *ConnStrIn =
                      "DSN=LUWDS1;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);
   ...