Enabling Trace Options for IBM i Developer Kit for Java JDBC Applications

This document describes the methods that are available to trace the IBM i Developer Kit for Java JDBC driver (com.ibm.db2.jdbc.app.DB2Driver). Most trace options can be enabled by using JVM properties. Some trace options can also be set by using environment variables as described in the following table. Trace settings are read when a JDBC connection is made. The trace ends when all connections being traced are closed.
 

JVM Property	Environment Variable	Connection Property	Description
jdbc.db2.trace	QIBM_JDBC_TRACE_LEVEL	trace=true *The connection property is limited to true or false.	Traces JDBC program flow with increasing levels of detail. Possible values are 0-4. For more information, see trace level.
jdbc.db2.trace.config	QIBM_JDBC_TRACE_CONFIG		File name to direct trace output to. Default is standard output. Possible values: stdout file://[path/]<filename> Notes: 1. The directories in the path must exist. 2. Include %j in the file name and the job number is inserted in the file name.
jdbc.db2.cli.trace	<none>		Adds a subset of CLI calls to trace output. Possible values are true and false.
jdbc.db2.malloc.trace	<none>		Adds detailed memory allocation information to trace output. Possible values are true and false.
jdbc.db2.trace.monitor=true	<none>		Enable Dynamic tracing. Possible values are true and false. For more information, see Native JDBC dynamic tracing.
<none>	<none>	"server trace=n"	Provides more diagnostic options, such as job log and database monitor. See the Server Trace section for more detail.

Note: The file created by the jdbc.db2.trace.config JVM property is limited to a 2 GB maximum file size, at which point tracing stops. If this limit is being encountered, consider the dynamic tracing option to be more selective about when tracing is enabled.

Property: jdbc.db2.trace (QIBM_JDBC_TRACE_LEVEL)

The property and environment variable can be set to one of the options shown in the next table.

No Trace	'0'	No tracing.
Error Level	'1'	Trace failures only.
Info Level	'2'	JDBC trace and errors.
Verbose Level	'3'	All JDBC calls and internal calls.
Extended Level	'4'	All JDBC trace points except CLI and malloc.

Server Trace

The value is a combination of the following options. To enable multiple options, add the values. For example, database monitor, job debug and save job log would be a value of 14.
 

SERVER_TRACE_START_DATABASE_MONITOR	2	Start the database monitor on the JDBC server job. Output file is QUSRSYS/QSQL123456 where 123456 is the job number of the QSQSRVR job that was traced.
SERVER_TRACE_DEBUG_SERVER_JOB	4	Start debug on the JDBC server job.
SERVER_TRACE_SAVE_SERVER_JOBLOG	8	Save the job log when the JDBC server job ends.
SERVER_TRACE_TRACE_SERVER_JOB	16	Start job trace on the JDBC server job.
SERVER_TRACE_SAVE_SQL_INFORMATION	32	Save SQL information.

Depending on the type of application, one of the following methods is used to set trace options:

Setting the trace in Qshell
For console applications that can be run from the Qshell environment (STRQSH command), the JVM properties can be set with the Java command when the program is run:
 

Java -Djdbc.db2.trace=4 -Djdbc.db2.trace.config=file:///adirectory/jdbcLog.%j.txt -Djdbc.db2.cli.trace=true -Djdbc.db2.malloc.trace=false MyClass

Setting the trace in RUNJVA
To specify JVM properties on the RUNJVA or JAVA commands use the "PROP" field (press F10=Additional parameters):
 

RUNJVA CLASS(MyClass) PROP((jdbc.db2.trace '4') (jdbc.db2.trace.config 'file:///adirectory/jdbcLog.%j.txt') (jdbc.db2.cli.trace 'true'))

Setting the trace by using environment variables
If only the basic trace is required, the environment variables can be used rather than the JVM properties. Before you issue the RUNJVA or STRQSH commands, use the following to update the environment variables for the current job:
 

ADDENVVAR ENVVAR(QIBM_JDBC_TRACE_LEVEL) VALUE('4') LEVEL(*JOB)
ADDENVVAR ENVVAR(QIBM_JDBC_TRACE_CONFIG) +
VALUE('file:///adirectory/jdbcLog.%j.txt') LEVEL(*JOB)

If the job that contains the JVM cannot be determined, it might not be possible to set the environment variables for the job before the JDBC connection is made. In this case, it might be necessary to set the environment variable for the system before you launch the application. Note, this option traces all JDBC applications on the system.
 

ADDENVVAR ENVVAR(QIBM_JDBC_TRACE_LEVEL) VALUE('4') LEVEL(*SYS)
ADDENVVAR ENVVAR(QIBM_JDBC_TRACE_CONFIG) +
VALUE('file:///adirectory/jdbcLog.%j.txt') LEVEL(*SYS)

IBM WebSphere Advanced Server

If the application being traced is a WebSphere Application Server, then command-line arguments can be specified that use the WebSphere advanced administrative console. The following is a typical JVM property string used in a WebSphere environment:
 

-Djdbc.db2.trace=3 -Djdbc.db2.trace.config=file:///tmp/jdbcLog.%j.txt -Djdbc.db2.cli.trace=true

Refer to the following document for screen captures showing where the JVM properties are updated on various WebSphere versions:

WebSphere Screen Captures for Native JDBC

Connection Properties
To enable traces set by a connection property, the application or data source must be modified to include the new property.

Related Information
For more information about the dynamic trace, see Native JDBC Dynamic Tracing.