IBM Support

Hands-on example for JDBC tracing(jcc trace) by using DB2JccConfiguration.properties file

Technical Blog Post


Abstract

Hands-on example for JDBC tracing(jcc trace) by using DB2JccConfiguration.properties file

Body

There are several ways to enable JDBC tracing(jcc trace) but the easiest way is tracing by using DB2JccConfiguration.properties file because you don't need any application change. I am posting actual example with sample java program compile and execution to show actual steps. If you cannot get trace files from your java program (e.g. WAS) even after restart, you can test these steps to see if your tracing configuration is working or not.
 
This article is composed of the following 6 steps.
--------------------------------------------
1. Sample program code
2. Configuration setting by using "DB2JccConfiguration.properties"
3. CLASSPATH backup and add new path in CLASSPATH
4. Sample program compile and run
5. Check jcc trace files
6. Restore the CLASSPATH
--------------------------------------------
 
1. Sample program code
- I just used sample code in the KC link(ref 1) and modified select query so that it can be tested in any DB2 database.
(woongc@eva) /home2/woongc/jcctst
$ cat tst.java
import java.sql.*;
 
public class tst {
 
        public static void main(String [] args) {
              String urlPrefix = "jdbc:db2:";
              String url;
              String user;
              String password;
              String dummy;
              Connection con;
              Statement stmt;
           ResultSet rs;
 
           System. out.println ("**** Enter class tst" );
 
           // Check the that first argument has the correct form for the portion
           // of the URL that follows jdbc:db2:,
           // as described
           // in the Connecting to a data source using the DriverManager
           // interface with the IBM Data Server Driver for JDBC and SQLJ topic.
           // For example, for IBM Data Server Driver for
           // JDBC and SQLJ type 2 connectivity,
           // args [0] might be MVS1DB2M. For
           // type 4 connectivity, args[0] might
           // be //stlmvs1:10110/MVS1DB2M.
 
           if (args.length !=3)
           {
             System. err.println ("Invalid value. First argument appended to " +
              "jdbc:db2: must specify a valid URL." );
             System. err.println ("Second argument must be a valid user ID." );
             System. err.println ("Third argument must be the password for the user ID.");
             System. exit(1);
           }
           url = urlPrefix + args[0];
           user = args[1];
           password = args[2];
 
           try {
              Class. forName("com.ibm.db2.jcc.DB2Driver");
              System. out.println("**** Loaded the JDBC driver" );
 
               // Create the connection using the IBM Driver for JDBC and SQLJ
              System. out.println(url);
              con = DriverManager. getConnection(url, user, password);
              con.setAutoCommit( false);
              System. out.println("**** Created a JDBC connection to the data source");
 
               // Create the Statement
              stmt = con.createStatement();
              System. out.println("**** Created JDBC Statement object" );
 
               // Execute a query and generate a ResultSet instance
              rs = stmt.executeQuery( "SELECT 1 FROM SYSIBM.SYSDUMMY1");
              System. out.println("**** Created JDBC ResultSet object" );
 
               // Print all of the employee numbers to standard output device
               while (rs.next()) {
                   dummy = rs.getString(1);
                   System. out.println("number = " + dummy);
              }
              System. out.println("**** Fetched all rows from JDBC ResultSet" );
              rs.close();
              System. out.println("**** Closed JDBC ResultSet" );
 
               // Close the Statement
               stmt.close();
               System. out.println("**** Closed JDBC Statement" );
 
               // Connection must be on a unit-of-work boundary to allow close
               con.commit();
               System. out.println ( "**** Transaction committed" );
 
            // Close the connection
               con.close();
               System. out.println("**** Disconnected from data source" );
 
               System. out.println("**** JDBC Exit from class tst - no errors" );
           }
           catch(ClassNotFoundException e) {
              System. err.println("Could not load JDBC driver" );
               System. out.println("Exception: " + e);
               e.printStackTrace();
           }
           catch(SQLException ex) {
              System. err.println("SQLException information" );
               while(ex!=null ) {
                 System. err.println ("Error msg: " + ex.getMessage());
                 System. err.println ("SQLSTATE: " + ex.getSQLState());
                 System. err.println ("Error code: " + ex.getErrorCode());
                 ex.printStackTrace();
                 ex = ex.getNextException(); // For drivers that support chained exceptions
               }
           }
       }
}
 
 
2. Configuration setting by using "DB2JccConfiguration.properties"
- The file name(DB2JccConfiguration.properties) must be exact and the path where the file exists must be in CLASSPATH.
(woongc@eva) /tmp
$ cat DB2JccConfiguration.properties
db2.jcc.override.traceDirectory=/tmp
db2.jcc.override.traceFile=jcctrc
db2.jcc.override.traceFileAppend=true
db2.jcc.override.TraceLevel=TRACE_ALL
 
 
3. CLASSPATH backup and add new path in CLASSPATH
(woongc@eva) /tmp
$ echo $CLASSPATH > classpath.bak
(woongc@eva) /tmp
$ export CLASSPATH=/tmp:$CLASSPATH
 
 
4. Sample program compile and run
- I tried type4 connection. You need url(IP, port number and dbname), username and password.
(woongc@eva) /home2/woongc/jcctst
$ $HOME/sqllib/java/jdk64/bin/javac tst.java
(woongc@eva) /home2/woongc/jcctst
$ $HOME/sqllib/java/jdk64/bin/java tst //localhost:60501/tstv105 woongc *******
**** Enter class tst
**** Loaded the JDBC driver
jdbc:db2://localhost:60501/tstv105
**** Created a JDBC connection to the data source
**** Created JDBC Statement object
**** Created JDBC ResultSet object
number = 1
**** Fetched all rows from JDBC ResultSet
**** Closed JDBC ResultSet
**** Closed JDBC Statement
**** Transaction committed
**** Disconnected from data source
**** JDBC Exit from class tst - no errors
 
 
5. Check jcctrc files
(woongc@eva) /tmp
$ ls -al jcctrc*
-rw-r--r--    1 woongc   staff         37989 May 25 15:53 jcctrc_global_1
(woongc@eva) /tmp
$ head -10 jcctrc_global_1
[jcc] BEGIN TRACE_XML_CONFIGURATION_FILE
[jcc] dsdriverConfigFile=null
[jcc] END TRACE_XML_CONFIGURATION_FILE
[jcc] BEGIN TRACE_DRIVER_CONFIGURATION
[jcc] Driver: IBM Data Server Driver for JDBC and SQLJ 3.69.49
[jcc] Compatible JRE versions: { 1.4, 1.5, 1.6, 1.7 }
[jcc] Target server licensing restrictions: { z/OS: disabled; SQLDS: disabled; iSeries: disabled; DB2 for Unix/Windows: enabled; Cloudscape: enabled; Informix: enabled }
[jcc] License editions: { O: not found; ZS: not found; IS: not found; AS: not found; EE: not found; PE: not found }
[jcc] Range checking enabled: true
[jcc] Bug check level: 0xff
 
 
6. Restore the CLASSPATH
export CLASSPATH=`cat classpath.bak`
 
--------------------------------------
ref 1
 
Example of a simple JDBC application
--------------------------------------

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11140448