IBM Support

Collecting Data: Tracing with the IBM Data Server Driver for JDBC and SQLJ

Question & Answer


Question

What information should you collect when you experience a problem with an application the using the IBM Data Server Driver for JDBC and SQLJ (also known as the DB2 Universal JDBC Driver or JCC)? Gathering the following information before engaging IBM support will help you to understand the problem more clearly and allow IBM to diagnose the problem promptly. This document outlines several methods for enabling and capturing traces to troubleshoot issues that occur in the use of the driver.

Answer

Information to Collect

This section lists the required information for IBM DB2 Support to analyze, and how to collect it.
In addition to answers for the questions above, the most commonly useful information for diagnosing a problem with a JDBC application that uses JCC, is a JCC trace. It is also recommended, if feasible, to enable application traces at the JDBC connection manager level (and transaction level, if the problem is related to XA). Consult the application vendor's documentation on how best to accomplish this. For WebSphere Application Server and other IBM products, search for existing MustGather (Collecting Data) technotes covering the problem scenario.

Unlike the legacy CLI-based Type 2 driver (DB2 JDBC Type 2 Driver), the IBM Data Server Driver for JDBC and SQLJ is not based on the DB2 CLI layer. A JCC trace cannot be enabled by changes to the CLI configuration (db2cli.ini). Instead, tracing is enabled by setting certain driver properties. There are several possible methods for enabling the trace. Which method is most appropriate depends on the scenario in which the driver is being used.

Enabling trace for a Datasource connection
Enabling trace for a DriverManager connection
Enabling trace for a WebSphere managed datasource connection

Enabling dynamic tracing

Collecting db2support
Submitting information to IBM Support
Notes regarding best practices




Enabling trace for a datasource connection

If a DataSource interface is used for database access, the trace properties can be set through methods of this interface. All DataSource classes of the IBM Data Server Driver for JDBC and SQLJ inherit from the base class DB2BaseDataSource, which also defines the properties for tracing.
To see an example of tracing a datasource connection programmatically, see the DB2 product documentation in IBM Knowledge Center:
Example of a trace program under the IBM Data Server Driver for JDBC and SQLJ

Enabling traces programmatically is generally undesirable except for stand-alone testcases. Fortunately, the trace properties can be set also in a properties file. Configuration of JDBC tracing is done by setting the following driver properties:

  • traceDirectory - 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.
  • traceFile - Defines the file to which trace output is written.
  • traceFileAppend - Controls whether trace files are overwritten if the files exist.
  • traceLevel - Defines what kind of information is traced.
  • traceFileAppend - Specifies whether to overwrite the existing files as specified by the traceFile property.
  • traceFileCount* - Specifies the maximum number of trace files when circular tracing is enabled
  • traceFileSize* - Specifies the maximum size of each trace file, in bytes, when circular tracing is enabled. Default is 1 MB is not specified. If traceFileSize is set to a value greater than 0 or lower than 1 MB then it will take the default value of 1 MB. This property is only used when traceOption is set to TRACE_OPTION_CIRCULAR(1).
  • traceOption* - Specifies how trace data is collected. Valid values:
    • DB2BaseDataSource.NOT_SET (0) - A single trace file is generated, with no limit to file size. This is the default.
    • DB2BaseDataSource.TRACE_OPTION_CIRCULAR (1) - circular tracing is enabled. Traces are written until the configured maximum number of trace files are filled, then continue to overwrite ('wrap') the trace files beginning with the oldest file.


* Properties traceFileCount, traceFileSize, and traceOption, are new, and are valid in driver versions 3.63 (DB2 version 9.7 Fix Pack 5) and later.

For more information on these properties, see the corresponding entries in

Common IBM Data Server Driver for JDBC and SQLJ properties for all supported database products


For valid values for traceLevel, see the following table:

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_CALLS**2048
com.ibm.db2.jcc.DB2BaseDataSource.TRACE_ALL-1

** Effective for IBM Data Server Driver for JDBC and SQLJ type 2 connectivity for DB2 Database for Linux, UNIX, and Windows only

Trace constants (other than TRACE_ALL, -1) are numerically additive. To specify multiple trace components (as specified by their corresponding trace constants) in tracelevel, combine the required trace constants by numeric addition. For example, if the trace specification

(TRACE_CONNECTION_CALLS |
TRACE_STATEMENT_CALLS |
TRACE_RESULT_SET_CALLS |
TRACE_DRIVER_CONFIGURATION |
TRACE_CONNECTS |
TRACE_DIAGNOSTICS)

is required, then traceLevel can be set to the sum of the integer values of these constants, for the above example:

1 + 2 + 4 + 16 + 32 + 512 = 567

So, traceLevel can be set to 567 to enable these trace components. In general, use TRACE_ALL (-1) when gathering traces to submit diagnostic information to IBM technical support, unless otherwise advised.


