75 ways to demystify DB2 #65: Techtip : Is enableSysplexWLB a JDBC driver-wide (global) property or a connection level property?

Body

Is enableSysplexWLB (Sysplex Workload balancing JDBC driver property) a data source property or a driver wide (global) property?

Answer)
enableSysplexWLB is not a driver-wide or a global property, so you cannot set it in the JCC properties file. These are properties starting with "db2.jcc.*" and "db2.jcc.override.*". The "db2.jcc." properties provide a driver wide default value.  It is a Data source or connection level property, so it can only be enabled programmatically inside the application via a Connection or DataSource object.

enableSysplexWLB: Indicates whether the Sysplex workload balancing function of the IBM Data Server Driver for JDBC and SQLJ is enabled. The data type of enableSysplexWLB is boolean. The default is false.
    

More information about this property can be found here:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_r0052038.html?lang=en

1) enableSysplexWLB in the JDBC connection url:
 

import java.sql.*;
import java.util.*;
public class jcctest1{
public static void main(String[] args)
{
 try {
Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
String url = "jdbc:db2://localhost:50001/sample1:traceFile=jcctrc1.txt;traceLevel=-1;enableSysplexWLB=true;";
   Properties properties = new Properties();
   properties.put("user" , "xxxx");
   properties.put("password", "xxxx");
    Connection con = DriverManager.getConnection(url, properties);
    
    System.out.println("Connected");
   con.close();
  }
  catch (Exception ex) {
   System.out.println("Exception: " + ex);
   ex.printStackTrace();
  }
}
}

C:\Program Files\IBM\SQLLIB_01\java\jdk\bin>javac jcctest1.java

C:\Program Files\IBM\SQLLIB_01\java\jdk\bin>java jcctest1
Connected

JCC Trace snippet:

[jcc] Attempting connection to localhost:50001/sample1
[jcc] Using properties: { maxStatements=0, currentPackagePath=null,...queryTimeoutInterruptProcessingMode=1, alternateGroupServerName=null, clientRerouteAlternateSe enableSysplexWLB=true, useJDBC4ColumnNameAndLabelSemantics=0, recordTemporalHistory=0, NODEFDAC=no, sslConnection=null, dateFormat=1, OPTOFC=null, cliSchema=null, retryWithAlternativeSecurityMechanism=0, enableExtendedDescribe=0, DBSPACETEMP=null, enableExtendedIndicators=0, globalSessionVariables=null, PLOAD_LO_PATH=null, encryptionAlgorithm=0, keepAliveTimeOut=15, DBUPSPACE=null, cursorSensitivity=0, jdbcCollection=NULLID, fullyMaterializeInputStreams=false, currentSQLID=null, loginTimeout=0, useIdentityValLocalForAutoGeneratedKeys=false, OPTCOMPIND=null, LIGHT_SCANS=null, defaultIsolationLevel=2, deferPrepares=true, currentDegree=null, DUMPMEM=null, memberConnectTimeout=-1 }
[jcc] END TRACE_CONNECTS

2) enableSysplexWLB  as a connection level  property:
 

a) Driver Manager Interface

import java.sql.*;
import java.util.*;
public class jcctest2{
public static void main(String[] args)
{
 try {
Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
ResultSet rs;
String url = "jdbc:db2://localhost:50001/sample1:traceFile=jcctrc2.txt;traceLevel=-1;";
   Properties properties = new Properties();
   properties.put("user" , "xxxx");
   properties.put("password", "xxxx");
properties.put("enableSysplexWLB", "true");
    Connection con = DriverManager.getConnection(url, properties);
    
    System.out.println("Connected");
   con.close();
  }
  catch (Exception ex) {
   System.out.println("Exception: " + ex);
   ex.printStackTrace();
  }
}
}

====
C:\Program Files\IBM\SQLLIB_01\java\jdk\bin>javac jcctest2.java

C:\Program Files\IBM\SQLLIB_01\java\jdk\bin>java jcctest2
Connected

JCC Trace snippet:
[jcc] BEGIN TRACE_CONNECTS
[jcc] Attempting connection to localhost:50001/sample1
[jcc] Using properties: { maxStatements=0, currentPackagePath=null, currentLockTimeout=-2147483647,....enableSysplexWLB=true, useJDBC4ColumnNameAndLabelSemantics=0, recordTemporalHistory=0, NODEFDAC=no, sslConnection=null, dateFormat=1, OPTOFC=null, cliSchema=null, retryWithAlternativeSecurityMechanism=0, enableExtendedDescribe=0, DBSPACETEMP=null, enableExtendedIndicators=0, globalSessionVariables=null, PLOAD_LO_PATH=null, encryptionAlgorithm=0, keepAliveTimeOut=15, DBUPSPACE=null, cursorSensitivity=0, jdbcCollection=NULLID, fullyMaterializeInputStreams=false, currentSQLID=null, loginTimeout=0, useIdentityValLocalForAutoGeneratedKeys=false, OPTCOMPIND=null, LIGHT_SCANS=null, defaultIsolationLevel=2, deferPrepares=true, currentDegree=null, DUMPMEM=null, memberConnectTimeout=-1 }
[jcc] END TRACE_CONNECTS

===

b) Data source Interface:

