Friday, April 3, 2009

DB2 JDBC Driver Secrets

There are several ways one can do DB2 JDBC tracing and it all really depends how creative you are.

Check version of JCC Driver

$ java com.ibm.db2.jcc.DB2Jcc -version

Simple nonsense solution to use JCC Tracing

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

import com.ibm.pdq.annotation.Sql;

public class T4Driver {

public static void main(String[] args) throws Exception {

Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
String url = "jdbc:db2://localhost:50000/SAMPLE:traceFile=c:/jdbc4.log;TraceLevel=TRACE_ALL;";
Connection con = DriverManager.getConnection(url, "pdqpot", "pdqpot123");
@Sql String sql = "VALUES CURRENT TIMESTAMP";
java.sql.PreparedStatement stmt = con.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();

String ts = "";
while (rs.next()) {
ts = rs.getString(1);
System.out.println("Timestamp: " + ts);
}
rs.close();
stmt.close();
}
}

The trick here is to add connection properties in the connection string and this is what probably most of your search results will show.

String url = "jdbc:db2://localhost:50000/SAMPLE:traceFile=c:/jdbc4.log;TraceLevel=TRACE_ALL;";

Simple no-nonsense solution to use Tracing using property file

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.Properties;

import com.ibm.db2.jcc.DB2Connection;
import com.ibm.pdq.annotation.Sql;

public class T4Driver {

public static void main(String[] args) throws Exception {
Properties conProperties = new Properties();
conProperties.put("user", "pdqpot");
conProperties.put("password", "pdqpot123");
conProperties.put("portNumber", "50000");
conProperties.put("databaseName", "SAMPLE");
conProperties.put("deferPrepares", "false");
conProperties.put("retrieveMessagesFromServerOnGetMessage", "true");
conProperties.put("emulateParameterMetaDataForZCalls", "1");
conProperties.put("clientApplicationInformation", "T4Driver");
conProperties.put("clientWorkstation", "192.168.10.125");
conProperties.put("defaultIsolationLevel", java.sql.Connection.TRANSACTION_READ_COMMITTED);
conProperties.put("jdbcCollection", "NULLIDR1");
conProperties.put("driverType", "4");

Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
String url = "jdbc:db2://localhost:50000/SAMPLE";
Connection con = DriverManager.getConnection(url, conProperties);
con.setAutoCommit(false);

if (con instanceof DB2Connection) {
DB2Connection db2Con = (DB2Connection) con;
System.out.println("DB2ClientWorkstation = " + db2Con.getDB2ClientWorkstation());
System.out.println("DB2ClientApplicationInformation = " + db2Con.getDB2ClientApplicationInformation());
System.out.println("getAutoCommit = " + (db2Con.getAutoCommit() ? "True" : "False"));
}
@Sql String sql = "VALUES CURRENT TIMESTAMP";
java.sql.PreparedStatement stmt = con.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();

String ts = "";
while (rs.next()) {
ts = rs.getString(1);
System.out.println("Timestamp: " + ts);
}

rs.close();
stmt.close();
}
}

If you look at above program, you notice that there is no trace information in the connection URL. But, the trace information is kept in a property file that JDBC driver uses at the runtime.

db2.jcc.override.traceFile=C:/temp/jdbc.trace
db2.jcc.override.traceFileAppend=true
db2.jcc.override.TraceLevel=TRACE_ALL
db2.jcc.override.currentSchema=VIKRAM

The name of the property file is DB2JccConfiguration.properties and it should be on your class path or in a JAR which should also be on your classpath.

Alternatively, you can specify the name of your file by supplying a switch to JVM at the runtime as shown below:

-Ddb2.jcc.propertiesFile=C:/Test/DB2JccConfiguration.properties

This approach is elegant as you are not touching the code to set the trace and you control it through a property file that you can change anytime.

JCC Tracing in multi-threaded applications

If you use traceFile option in DB2JccConfiguration.properties file, you will collect trace information for all threads in a single file if you are running a multi-threaded application. In that case, you should use traceDirectory property so that JCC driver creates a separate file for each thread.

db2.jcc.override.traceDirectory=C:/temp/jdbc/
db2.jcc.override.traceFile=trace.log
db2.jcc.override.traceFileAppend=true
db2.jcc.override.TraceLevel=TRACE_ALL

Suppose your java program creates two connections in 2 separate threads. When the program completes, the following files contain the trace data:

C:/temp/jdbc/trace.log_global_0
C:/temp/jdbc/trace.log_global_1

Some very important properties least understood

I also use few properties in this simple program and some of them are very useful and important.

