DB2 pureScale Workload Balancing setup made simple

Body

Enabling Workload Balancing (WLB) for a DB2 pureScale instance is very easy.  There are two methods of WLB available for use: connection-level balancing and transaction-level balancing. The WLB feature is automatically on at the DB2 member server.  The client configuration is where WLB features are enabled.  With a quick modification of the db2dsdriver.cfg file or the java connection properties the WLB features can be exploited so that as members are added to the instance performance can almost scale linearly.

To find out if your DB2 client version can take advantage of Workload balancing follow this link:
https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.qb.server.doc/doc/r0056430.html

Java Clients:
To configure a IBM® Data Server Driver for JDBC and SQLJ client application the properties that enable workload balancing must be set. The WLB feature is not set by default.  Java client applications support transaction-level workload balancing. They do not support connection-level workload balancing.

For Java clients, obtain the connection from one of these four interfaces:
1) javax.sql.DataSource
2) javax.sql.ConnectionPoolDataSource
3) javax.sql.XADataSource
4) java.sql.DriverManager

Next set:
enableSysplexWLB = TRUE
maxTransportObjects = 100  ( or some sufficiently large value based on application needs )

Then connect to a DB2 pureScale instance.  Adding all the pureScale cluster members to the alternate server configuration is recommended to cover first connect failures.

Non-Java Clients:
Connection-level workload balancing by non-java clients is enabled by default.  The transaction-level workload balancing must be enabled explicitly. The connection properties that must be set to enable the workload balancing are in the db2dsdriver.cfg file.  A .NET application will use only the db2cli.ini or .NET object properties, not covered here.

db2dsdriver.cfg:   
    database section:
         connectionLevelLoadBalancing = TRUE ( this is the default so unless it needs to be specifically turned off it is not required )

    wlb section:
         enableWLB = TRUE ( To further enable transaction-level workload balancing set this to TRUE. This is off by default )

    example:
        <configuration>
          <dsncollection>
            <dsn alias="MYDB" name="MYDB" host="db2member0.mycompany.com" port="50000">
            </dsn>
          </dsncollection>
          <databases>
            <database name="MYDB" host="db2member0.mycompany.com" port="50000">
              <wlb>
                <parameter name="enableWLB" value="true" />
                <parameter name="maxTransports" value="100" />
              </wlb>
              <acr>
                <parameter name="enableAlternateServerListFirstConnect" value="true" />
                <alternateserverlist>
                  <server name="member1" hostname="db2member1.mycompany.com" port="50001" />
                  <server name="member2" hostname="db2member2.mycompany.com" port="50002" />
                </alternateserverlist>          
              </acr>
            </database>
          </databases>
        </configuration>

The automatic client reroute section, acr, is not required but recommended to be set.  Setting the alternate server list in the db2dsdriver.cfg file allows the client on first connect to be able to connect to any of the members in the pureScale cluster if the first connect fails.  The alternate server list with information about all the other members in the pureScale cluster is returned with the first successful connection attempt. This list is regularly refreshed at configurable intervals to reflect the current state of the pureScale cluster. The list contains the IP address, port number, and weight. The client stores the list in memory and also creates a local cache file, srvrlst.xml.