IBM Support

MustGather: Collect a DB2 JDBC Trace for WebSphere Application Server for z/OS

Troubleshooting


Problem

For problems that occur in WebSphere Application Server for z/OS with connections to DB2 the following documentation can be collected (MustGather) before contacting support so that the WebSphere Application Server z/OS Support team or DB2 z/OS JDBC team can help expedite problem resolution.

Diagnosing The Problem

If you have not contacted IBM, click: MustGather: Read first for WebSphere Application Server for z/OS.

Collecting the trace before opening a PMR will help the support team with quicker turnaround time.

This document describes 3 different methods of collecting a DB2 JDBC trace. The most common method and usually preferred one is outlined in step 1.

 
  1. Collecting a JDBC trace in its own file separate from WebSphere Application Server z/OS trace.
  2. Collecting a JDBC trace that is combined or interleaved with WebSphere Application Server z/OS trace.
  3. Dynamically enabling a JDBC trace in its own file separate from WebSphere Application Server z/OS trace.

Resolving The Problem

1. Collecting a JDBC trace in its own file separate from WebSphere Application Server trace.

Note: This trace requires a restart of the Application Server to enable the trace, and a restart of the Application Server to disable the trace. 
This trace can only be used in an application server running with a single servant.


  1. Login into the WebSphere administrative console.
    Follow the path:
    Resources > JDBC > JDBC Providers > DB2 Universal JDBC Driver Provider > data sources > [click  your data source name] (ie. DB2 Universal JDBC Driver data source) > Custom Properties
  2. Set custom property:
    traceLevel = -1
    which corresponds to TRACE_ALL.
  3. Then Follow the path to:
    Servers > Server Types > WebSphere Application Servers > [click  your server name] > Java and Process Management > Process Definition > Servant > Java virtual machine > Custom Properties.
  4. If the property db2.jcc.propertiesFile exists then use the file that it points to.
    If the property doesn't exist, create the property db2.jcc.propertiesFile and point it to a file in the HFS that the servant region has read permission to, for example:
    db2.jcc.propertiesFile = /tmp/db2jcctrace.properties
  5. Save all the changes and synchronize.
  6. Edit the file from the step above, db2jcctrace.properties and add the 3 properties below. Two types of traces will be collected in 2 separate files - Java trace (ex. javaTrace) and Native trace (ex. nativeTrace). The directory name should be modified to indicate where the files will be written to.

    db2.jcc.override.traceFile=/tmp/jcctrace/javaTrace
    db2.jcc.t2zosTraceFile=/tmp/jcctrace/nativeTrace
    db2.jcc.t2zosTraceBufferSize=1024

    Note:
    - The target HFS directory for these traces must already exist. The actual target trace files (ex. javaTrace and nativeTrace) will be created. The directory and file names shown above are only examples and can be changed.
    - The Java trace and Native trace must be configured as two separate files.
    - The Java trace is text readable
    - The Native trace is in binary and therefore not readable without additional formatting. It is only written when type 2 data source is used.
  7. Restart the server and re-create the problem.
  8. If native trace was generated, it needs to be converted to text using db2jcctrace formatter which is available in the /bin directory of the DB2 Universal Driver installation.
    Issue the following command from UNIX System Services shell:
    db2jcctrace fmt natTrace > nativeTrace.fmt

    Note:
    If formatting in an OMVS shell, you may need to execute the following export statements first:
    Below are examples, please correct the export to reflect your data set names and DB2 HFS location.
    export STEPLIB=DB2.DSNEXIT:DB2.DSNLOAD:DB2.SDSNLOD2
    export LD_LIBRARY_PATH=/db2v10/jdbc/lib
    export LIBPATH=/db2v10/jdbc/lib
  9. Please send in the following documentation:
    - Complete Control region job log output
    - Complete Servant region job log output
    - JDBC Java trace file (ex. javaTrace) in ASCII
    - Formatted JDBC native trace file (ex. nativeTrace.fmt) in ASCII


2. Collecting a JDBC trace that is combined or interleaved with WebSphere Application Server trace.