If tracing is being enabled by using a driver configuration properties file, the file can contain entries as follows:

db2.jcc.traceDirectory=/tmp/jcctrace
db2.jcc.traceFile=trace
db2.jcc.traceFileAppend=false
db2.jcc.traceLevel=-1

To configure JDBC trace to be dynamically enabled or disabled, the tracePolling and associated parameters should be added to the driver configuration properties file. It cannot be configured via JDBC driver properties outside of the driver configuration properties file.

By default, JDBC driver will look driver configuration properties file called DB2JccConfiguration.properties in the directories specified by the CLASSPATH environment variable unless a different file was specified via db2.jcc.propertiesFile. In the example below we enable for tracing to be dynamically enabled/disabled and for the trace to wrap so it does not consume too much space.

db2.jcc.tracePolling=true
db2.jcc.tracePollingInterval=60
db2.jcc.traceDirectory=/tmp
db2.jcc.traceLevel=0
db2.jcc.traceOption=1
db2.jcc.traceFileCount=5
db2.jcc.traceFileAppend=false

tracePolling: Enables JDBC trace to be dynamically enabled/disabled.
tracePollingInterval: Check the driver configuration file every xx seconds to see if the traceLevel parameter has changed

Enable by setting traceLevel=-1
Disable by setting traceLevel=0


On Windows, the file path separator ('\', that is, backslash) needs to be 'escaped'. The file path can look similar to what follows:

db2.jcc.traceDirectory=C:\\temp\\java\\jcctrace


There are no naming conventions for the driver configuration properties file. The file name is specified by the flag -D<option=value> when the Java program is run. For example, if the configuration file is named jcc.properties, the program call would look like this.

java -Ddb2.jcc.propertiesFile=jcc.properties JccTraceExample

In this case, the configuration file is placed in the same directory as the Java class file. A complete path for the configuration file can be specified as well.

Alternately, if the properties file is named 'DB2JccConfiguration.properties' and added to a directory that is part of the Java runtime class path (for example, as specified by the java command-line -cp argument, or by the CLASSPATH environment variable), this will also enable JDBC driver tracing. The file must have this exact file name, and only one copy of the file can be found in the application's class path.


If trace properties are also specified in the source code, then the properties that are defined in the source code are used unless 'override' properties (for example, db2.jcc.override.traceLevel) are used in the properties file.
For more information on driver configuration properties, see the DB2 product documentation in IBM Knowledge Center:

IBM Data Server Driver for JDBC and SQLJ configuration properties

Customization of IBM Data Server Driver for JDBC and SQLJ configuration properties

Example of using configuration properties to start a JDBC trace

The above links are for version 10.1. Consult the appropriate version pages in the product documentation if you are on another version.


Version 9.8 (pureScale)
Version 9.7
Version 9.5
Version 9.1
Version 8

If traces are configured by using a driver configuration properties file, the properties apply driver-wide, so they automatically refer to all data sources. Again, this is not always desirable.
If the data source is defined separately from the source code, which is true if an application-managed datasource is used, the trace properties can be specified together with the definition of the DataSource. This allows an activation/deactivation of tracing without changes to the source code. The following section deals with a specific case of this scenario: tracing a WebSphere-managed (com.ibm.ws.rsadapter) datasource.

For more information on datasource-level properties, see the following links:

Version 10.1
Version 9.7
Version 9.5
Version 9.1
Version 8



Enabling trace for a WebSphere-managed datasource

Where WebSphere Application Server is involved, if JDBC traces are required, it is often easier and more convenient to enable tracing within WebSphere rather than hard-coding the trace, or enabling the trace using a configuration properties file. This can be accomplished from within the WebSphere administration console, if the datasource is managed by WebSphere.
Here are the steps for taking a combined WebSphere and JCC (JDBC) combined trace:

1) Set the trace properties for JDBC in WebSphere Application Server

In the administration console, go to
Resources > JDBC Provider > Data Sources > (Data source name) > Additional Properties > Custom Properties
Set: traceLevel to -1 (-1 means full trace, TRACE_ALL)

The property traceFile may also be set, to write the JCC trace to another file. It is recommended, however, to leave this blank, which creates the jcc trace embedded in the WebSphere trace (trace.log) automatically. This allows you to take advantage of the 'maximum trace file size' and 'maximum number of historical logs' options in WebSphere tracing. It's also preferred to take WebSphere traces concurrently with JCC traces, unless otherwise advised. For details on values for traceLevel, see 'Table 1. DB2 JDBC Trace constants' in the
previous section.

2) Start the trace, by enabling WebSphere diagnostic trace

In the administration console, go to
Troubleshooting > Logs and Trace > (select the server) > Diagnostic Trace