import java.sql.*;
import java.util.*;
public class jcctest3{
public static void main(String[] args)
{
 try {

 // create data source
            com.ibm.db2.jcc.DB2SimpleDataSource ds = new com.ibm.db2.jcc.DB2SimpleDataSource();

            // set connection properties
            ds.setServerName("xxxx");
            ds.setPortNumber(50001);
            ds.setDatabaseName("sample1");
            ds.setDriverType(4);         
        ds.setEnableSysplexWLB(true);
        ds.setTraceDirectory("c:\\temp");
            ds.setTraceFile("trace3");
            ds.setTraceFileAppend(false);
            ds.setTraceLevel(com.ibm.db2.jcc.DB2BaseDataSource.TRACE_ALL);

            // get connection
            java.sql.Connection con = ds.getConnection("db2admin", "db2admin");
                
    System.out.println("Connected");
   con.close();
  }
  catch (Exception ex) {
   System.out.println("Exception: " + ex);
   ex.printStackTrace();
  }
}
}

C:\Program Files\IBM\SQLLIB_01\java\jdk\bin>javac jcctest3.java

C:\Program Files\IBM\SQLLIB_01\java\jdk\bin>java jcctest3
Connected

JCC Trace snippet:

[jcc] BEGIN TRACE_CONNECTS
[jcc] Attempting connection to mkkassey-pc:50001/sample1
[jcc] Using properties: { maxStatements=0, currentPackagePath=null, currentLockTimeout=-2147483647, timerLevelForQueryTimeOut=0, optimizationProfileToFlush=null, timeFormat=1, monitorPort=0, sendCharInputsUTF8=0, LOCKSSFU=null, alternateGroupDatabaseName=null, extendedTableInfo=0, sendDataAsIs=false, ..... enableSysplexWLB=true, useJDBC4ColumnNameAndLabelSemantics=0, recordTemporalHistory=0, NODEFDAC=no, sslConnection=null, dateFormat=1, OPTOFC=null, cliSchema=null, retryWithAlternativeSecurityMechanism=0, enableExtendedDescribe=0, DBSPACETEMP=null, enableExtendedIndicators=0, globalSessionVariables=null, PLOAD_LO_PATH=null, encryptionAlgorithm=0, keepAliveTimeOut=15, DBUPSPACE=null, cursorSensitivity=0, jdbcCollection=NULLID, fullyMaterializeInputStreams=false, currentSQLID=null, loginTimeout=0, useIdentityValLocalForAutoGeneratedKeys=false, OPTCOMPIND=null, LIGHT_SCANS=null, defaultIsolationLevel=2, deferPrepares=true, currentDegree=null, DUMPMEM=null, memberConnectTimeout=-1 }
[jcc] END TRACE_CONNECTS

-------
3) Tried to configure db2.jcc.enableSysplexWLB=true as a driver wide/global property in jcc.properties file,  however, enableSysplex=true is not picked up by the application, instead the default value of false was used as seen in the "Using Properties" section of the JCC trace

import java.sql.*;
import java.util.*;
public class jcctest4{
public static void main(String[] args)
{
 try {
Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
String url = "jdbc:db2://localhost:50001/sample1:traceFile=jcctrc3.txt;traceLevel=-1;";
   Properties properties = new Properties();
   properties.put("user" , "xxxx");
   properties.put("password", "xxxx");
    Connection con = DriverManager.getConnection(url, properties);
    
    System.out.println("Connected");
   con.close();
  }
  catch (Exception ex) {
   System.out.println("Exception: " + ex);
   ex.printStackTrace();
  }
}
}

=

jcc.properties:

db2.jcc.traceDirectory=c:\\temp
db2.jcc.traceFile=trace
db2.jcc.traceFileAppend=false
db2.jcc.traceLevel=-1
db2.jcc.enableSysplexWLB=true

C:\Program Files\IBM\SQLLIB_01\java\jdk\bin>javac jcctest4.java

C:\Program Files\IBM\SQLLIB_01\java\jdk\bin>java -Ddb2.jcc.propertiesFile=jcc.properties jcctest4
Connected

Trace snippet:
[jcc] Dumping all file properties: { db2.jcc.enableSysplexWLB=true, db2.jcc.traceFileAppend=false, db2.jcc.traceFile=trace, db2.jcc.traceDirectory=c:\temp, db2.jcc.traceLevel=-1 }
[jcc] END TRACE_DRIVER_CONFIGURATION
[jcc] BEGIN TRACE_CONNECTS
[jcc] Attempting connection to localhost:50001/sample1
[jcc] Using properties: { maxStatements=0, driverType=4,...., databaseName=sample1, serverName=localhost,  portNumber=50001, password=****,...
 readOnly=false, INFORMIXOPCACHE=null, useRowsetCursor=true, traceFileAppend=false, enableSysplexWLB=false, useJDBC4ColumnNameAndLabelSemantics=0, recordTemporalHistory=0, NODEFDAC=no, sslConnection=null, dateFormat=1, OPTOFC=null, cliSchema=null, retryWithAlternativeSecurityMechanism=0, enableExtendedDescribe=0, DBSPACETEMP=null, enableExtendedIndicators=0, globalSessionVariables=null, PLOAD_LO_PATH=null, encryptionAlgorithm=0, keepAliveTimeOut=15, DBUPSPACE=null, cursorSensitivity=0, jdbcCollection=NULLID, fullyMaterializeInputStreams=false, currentSQLID=null,.... }
[jcc] END TRACE_CONNECTS

Note:
Dumping all file properties => Properties set in the jcc.properties file

Using properties => Properties used by the application.

Thanks for reading!

Hope it is helpful.

Please leave a comment if you have a question or feedback.

~Mary Kassey