DB2 application development: Tracing with the DB2 Universal JDBC Driver

How to perform a JDBC trace when the CLI layer is missing

Trace data at the interface between application and database provides the developer with information to identify program errors and to optimize database access. The DB2® Legacy JDBC™ Driver is based on the DB2 Call Level Interface (CLI) layer and allows for JDBC or CLI tracing through changes in the CLI configuration. The new DB2 Universal JDBC Driver is no longer based on the DB2 CLI layer, so that the known trace facilities are no longer available. Instead the DB2 Universal JDBC Driver offers trace facilities by setting of certain driver properties. This article first looks at the CLI-based trace facilities of the DB2 Legacy JDBC Driver, then describes the trace facilities of the new DB2 Universal JDBC Driver and demonstrates their usage by example.

Share:

Dirk Fechner (fechner@de.ibm.com), IT Services Specialist, IBM Software Group, Information Management

Author photoDirk Fechner works as an IT Specialist for IBM Software Group Services. His area of expertise is design, implementation, and administration of DB2 databases on distributed platforms as well as database application development. He has ten years of experience with DB2 for Linux, UNIX, and Windows and is an IBM Certified Advanced Database Administrator and an IBM Certified Application Developer.


developerWorks Contributing author
        level

09 June 2005

Also available in Russian

Introduction -- Why JDBC tracing?

JDBC tracing is a method for providing Java application developers with valuable information to aid in their database application development efforts. Here are some examples of instances when you need to know how to perform a JDBC trace:

  • Searching for program logic or initialization errors -- A database connection may fail because of a wrong URL, a query that is expected to be executed once is called again and again, database inconsistencies may occur because of a transaction that is not correctly defined, and so on. In all these cases, it is likely that trace data can be used to uncover the cause of the problem.
  • Performance tuning -- Performance problems in multi-tier environments are hard to detect because the responsible tier - application, network, or database - has to be determined first. By analyzing the entry/exit timestamps of function calls in the trace data stream, you can identify which tier is causing the performance problems.
  • Understanding third party software -- When you're using third party software, problem determination is often difficult as source code is not available. Therefore trace information may be helpful to better understand how third party software implements the database interface.

DB2 Legacy JDBC Driver versus DB2 Universal JDBC Driver


The DB2 Legacy JDBC Driver is the old type 2 driver that has been available in older versions of DB2. It is built on top of the DB2 CLI, a DB2-native C call level interface, which was itself built on top of several other layers. As a type 2 driver, it requires a DB2 client installation. The DB2 Legacy JDBC Driver is contained in the file db2java.zip.

With DB2 version 8, a new JDBC driver was shipped, the so-called DB2 Universal JDBC Driver. It was written from scratch and is architected as an abstract JDBC processor that allows for type 2 and type 4 connectivity. Applications select the desired type of connectivity by using different URL syntax when making the connection. The DB2 Universal Driver communicates with the DB2 server using Distributed Relational Database Architecture (DRDA) protocol, and is built on top of a Java client layer that replaces CLI and a number of layers beneath it. A DB2 client installation is only required for type 2 connectivity; type 4 does not require a DB2 client. The DB2 Universal JDBC Driver is contained in the file db2jcc.jar (JCC stands for Java Common Connectivity). Although the DB2 Legacy Driver is still supported, it will be completely replaced by the DB2 Universal JDBC Driver in the future.


Tracing with the DB2 Legacy JDBC Driver Type 2


The CLI-based DB2 Legacy JDBC Driver Type 2 offers the application developer two different trace possibilities:

  • Tracing at the JDBC layer -- In this case all JDBC function calls are traced. The trace information consists of: sequence of function calls, input and output parameters, return codes, as well as error and warning messages.
  • Tracing at the CLI layer -- The DB2 Legacy JDBC Driver Type 2 internally maps all JDBC function calls on CLI function calls. Therefore a Java program that uses this driver can alternatively activate tracing at the CLI layer. The trace information is the same -- function calls, parameters, return codes, and messages -- but on a lower level at the CLI layer.

Both trace variants are controlled through the CLI initialization file, db2cli.ini. On Windows systems the file db2cli.ini can be found in the directory %DB2PATH% (C:\Program Files\IBM\SQLLIB by default). On UNIX/Linux systems it can be found in the instance owner's $HOME directory under sqllib/cfg.

