Problem diagnosis with the IBM Data Server Driver for JDBC and SQLJ

Testing a data server connection

Run the DB2Jcc utility to test a connection to a data server. You provide DB2Jcc with the URL for the data server, for IBM Data Server Driver for JDBC and SQLJ type 4 connectivity or IBM Data Server Driver for JDBC and SQLJ type 2 connectivity. DB2Jcc attempts to connect to the data server, and to execute an SQL statement and a DatabaseMetaData method. If the connection or statement execution fails, DB2Jcc provides diagnostic information about the failure.

Collecting JDBC trace data

Use one of the following procedures to start the trace:

Procedure 1: For IBM Data Server Driver for JDBC and SQLJ type 2 connectivity, the recommended method is to start the trace by setting the db2.jcc.override.traceFile property and the db2.jcc.t2zosTraceFile property in the IBM Data Server Driver for JDBC and SQLJ configuration properties file.

You can set the db2.jcc.tracePolling and db2.jcc.tracePollingInterval properties before you start the driver to allow you to change global configuration trace properties while the driver is running.

Procedure 2: For IBM Data Server Driver for JDBC and SQLJ type 4 connectivity, the recommended method is to start the trace by setting the db2.jcc.override.traceFile property or the db2.jcc.override.traceDirectory property in the IBM Data Server Driver for JDBC and SQLJ configuration properties file. You can set the db2.jcc.tracePolling and db2.jcc.tracePollingInterval properties before you start the driver to allow you to change global configuration trace properties while the driver is running.

Procedure 4:

If you use the DataSource interface to connect to a data source, invoke the javax.sql.DataSource.setLogWriter method to turn the trace on. With this method, TRACE_ALL is the only available trace level.

After a connection is established, you can turn the trace off or back on, change the trace destination, or change the trace level with the DB2Connection.setJccLogWriter method. To turn the trace off, set the logWriter value to null.

The logWriter property is an object of type java.io.PrintWriter. If your application cannot handle java.io.PrintWriter objects, you can use the traceFile property to specify the destination of the trace output. To use the traceFile property, set the logWriter property to null, and set the traceFile property to the name of the file to which the driver writes the trace data. This file and the directory in which it resides must be writable. If the file already exists, the driver overwrites it.

String url = "jdbc:db2://sysmvs1.stl.ibm.com:5021/san_jose" +
 ":traceFile=/u/db2p/jcctrace;" +
 "traceLevel=" + com.ibm.db2.jcc.DB2BaseDataSource.TRACE_DRDA_FLOWS + ";";

Procedure 6: Use DB2TraceManager methods. The DB2TraceManager class provides the ability to suspend and resume tracing of any type of log writer.

Example of starting a trace using configuration properties: For a complete example of using configuration parameters to collect trace data, see Examples of using configuration properties to start a JDBC trace.

Trace example program: For a complete example of a program for tracing under the IBM Data Server Driver for JDBC and SQLJ, see Example of a trace program under the IBM Data Server Driver for JDBC and SQLJ.

Collecting SQLJ trace data during customization or bind

To collect trace data to diagnose problems during the SQLJ customization or bind process, specify the -tracelevel and -tracefile options when you run the db2sqljcustomize or db2sqljbind bind utility.

Collecting diagnostic data for a connection to a Db2 data server or data sharing group

jccDump_timestamp_random-number_i.log

The maximum size of the file is approximately 2MB. When an application writes its first trace record, the driver creates a file with i = 0. The driver then writes trace data to the file. When the size of the file reaches approximately 2MB, the driver renames the file. The new name is the same as the original name, except that i is now 1. The driver continues this cycle until the application completes. Each dump entry inside the file includes the timestamp, JVM name, thread ID, data source ID, and connection ID details, so that you can locate the source of the exception.

Specifying the data to collect for a data server: You specify the SQL error codes or SQL warning codes for which you want diagnostic data to be collected by setting the db2.jcc.diagLevelExceptionCode global configuration property.

Specifying data collection for a data sharing group: When the db2.jcc.traceLevel or db2.jcc.override.traceLevel global configuration property, or the traceLevel Connection or DataSource property is set to TRACE_SYSPLEX (X'80000'), the IBM Data Server Driver for JDBC and SQLJ collects data about the data sharing group, such as the server list, automatic client reroute and workload balancing property values, transport pool statistics, and special register values.

Formatting information about an SQLJ serialized profile

The profp utility formats information about each SQLJ clause in a serialized profile. The format of the profp utility is:

Run the profp utility on the serialized profile for the connection in which the error occurs. If an exception is thrown, a Java stack trace is generated. You can determine which serialized profile was in use when the exception was thrown from the stack trace.

Formatting information about an SQLJ customized serialized profile

The db2sqljprint utility formats information about each SQLJ clause in a serialized profile that is customized for the IBM Data Server Driver for JDBC and SQLJ.

Run the db2sqljprint utility on the customized serialized profile for the connection in which the error occurs.