For example:

  • deferPrepares This property is true by default and that may lead to some problems occasionally. The prepare is deferred until execution time to save few network hops to describe the query from the database rather than to use java specific mappings. This parameter is true for performance but if there is a problem in correct mapping between java data types and DB2, JCC driver will recover from those errors automatically at execution time and you will see lots of SQL errors in your trace file which should not have been there. To avoid those errors, set this param to false and again see the trace.

    If you want to check very quickly how deferPrepares works, set this value to true in above code and make a mistake in your SQL statement. For example, you can set the SQL as "VALUES CURRENT TIMESTAMP2". This SQL will fail at runtime.

    If deferPrepares=true, you will see the exception at line where you have ResultSet rs = stmt.executeQuery(); but if you set this to false, you will see exception at line where you have java.sql.PreparedStatement stmt = con.prepareStatement(sql);

    This should explain the importance of deferPrepares. The value of true gives a better performance but it can bog you down if there are differences in data type matching between java and DB2 and some problems related to dates or numbers or decimals.

  • clientWorkstation You set this value to identify client workstation and this is an ideal property to set to the IP address from your web response if you are writing a web application. This will allow db2 audit to catch IP addresses of the web client. A very unknown feature not used by Java application developers.
  • jdbcCollection This is another very powerful property that affects the execution of your SQL statements if you are using parameter markers in your application. Please read this article for details on this property at this link
  • driverType This is the type of driver you choose to use. There used to be a separate JDBC driver for Type-2 connections but that has been deprecated and both the drivers are merged in a single db2jcc.jar file. If you set driverType=2, you are using Type-2 driver of DB2 which is using CLI calls under the cover to the database. Which one should you use?

    If you set driverType=4, you really do not need a DB2 client on your machine to connect to DB2 but you will need a DB2 client of you set driverType=2. If you are connecting remotely to db2, you will get a better performance by using Type-4 driver and if you are connecting locally to DB2, you will get a better performance by using Type-2 driver.

  • retrieveMessagesFromServerOnGetMessage This is another property that gives a formatted SQLException output.

    Did you notice that we specified default schema in the external property file and that is very useful if you want to change default schema at run time rather than to hard code in URL of the connection string.

    Did you, by any chance, notice the use of @Sql in above java code and an import of com.ibm.pdq.annotation.Sql. This is a way to check the syntax of SQL at design time if your SQL is right or not and allows you to use context sensitive help by pressing ctrl-space. If this sounds exciting, download free Data Studio from this link.

  • One may ask if there is a way to specify all those properties in a single file and not have to hard code these ones in the code.
    conProperties.put("user", "pdqpot");
    conProperties.put("password", "pdqpot123");
    conProperties.put("portNumber", "50000");
    conProperties.put("deferPrepares", "false");
    conProperties.put("retrieveMessagesFromServerOnGetMessage", "true");
    conProperties.put("emulateParameterMetaDataForZCalls", "1");
    conProperties.put("clientApplicationInformation", "T4Driver");
    conProperties.put("clientWorkstation", "192.168.10.125");
    conProperties.put("defaultIsolationLevel", java.sql.Connection.TRANSACTION_READ_COMMITTED);
    conProperties.put("jdbcCollection", "NULLIDR1");
    conProperties.put("driverType", "4");

    In the property file DB2JccConfiguration.properties, you can only specify few properties that JCC driver will actually use. So, if you specify deferPrepares in this file, JCC driver is not going to use it. But, you can do one thing - Take all of the above properties and put them in same property file and load it at run time in your program. This way, JCC driver will also use same property file and your program is also using same property file. Some may like it and some may not. Choice is yours.

JCC Tracing made really simple with connection properties

What if, you want all properties put in a single file and also control JCC tracing at run time. The following code example actually does that and all of your settings are in one file but you control JCC tracing through one -D switch specified through command line.

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.Enumeration;
import java.util.Properties;

import com.ibm.pdq.annotation.Sql;

public class T4Driver
{

private static String PARAM_PROP_FILE = "DB2JccConfiguration.properties";

public static void main(String[] args) throws Exception
{
String propName, propValue;
Properties conProperties = new Properties();
InputStream istream = ClassLoader.getSystemResourceAsStream(PARAM_PROP_FILE);
if (istream == null)
{
try
{
conProperties.load(new FileInputStream(PARAM_PROP_FILE));
} catch (Exception e)
{
System.out.println(PARAM_PROP_FILE + " not found. Exiting ... ");
System.exit(-1);
}
} else
{
conProperties.load(istream);
System.out.println(PARAM_PROP_FILE + " file loaded");
}

Enumeration enu = conProperties.keys();
while (enu.hasMoreElements())
{
propName = (String) enu.nextElement();
propValue = conProperties.get(propName).toString();
System.out.println("propName = " + propName + " propValue = " + propValue);
}
Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
String url = "jdbc:db2://localhost/SAMPLE";
Connection con = DriverManager.getConnection(url, conProperties);
con.setAutoCommit(false);

@Sql String sql = "VALUES CURRENT TIMESTAMP";
java.sql.PreparedStatement stmt = con.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();

String ts = "";
while (rs.next())
{
ts = rs.getString(1);
System.out.println("Timestamp: " + ts);
}

rs.close();
stmt.close();
}
}

Some of the points to remember:

  • All properties are placed in a single file including tracing and connection related.
  • This property file can be placed in current directory assuming that the current directory is not on classpath
  • If you do not specify this property file through -D switch, no tracing will happen.
  • The Java program actually reads this file and uses as a connection property.
  • If you specify this file as command line argument -Ddb2.jcc.propertiesFile=C:/Test/DB2JccConfiguration.properties, you will start tracing.
  • If you have this file in a JAR file or in classpath, you do not need to specify it using -D switch. However, you will have to delete JCC tracing parameters from this file to stop tracing.
  • Use any of the method that suits your best needs.
  • DB2JccConfiguration.properties file having JCC tracing and connection properties
    #### JCC Driver reads these properties automatically
    db2.jcc.override.traceFile=C:/temp/jdbc.trace
    db2.jcc.override.traceFileAppend=true
    db2.jcc.override.TraceLevel=TRACE_ALL
    db2.jcc.override.currentSchema=VIKRAM

    #### Extra connection properties used at the time of opening connections.
    #### Java program reads this propety file

    user=pdqpot
    password=pdqpot123
    portNumber=50000
    deferPrepares=false
    retrieveMessagesFromServerOnGetMessage=true
    emulateParameterMetaDataForZCalls=1
    clientApplicationInformation=T4Driver
    clientWorkstation=192.168.10.125
    defaultIsolationLevel=2
    jdbcCollection=NULLIDR1
    driverType=4