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);
   ...