To activate tracing at the JDBC layer, make the following entries in the file db2cli.ini under the section [COMMON]:

Listing 1. CLI keywords for JDBC tracing
[COMMON]
JDBCTrace=1
JDBCTracePathName=<trace directory>

Optionally the keyword JDBCTraceFlush=1 can be specified. In this case each trace entry is separately written to the trace file; afterwards the trace file is closed and opened again. This guarantees that no trace entry gets lost, even if the Java program crashes. On the other hand, this option extremely degrades performance.

As entries in the file db2cli.ini are only read when a database connection is first established, changes to the db2cli.ini file have no impact on existing database connections.

The following sample program can be used to test JDBC tracing when the DB2 Legacy JDBC Driver Type 2 is used. In the remainder of this article the sample program is also used to demonstrate the trace facilities of the DB2 Universal JDBC Driver.

Listing 2. Sample code for trace test with the Legacy Driver Type 2
public class LegacyTraceExample
{
    public static void main(String[] args) {
        try {
            // load driver
            Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();

            // set connection properties
            String databaseUrl = "jdbc:db2:sample";

            // get connection
            java.sql.Connection con =
                java.sql.DriverManager.getConnection(databaseUrl, "user", "password");

            // execute a query
            java.sql.Statement stmt = con.createStatement();

            String query = "SELECT COUNT(*) FROM SYSCAT.TABLES";
            java.sql.ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                System.out.println("\n" + query + " = " + rs.getInt(1));
            }

            rs.close();
            stmt.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Because the sample program is only used to demonstrate trace facilities, exception handling is reduced to a minimum. First the DB2 Legacy JDBC Driver Type 2 is dynamically loaded. Then a connection to the database is established via the JDBC driver manager and a simple query against the system catalog tables is executed.

The following entries in the db2cli.ini file create a JDBC trace of the sample program in the directory C:\temp.

Listing 3. db2.cli entries for trace test with the Legacy Driver Type 2
[COMMON]
JDBCTrace=1
JDBCTraceFlush=1
JDBCTracePathName=c:\temp

Tracing with the DB2 Universal JDBC Driver Type 2/Type 4


The DB2 Universal JDBC Driver is no longer based on the DB2 CLI layer. This is true for an initialization as type 4 driver as well as for an initialization as type 2 driver. Because of this a JDBC trace through changes of the CLI configuration (file db2cli.ini) is no longer possible. Instead, the DB2 Universal JDBC Driver allows tracing by setting of certain driver properties.

When the DataSource interface is used for database access, the trace properties can be set through methods of this interface. All DataSource classes of the DB2 Universal JDBC Driver inherit from the base class DB2BaseDataSource which also defines the properties for tracing.

Listing 4. DB2 DataSource classes
com.ibm.db2.jcc.DB2BaseDataSource
    com.ibm.db2.jcc.DB2SimpleDataSource
    com.ibm.db2.jcc.DB2DataSource
    com.ibm.db2.jcc.DB2ConnectionPoolDataSource
    com.ibm.db2.jcc.DB2XADataSource

In the following examples a DB2SimpleDataSource is used for database access. The configuration for JDBC tracing works identically for all DataSource classes and is also independent of a type 2 or type 4 initialization of the DB2 Universal JDBC Driver.

The following sample program demonstrates how JDBC tracing can be activated by calling methods that are defined in the class DB2BaseDataSource.

Listing 5. Sample code for trace test with the Universal Driver Type 2/4 (Variant 1)
public class JccTraceExample1
{
    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("localhost");
            ds.setPortNumber(50000);
            ds.setDatabaseName("sample");
            ds.setDriverType(4);

            // set trace properties
            ds.setTraceDirectory("c:\\temp");
            ds.setTraceFile("trace");
            ds.setTraceFileAppend(false);
            ds.setTraceLevel(com.ibm.db2.jcc.DB2BaseDataSource.TRACE_ALL);

            // get connection
            java.sql.Connection con = ds.getConnection("user", "password");

            // execute a query
            java.sql.Statement stmt = con.createStatement();

            String query = "SELECT COUNT(*) FROM SYSCAT.TABLES";
            java.sql.ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                System.out.println("\n" + query + " = " + rs.getInt(1));
            }

            rs.close();
            stmt.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

First a DB2SimpleDataSource for database access is initialized, in this case as type 4. Configuration of JDBC tracing is done by calling the following methods:

  • setTraceDirectory - Defines the directory where trace files are written. If a trace directory is defined, a separate trace file is created for each database connection. This is recommended because otherwise trace data for all database connections is written to a single trace file -- this makes trace file analysis much more difficult.
  • setTraceFile - Defines the file to which trace output is written. If a trace filename is specified in combination with a trace directory -- like in the example -- a trace file according to the following pattern is created for each database connection: <trace directory>\<trace file>_<data source type>_<sequential number>. For the sample program, a trace file c:\temp\trace_sds_0 is created. If no trace directory is specified, all trace output of all database connections is written to the specified trace file. In this case, the trace file can be specified with its complete path too.
  • setTraceFileAppend -- Controls whether or not trace files are overwritten if the files already exist.
  • setTraceLevel - Defines what kind of information should be traced. For this purpose constants that are defined in the base class DB2BaseDataSource are used.
Table 1. DB2 JDBC trace constants
Trace ConstantInteger Value
com.ibm.db2.jcc.DB2BaseDataSource.TRACE_NONE0
com.ibm.db2.jcc.DB2BaseDataSource.TRACE_CONNECTION_CALLS1
com.ibm.db2.jcc.DB2BaseDataSource.TRACE_STATEMENT_CALLS2
com.ibm.db2.jcc.DB2BaseDataSource.TRACE_RESULT_SET_CALLS4
com.ibm.db2.jcc.DB2BaseDataSource.TRACE_DRIVER_CONFIGURATION16
com.ibm.db2.jcc.DB2BaseDataSource.TRACE_CONNECTS32
com.ibm.db2.jcc.DB2BaseDataSource.TRACE_DRDA_FLOWS64
com.ibm.db2.jcc.DB2BaseDataSource.TRACE_RESULT_SET_META_DATA128
com.ibm.db2.jcc.DB2BaseDataSource.TRACE_PARAMETER_META_DATA256
com.ibm.db2.jcc.DB2BaseDataSource.TRACE_DIAGNOSTICS512
com.ibm.db2.jcc.DB2BaseDataSource.TRACE_SQLJ1024
com.ibm.db2.jcc.DB2BaseDataSource.TRACE_XA_CALLS2048
com.ibm.db2.jcc.DB2BaseDataSource.TRACE_ALL-1

In the sample program all trace information is gathered (com.ibm.db2.jcc.DB2BaseDataSource.TRACE_ALL). If you wish to only trace certain information, you can combine the trace constants by using the OR-operator (for example com.ibm.db2.jcc.DB2BaseDataSource.TRACE_STATEMENT_CALLS | com.ibm.db2.jcc.DB2BaseDataSource.TRACE_RESULT_SET_CALLS).

This kind of tracing may be a little bit problematic, as it requires changes to the source code, which is not desirable under all circumstances. When the data source is defined separately from the source code, which is true if an application server like WebSphere is used, the trace properties can be specified together with the definition of the Data Source. This allows an activation/deactivation of tracing without changes to the source code.

But even if the data source is defined in the source code -- like in the sample program -- tracing can be controlled without changes to the source code. Listing 6 shows the sample program without setting of trace properties.

Listing 6. Sample code for trace test with the Universal Driver Type 2/4 (Variant 2)
public class JccTraceExample2
{
    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("localhost");
            ds.setPortNumber(50000);
            ds.setDatabaseName("sample");
            ds.setDriverType(4);

            // get connection
            java.sql.Connection con = ds.getConnection("user", "password");

            // execute a query
            java.sql.Statement stmt = con.createStatement();

            String query = "SELECT COUNT(*) FROM SYSCAT.TABLES";
            java.sql.ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                System.out.println("\n" + query + " = " + rs.getInt(1));
            }

            rs.close();
            stmt.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

To control tracing without changes to the source code, you create a separate configuration file containing the trace properties.

Listing 7. DB2 JDBC trace properties
db2.jcc.traceDirectory=c:\\temp
db2.jcc.traceFile=trace
db2.jcc.traceFileAppend=false
db2.jcc.traceLevel=-1

There are no naming conventions for this configuration file. The filename is specified via the option -D when the Java program is executed. For example if the configuration file is named jcc.properties, the program call looks like this.

Listing 8. DB2 JDBC trace properties file
java -Ddb2.jcc.propertiesFile=jcc.properties JccTraceExample2

In this case the configuration file is placed in the same directory as the Java class file. Otherwise a complete path for the configuration file can be specified too.

If a configuration file is used, the trace level cannot be specified as constant, instead the corresponding integer values have to be used, for example -1 for TRACE_ALL or 6 for TRACE_STATEMENT_CALLS | TRACE_RESULT_SET_CALLS (in this case the values are simply added = 2 + 4).

Because the properties in the configuration file are not restricted to a certain data source, they automatically refer to all data sources. Therefore the trace filenames that are generated differ from the ones in the previous example. If a trace directory is specified as well as a trace file, a trace file according to the following pattern is created for each database connection: <trace directory>\<trace file>_global_<sequential number>. For the sample program a trace file c:\temp\trace_global_0 is created.

If trace properties are specified in the source code and in the configuration file too, then the properties defined in the source code are used. To force usage of the trace properties in the configuration file, the trace properties in the configuration file have to be specified with the addition override.

Listing 9. Example for overriding of trace properties
db2.jcc.override.traceDirectory=c:\\temp
db2.jcc.override.traceFile=trace
db2.jcc.override.traceFileAppend=false
db2.jcc.override.traceLevel=-1

Variants of tracing when using the DB2 Universal JDBC Driver


Instead of a Data Source the DriverManager interface can also be used to establish a database connection. In this case the trace properties can be specified as an addition to the database URL.

Listing 10. Sample code for trace test with the Universal Driver Type 2/4 (Variant 3)
public class JccTraceExample3
{
    public static void main(String[] args) {
        try {
            // load driver
            Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();

            // set connection properties
            String databaseUrl = "jdbc:db2://localhost:50000/sample"
                                 + ":traceDirectory=c:\\temp"
                                 + ";traceFile=trace"
                                 + ";traceFileAppend=false"
                                 + ";traceLevel="
                                 + com.ibm.db2.jcc.DB2BaseDataSource.TRACE_ALL
                                 + ";";

            // get connection
            java.sql.Connection con =
                java.sql.DriverManager.getConnection(databaseUrl, "user", "password");

            // execute a query
            java.sql.Statement stmt = con.createStatement();

            String query = "SELECT COUNT(*) FROM SYSCAT.TABLES";
            java.sql.ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                System.out.println("\n" + query + " = " + rs.getInt(1));
            }

            rs.close();
            stmt.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

If a trace directory is specified as well as a trace file, a trace file according to the following pattern is created for each database connection: <trace directory>\<trace file>_driver_<sequential number>. For the sample program a trace file c:\temp\trace_driver_0 is created.

Alternatively trace output can be redirected to a PrintWriter. In this case tracing is activated by calling the method setJccLogWriter of the class com.ibm.db2.jcc.DB2Connection. As parameters the method expects the PrintWriter to be used as well as the trace level.

Listing 11. Sample code for trace test with the Universal Driver Type 2/4 (Variant 4)
public class JccTraceExample4
{
    public static void main(String[] args) {
        try {
            // create print writer
            java.io.PrintWriter printWriter = new java.io.PrintWriter(
                new java.io.BufferedOutputStream(
                    new java.io.FileOutputStream("c:\\temp\\trace.txt"), 4096), true);

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

            // set connection properties
            ds.setServerName("localhost");
            ds.setPortNumber(50000);
            ds.setDatabaseName("sample");
            ds.setDriverType(4);

            // get connection
            java.sql.Connection con = ds.getConnection("user", "password");

            // activate trace
            ((com.ibm.db2.jcc.DB2Connection) con).setJccLogWriter(printWriter,
                com.ibm.db2.jcc.DB2BaseDataSource.TRACE_ALL);

            // execute a query
            java.sql.Statement stmt = con.createStatement();

            String query = "SELECT COUNT(*) FROM SYSCAT.TABLES";
            java.sql.ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                System.out.println("\n" + query + " = " + rs.getInt(1));
            }

            rs.close();
            stmt.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Acknowledgement


I would like to acknowledge Peter Schurr for reviewing this article.


Download

DescriptionNameSize
DB2 Universal JDBC Driver trace samplesdb2jcc_trace_samples.zip  ( HTTP | FTP | Download Director Help )4KB

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Java technology
ArticleID=84723
ArticleTitle=DB2 application development: Tracing with the DB2 Universal JDBC Driver
publish-date=06092005