Work Load Balancing
In order to utilize DB2 automatic Work Load Balancing for JBoss application, some changes need to be made in the JBoss JDBC driver manager configuration.
Myth – Most people think that work load balancing is done by the DB2 pureScale and it is not true. The work load balancing is done by the DB2 drivers. DB2 pureScale does only one thing – It returns a list of DB2 members available and how busy they are and then DB2 JCC driver does the workload balancing based upon this information. DB2 pureScale member once it receives a work (S/I/U/D), it has no way of rerouting that work to another DB2 member so the real work load balancing is done by the drivers and not by DB2 pureScale or even DB2.
Locate the XML file that describes the driver properties and add connection level properties so that JDBC driver does the Work Load Balancing.
Here is an example:
<?xml version="1.0" encoding="UTF-8"?> <datasources> <local-tx-datasource> <jndi-name>DB2Prod</jndi-name> <driver-class>com.ibm.db2.jcc.DB2Driver</driver-class> <connection-url>jdbc:db2://10.10.10.10:53000/PSDB</connection-url> <!-- Set Current Schema --> <connection-property name="currentSchema">PS</connection-property> <!-- JDBC Tracing - Comment when not needed. Can generate huge files --> <connection-property name="traceDirectory">/tmp</connection-property> <connection-property name="traceFile">jcc.trace</connection-property> <connection-property name="traceLevel">-1</connection-property> <!-- Workload Balancing Parameters --> <connection-property name="enableSysplexWLB">true</connection-property> <!-- Client Identification Parameters --> <connection-property name="clientProgramName">MyPureScaleApp</connection-property> <connection-property name="clientWorkstation">Serv1</connection-property> <!-- Automatic Client Reroute Parameters --> <connection-property name="clientRerouteAlternateServerName"> 10.10.10.11,10.10.10.12,10.10.10.13 </connection-property> <connection-property name="clientRerouteAlternatePortNumber"> 53000,53000,53000 </connection-property> <connection-property name="maxRetriesForClientReroute">1</connection-property> <connection-property name="retryIntervalForClientReroute">1</connection-property> <user-name>PS</user-name> <password>password</password> <idle-timeout-minutes>15</idle-timeout-minutes> <min-pool-size>10</min-pool-size> <max-pool-size>200</max-pool-size> <!-- sql to call on an existing pooled connection when it is obtained from pool --> <new-connection-sql>select 1 from sysibm.sysdummy1</new-connection-sql> <!-- <security-domain>EncryptedSuperPnrDbRealm</security-domain> --> <metadata> <type-mapping>DB2</type-mapping> </metadata> </local-tx-datasource> </datasources>
Explanation of WLB Properties
enableSysplexWLB – The connection of the application to the data server chooses a transport based on the weights that are returned by the data server. A transport is chosen at every transaction boundary. This action balances the load on different DB2 data sharing members.
The list of data sharing members are returned by DB2 pureScale at every db2.jcc.maxRefershInterval which has a default value of 10 seconds. You can run command db2pd -d <dbname> -serverlist and this is the list returned to the driver. Please note that if the output from this db2pd command shows server names as FQDN, you have to make sure that your DNS server is able to resolve the names to the IP addresses.
clientRerouteAlternateServerName – The server list is returned by the DB2 server when enableSysplexWLB is set to true and that information is used to balance the workload and rerouting connections to the other members if one is not available. However at the start of the application, it does not know what alternate servers are available so use this property to define the names / IP addresses of the other DB2 members. Please note that this information is used only once at the start of the application. If the member which it is connecting to is down then it picks up the alternate server from this list otherwise it uses the information from the output of the db2pd -d <dbname> -serverlist.
clientRerouteAlternatePortNumber – The port number for the other DB2 members.
maxRetriesForClientReroute – The number of times to retry the connection to each server, including the primary server, after a connection to the primary server fails. The default is 3. For fast failover, I usually set this to one.
retryIntervalForClientReroute – The number of seconds to wait between retries. The default is no wait. I usually set this to 1 seconds for pureScale.
Some Global Properties for Work Load Balancing that affects the behaviour.
db2.jcc.maxRefershInterval – Specifies the maximum amount of time in seconds between refreshes of the client copy of the server list that is used for workload balancing. The default is 10. The minimum valid value is 1. If default value is used, driver will ask DB2 – "How many members do you have and how busy they are?"
db2.jcc.maxTransportObjectIdleTime – Specifies the maximum elapsed time in number of seconds before an idle transport is dropped. The default is 10. The minimum supported value is 0. When you start JBoss and you may see list of connections in each DB2 member if you do db2 list applications but you may notice that they all disappear after a while. This is due to this parameter. If your JBoss configuration is setting 15 minutes of time out then set this parameter to 900 seconds.
db2.jcc.maxTransportObjectWaitTime – Specifies the number of seconds that the client will wait for a transport to become available. The default is 1. The minimum supported value is 0.
db2.jcc.minTransportObjects – Specifies the lower limit for the number of transport objects in a global transport object pool. The default value is 0. Any value that is less than or equal to 0 means that the global transport object pool can become empty. I usually set this to the number of connections in the connection pool that the JBoss uses so that these connections are created upfront.
How do you set above properties? Unfortunately, any property that starts with db2.jcc are global properties and they affect all connections so they can not be defined as a part of the connection properties and have to defined in a property file which can be picked up by the JCC driver.
There are two way to do this.
1. Define these properties in DB2JccConfiguration.properties file and include the directory that contains DB2JccConfiguration.properties in the CLASSPATH concatenation. If this file is put in a JAR, convert it to UNICODE before putting in the JAR file and the JAR file must be in the CLASSPATH concatenation.
2. Define these properties in a file of your choice and include that file using JVM params. For example: use -Ddb2.jcc.propertiesFile=/path/jcc.props
Where the contents of either DB2JccConfiguration.properties or jcc.props are as shown:
db2.jcc.maxRefershInterval=10 db2.jcc.maxTransportObjectIdleTime=900 db2.jcc.maxTransportObjectWaitTime=2 db2.jcc.minTransportObjects=10
Last Thought:
If you want to set these values individually at the individual connection level rather than setting them up at the global level, you may add these connection level properties in the JBoss configuration. (But, I have not tested this so cannot say if this is correct or not.)
<connection-property name="MaxRefershInterval">10</connection-property> <connection-property name="MaxTransportObjectIdleTime">900</connection-property> <connection-property name="MaxTransportObjectWaitTime">2</connection-property> <connection-property name="MinTransportObjects">10</connection-property>