IBM Support

Collecting Data: Tracing for the DB2 JDBC Type 2 Driver

Technote (FAQ)


Question

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.

Cause

Situation
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?

Impact
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?

Answer

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
Where 'tracefile' is the desired trace file name.
  • If the problem is intermittent or takes some time to occur:

      db2trc on -t -l 128M
This starts the trace with a 128 MB buffer, which will 'wrap' until the trace is dumped to file.

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):

      db2trc off
  • (If DB2 trace was enabled with the -l flag)

      db2trc dump tracefile.dmp

      db2trc off

  • 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

Don't forget to restart the application (that is, restart the JVM) so that the CLI configuration changes take effect.

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

Hints
  • 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

Related information

Additional Collecting Data documents

Product Alias/Synonym

DB2 LUW

Document information

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: