Collecting Data: Tracing for the DB2 JDBC Type 2 Driver
What information should be collected when I experience a problem with with an application using the legacy CLI-based DB2 JDBC Type 2 Driver?
Collecting the information below, before engaging IBM support, will help you understand the problem more clearly and save time by allowing IBM to more quickly diagnose the problem.
This section lists questions which should be considered, regarding the conditions under which the problem occurred.
- Is this a reoccurring issue or the first time experiencing the issue?
- Have there been any recent changes to the instance or database cfg files (dbm cfg or db cfg)?
- Have there been any recent changes to your operating system?
- Are you able to reproduce this behavior? If so, can a testcase be provided?
This section lists questions to consider regarding the effects of the problem.
- Is this a production, development, or test environment?
- How many users are affected by this problem?
- What is the business impact of this problem?
- Are there other repercussions to the problem occurring?
Information to Collect
This section lists the required information for DB2 Support to analyze.
In addition to answers for the questions above, the most commonly useful information for diagnosing a problem with a DB2 JDBC Type 2 application is a coordinated JDBC and CLI trace, and client-side DB2 trace. Often the root cause can be at the server or the internal layers lower than DB2 CLI API, so if it is feasible, you should collect both the JDBC/CLI and DB2 traces concurrently.
Finally, it is recommended to enable application tracing at the JDBC connection manager level (and transaction level, if the problem is related to XA). Please consult the application vendor's documentation on how best to accomplish this.
Enabling JDBC and CLI traces
To collect the JDBC, CLI and DB2 trace (optional, recommended) on the problem, please follow the steps below:
1) Turn on CLI trace
From a DB2 Command Window, execute the following commands:
db2 UPDATE CLI CFG FOR SECTION COMMON USING Trace 1
db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceFlush 1
db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceFlushOnError 1
db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceComm 1
db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceTimestamp 3
db2 UPDATE CLI CFG FOR SECTION COMMON USING TracePathName <existing path>
where the 'existing path' must already exist, should be empty and must be writeable by the application. For example, C:\clitrace for Windows or /tmp/clitrace for Unix/Linux.
2) Enable JDBC Trace
From a DB2 Command Window, execute the following commands:
db2 UPDATE CLI CFG FOR SECTION COMMON USING JDBCTrace 1
db2 UPDATE CLI CFG FOR SECTION COMMON USING JDBCTracePathName <existing path>
db2 UPDATE CLI CFG FOR SECTION COMMON USING JDBCTraceFlush 1
where the 'existing path' is the same as for the CLI trace.
3) Verify the trace settings, and restart the application
You can verify the above keywords settings with the following command:
db2 GET CLI CFG FOR SECTION COMMON
Note that the db2cli.ini file is only read by the application when the application starts. Therefore, you must restart the application for the above changes to take effect.
For JDBC applications, that means that restarting the application Server JVM (e.g. restarting the Deployment Manager in Websphere Application Server) is required to enable and disable JDBC/CLI tracing.
4) Start the DB2 trace (optional, recommended)
Use the commands below unless otherwise specified By DB2 support.
- If the problem is easily reproducible:
db2trc on -t -f tracefile.dmp
- If the problem is intermittent or takes some time to occur:
db2trc on -t -l 128M
5) Recreate the problem with the application
6) Stop the DB2 trace and disable JDBC/CLI traces
- (If DB2 trace was enabled with the -f flag):
- (If DB2 trace was enabled with the -l flag)
db2trc dump tracefile.dmp
- disable the JDBC/CLI trace
db2 UPDATE CLI CFG FOR SECTION COMMON USING Trace 0
db2 UPDATE CLI CFG FOR SECTION COMMON USING JDBCTrace 0
7) Format the DB2 trace
If DB2 traces were enabled, format the traces with the following commands:
db2trc fmt tracefile.dmp tracefile.fmt
db2trc flw tracefile.dmp tracefile.flw
db2trc fmt -c tracefile.dmp tracefile.fmtc
where 'tracefile' is the desired trace file name
8) Collect db2support
db2support output should also be collected for diagnostic and system information about DB2 and the platform, after traces have been collected.
At the client:
db2support <output path> -g -s
And on the database server (if the server is on a distributed, rather than host, platform):
db2support <output path> -d <database-name> -g -s
Submitting information to IBM Support
Once you have collected your information, you can begin Problem Determination through the product Support web page, or simply submit the diagnostic information to IBM support. Use the document below for submitting information to IBM Support.
Submitting diagnostic information to IBM Technical Support for problem determination
- Note that turning on JDBC/CLI trace (and DB2 trace) can cause significant performance impact on all the applications running on the same system, so be sure to turn off the trace as soon as the problem is reproduced, and minimize the number of running applications to reduce the size of the trace files.
- If the problem is not reproducible on demand, or if enabling both the JDBC and CLI traces (in addidtion to application-level tracing) causes unacceptable performance degradation or fills up the log directory, you may consider turning on the CLI trace only. The CLI trace, once enabled, can then be turned on or off dynamically using the CLI keyword TraceRefreshInterval. See the following technote for details:
MustGather: CLI Applications
More support for:
DB2 for Linux, UNIX and Windows
Programming Interface - JDBC
Software version: 9.1, 9.5, 9.7
Operating system(s): AIX, HP-UX, Linux, Solaris, Windows
Reference #: 1388460
Modified date: 23 January 2015
Translate this page: