IBM Support

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

Technical Blog Post


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


There are several ways to enable JDBC tracing(jcc trace) but the easiest way is tracing by using 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 ""
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
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("");
              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 ( {
                   dummy = rs.getString(1);
                   System. out.println("number = " + dummy);
              System. out.println("**** Fetched all rows from JDBC ResultSet" );
              System. out.println("**** Closed JDBC ResultSet" );
               // Close the Statement
               System. out.println("**** Closed JDBC Statement" );
               // Connection must be on a unit-of-work boundary to allow close
               System. out.println ( "**** Transaction committed" );
            // Close the connection
               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);
           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 = ex.getNextException(); // For drivers that support chained exceptions
2. Configuration setting by using ""
- The file name( must be exact and the path where the file exists must be in CLASSPATH.
(woongc@eva) /tmp
$ cat
3. CLASSPATH backup and add new path in CLASSPATH
(woongc@eva) /tmp
$ echo $CLASSPATH > classpath.bak
(woongc@eva) /tmp
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
(woongc@eva) /home2/woongc/jcctst
$ $HOME/sqllib/java/jdk64/bin/java tst //localhost:60501/tstv105 woongc *******
**** Enter class tst
**** Loaded the JDBC driver
**** 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] dsdriverConfigFile=null
[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":""}]