Problem diagnosis with the IBM Data Server Driver for JDBC and SQLJ
The IBM® Data Server Driver for JDBC and SQLJ includes diagnostic tools and traces for diagnosing problems during connection and SQL statement execution.
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 4 connectivity or IBM Data Server Driver for JDBC and SQLJ type 2 connectivity for Db2®, 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.
- Invoke the DB2BaseDataSource.setTraceLevel method
to set the type of tracing that you need. The default trace level
is
TRACE_ALL
. See Common IBM Data Server Driver for JDBC and SQLJ properties for all supported database products for a list of traceLevel settings. See Properties for the IBM Data Server Driver for JDBC and SQLJ for information on how to specify more than one type of tracing. - Invoke the DB2BaseDataSource.setJccLogWriter method to specify the trace destination and turn the trace on.
Procedure 3:
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.
- Invoke the DriverManager.getConnection method
with the
traceLevel
property set in the info parameter or url parameter for the type of tracing that you need. The default trace level isTRACE_ALL
. See Common IBM Data Server Driver for JDBC and SQLJ properties for all supported database products for a list of traceLevel settings. See Properties for the IBM Data Server Driver for JDBC and SQLJ for information on how to specify more than one type of tracing. - Invoke the DriverManager.setLogWriter method to specify the trace destination and turn the trace on.
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.
traceFile
and traceLevel
properties
as part of the URL when you load the driver. For example: 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 5: 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
- timestamp is the timestamp when the data is written.
- random-number is a randomly generated positive integer.
- i is 0 or 1.
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.
- The maximum file size is not exactly 2MB. It might exceed 2 MB so that the last dump entry is fully logged into a single file for easier readability.
- Bringing down a JVM by killing the Java™ process can create .lck files on the file system. These .lck files must be deleted manually.
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.
If a value of db2.jcc.diagLevelExceptionCode is not specified, no diagnostic data is collected.
If db2.jcc.diagLevelExceptionCode is set to -1, diagnostic information is collected for all SQL error codes.
An SQL error code can be specified without a sign, prefixed by a minus (-) sign, or suffixed by the letter n
. An SQL warning code can be prefixed by a plus (+) sign, or suffixed by the letter p
.
When more than one SQL error code or SQL warning code is specified, the codes must be separated by commas.
Examples:
- db2.jcc.diagLevelExceptionCode=204
- db2.jcc.diagLevelExceptionCode=-204
- db2.jcc.diagLevelExceptionCode=204n
- db2.jcc.diagLevelExceptionCode=+222
- db2.jcc.diagLevelExceptionCode=222p
- db2.jcc.diagLevelExceptionCode=-204,-30108,-4499
- db2.jcc.diagLevelExceptionCode=204,30108n,4499
- db2.jcc.diagLevelExceptionCode=204n,30108,4499n
- db2.jcc.diagLevelExceptionCode=+100,-30108,222p
- db2.jcc.diagLevelExceptionCode=100p,30108,+222
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
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.