Note: This trace requires a restart of the Application Server to enable the trace, and a restart of the Application Server to disable the trace.
This trace can be used in an application server running with a one or more servants.


  1. Login into the WebSphere administrative console.
    Follow the path:
    Resources > JDBC > JDBC Providers > DB2 Universal JDBC Driver Provider > data sources > [click your data source name] (ie. DB2 Universal JDBC Driver data source) > Custom Properties
  2. Set custom property:
    traceLevel = -1
    which corresponds to TRACE_ALL.
  3. Follow the path:
    Troubleshooting > Logs and Trace > server name > Change log detail levels
    Set trace:
    WAS.database=all
  4. Save the changes, synchronize.
  5. Restart the server and re-create the problem.
  6. Send in the following documentation:
    - Complete Control region job log output
    - Complete Servant region job log output
    The JDBC trace will be in the SYSPRINT part of the job log along with the WebSphere traces.

3. Dynamically enabling a JDBC trace in its own file separate from WebSphere Application Server trace.

Note: This trace requires a restart of the Application Server to set a Java custom property. Once the custom property is in place, JDBC trace can be enabled and disabled dynamically at any time.  This trace can only be used in an application server running with a single servant.

Important: DB2 APAR PK68428 or JDBC Driver level 3.51.126 is needed in order to use this dynamic JDBC trace for DB2 option.

 
  1. Follow prior steps (1a) through (1e) to enable the JDBC trace with traceLevel=-1 and configure the jdbc properties file.
  2. Edit the jdbc properties file, db2jcctrace.properties and add the properties below. Two types of traces will be collected in 2 separate files - Java trace (ex. javaTrace) and Native trace (ex. nativeTrace). The directory name should be modified to indicate where the files will be written to.
    Update property db2.jcc.tracePollingInterval to a desired interval, in seconds. It enables the polling the IBM Data Server Driver for JDBC and SQLJ global configuration file for changes in trace directives.

    db2.jcc.override.traceFile=/tmp/jcctrace/javaTrace
    db2.jcc.t2zosTraceFile=/tmp/jcctrace/nativeTrace
    db2.jcc.t2zosTraceBufferSize=1024
    db2.jcc.tracePolling=true
    db2.jcc.tracePollingInterval=<interval in seconds>
    db2.jcc.override.traceLevel=0

    Note:
    - The target HFS directory for these traces must already exist. The actual target trace files (ex. javaTrace and nativeTrace) will be created. The directory and file names shown above are only examples and can be changed.
    - The Java trace and Native trace must be configured as two separate files.
    - The Java trace is text readable
    - The Native trace is in binary and therefore not readable without additional formatting. It is only written when type 2 data source is used.
  3. Restart the application server.
    The server should not create any trace as the property db2.jcc.override.traceLevel is set to a value of 0.
  4. When you are ready to enable the trace, modify the following variable in db2jcctrace.properties file, ex:
    db2.jcc.override.traceLevel=-1

    The change will be picked up within the interval specified by db2.jcc.tracePollingInterval property.
  5. After the problem has been captured in the trace, you can dynamically disable the trace by modifying the following variable in db2jcctrace.properties file, ex:
    db2.jcc.override.traceLevel=0
  6. If native trace was generated, it needs to be converted to text using db2jcctrace formatter which is available in the /bin directory of the DB2 Universal Driver installation.
    Issue the following command from UNIX System Services shell:
    db2jcctrace fmt natTrace > nativeTrace.fmt

    Note:
    If formatting in an OMVS shell, you may need to execute the following export statements first:
    Below are examples, please correct the export to reflect your data set names and DB2 HFS location.
    export STEPLIB=DB2.DSNEXIT:DB2.DSNLOAD:DB2.SDSNLOD2
    export LD_LIBRARY_PATH=/db2v10/jdbc/lib
    export LIBPATH=/db2v10/jdbc/lib
  7. Send in the following documentation:
    - Complete Control region job log output
    - Complete Servant region job log output
    - JDBC Java trace file (ex. javaTrace) in ASCII
    - Formatted JDBC native trace file (ex. nativeTrace.fmt) in ASCII.

Additional Resources:
DB2 for z/OS Information center
How to Enable a Connection Pool Trace for WebSphere Application Server

[{"Product":{"code":"SS7K4U","label":"WebSphere Application Server for z\/OS"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"DB Connections\/Connection Pooling","Platform":[{"code":"PF035","label":"z\/OS"}],"Version":"8.5;8.0;7.0","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
10 June 2021

UID

swg21661412