Trace Specification:
    v6 or later: *=info:WAS.j2c=all:RRA=all:WAS.database=all:Transaction=all
    v5: RRA=all=enabled:WAS.database=all=enabled:J2C=all=enabled
Note that each option is seperated by a ':'.

See also related documentation available in the WebSphere Application Server Information Center:

Version 6.1
Version 7
Version 8



WebSphere trace and log data should be collected per the following technote:
MustGather: Connection pooling problems for WebSphere Application Server

(see ISA Lite or the section 'Collecting Data manually')



Enabling trace for a DriverManager connection

For an application using a DriverManager, rather than a datasource connection, there are two methods for enabling the trace:
  • Programatically, usting the PrintWriter object
  • Update the JDBC connection URL to contain the properties.

The first method can be seen in the JDBC code samples, for example [here> ] for version 9.5.
The second method is much easier to implement. For example, to modify the URL in code:
    String databaseUrl = "jdbc:db2://localhost:50000/sample"
    + ":traceDirectory=c:\\temp"
    + ";traceFile=trace"
    + ";traceFileAppend=false"
    + ";traceLevel="
    + (com.ibm.db2.jcc.DB2BaseDataSource.TRACE_ALL)
    + ";";

If the URL is specified outside application code, specify a numeric value for traceLevel. For example:

jdbc:db2://localhost:50000/sample:traceDirectory=c:\\temp;traceFile=jcctrace.log;traceFileAppend=false;traceLevel=-1;

On Windows platforms, or

jdbc:db2://localhost:50000/sample:traceDirectory=/tmp;traceFile=jcctrace.log;traceFileAppend=false;traceLevel=-1;

On Linux/Unix platforms.

Applicable trace properties can be found in the section
'Enabling trace for a datasource connection' above. As in the datasource scenario, recent versions of the driver (3.63 or later) allow for circular tracing. For example, the URL

jdbc:db2://localhost:50000/sample:traceDirectory=/tmp;traceFile=jcctrace.log;traceFileCount=5;traceFileSize=20971520;traceLevel=-1;

Enables the trace with a maximum of five (5) files of 20 MB (20*1024*1024 bytes) each.

Multiple trace constants can be specified in traceLevel using numeric addition. Again, see the section 'Enabling trace for a datasource connection' above, for details on determining the desired numerical value to set traceLevel for a particular set of trace components.

For details on these traceLevel values, see the corresponding section in the document:

Common IBM Data Server Driver for JDBC and SQLJ properties for all supported database products

For more information on configuring a JCC trace for a DriverManager interface, see:

Connecting to a data source using the DriverManager interface with the IBM Data Server Driver for JDBC and SQLJ



Enabling dynamic trace

As of release 3.72 and 4.22 the JCC driver provides the capability to enable tracing on demand.

Dynamic tracing is enabled by configuration of a properties file. Create a file named DB2JccConfiguration.properties with the following entries:

db2.jcc.override.traceDirectory=/temp
db2.jcc.override.traceFile=jcctrace
db2.jcc.override.traceFileAppend=false
db2.jcc.override.traceLevel=-1
db2.jcc.tracePolling=false

Specify an appropriate location the application id will have permission to write the trace output to. The directory must already exist. Restart the application if already running for the properties file settings to take affect.

Deploy the properties file to the same location where the JCC jar (db2jcc.jar or db2jcc4.jar) resides or in any location referenced from CLASSPATH and ensure to restart the application or JVM for application server. Alternatively specify the properties files as a JVM argument.

-Ddb2.jcc.propertiesFile=/tmp/DB2JccConfiguration.properties

Enable tracing by updating the tracePolling entry in the properties file.

db2.jcc.tracePolling=true

Disable the tracing by updating the tracePolling entry in the properties file to false.

db2.jcc.tracePolling=false

Additionally use db2.jcc.tracePollingInterval for configuring the interval in seconds within which you want the trace directive changes to be picked up. This requires a restart of the application or application server (JVM) to take affect. Default value is 60 seconds. Every 60 seconds the JCC driver will poll the configuration for any trace directive changes.

db2.jcc.tracePollingInterval=



Collecting db2support

Once traces are collected, db2support output should also be collected for diagnostic and system information about DB2 and the platform, after traces have been collected. This should be done for the DB2 client, if Type 2 driver is used (i.e. driverType=2 in the datasource properties) and the DB2 server, if is on a distributed (Linux, Unix, Windows).
At the client (if a Type 2 connection is used):
    db2support <output path> -g -s
At the database server:
    db2support <output path> -d <database-name> -g -s



Submitting information to IBM Support

After collecting your information, you can begin Problem Determination through the product troubleshooting web page, or submit the diagnostic information to IBM support. Use the following document for instructions on submitting information to IBM Support.

Submitting diagnostic information to IBM Technical Support for problem determination



(Important) Notes regarding best practices:

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Programming Interface - JDBC","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;10.1;10.5;11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21196160