JDBC driver tracing for Open Liberty
You can enable tracing for your third-party JDBC driver in Open Liberty for debugging and support purposes. To collect a JDBC driver trace, you must specify your JDBC driver, set the logging level, and provide any required driver-specific configurations.
Understanding Open Liberty tracing
How you enable trace for your JDBC driver depends on whether the driver supports the java.util.logging
Java logging library or some other logging library, such as SLF4J or Log4J. In any case, you must configure the trace specification for your driver. Drivers that support the java.util.logging
library are specified by the package name that the driver uses.
Drivers that do not support the java.util.logging
library are specified by the log writer name for the JDBC driver. Open Liberty provides log writers for common database vendors that generate logs based on JDBC specification methods such as getConnection
and executeQuery
. Open Liberty also provides a generic log writer for drivers that do not support the java.util.logging
library and for which no driver-specific log writer exists.
When trace is enabled for your driver, you can configure custom trace settings as vendor properties.
For more information on Open Liberty tracing, see Log and trace configuration.
Enable trace for your JDBC driver
The following examples demonstrate how to enable trace for JDBC drivers. For driver-specific examples that include the necessary package or log writer names for common JDBC drivers, see Trace configuration examples for common JDBC drivers.
For JDBC drivers that support the java.util.logging
, specify the package name that the driver uses, as shown in the following server.xml
file example for the Microsoft SQL JDBC driver.
<logging traceSpecification="*=info:RRA=all:com.microsoft.sqlserver.jdbc=all" maxFiles=10 maxFileSize=100 />
This example also specifies the optional maxFiles
and maxFileSize
attributes, which configure limits on the size and number of files in your logs. For more information about these and other logging
element attributes, see Logging.
For JDBC drivers that do not support the java.util.logging
library, specify either the name of a driver-specific log writer or the generic Open Liberty log writer. The following server.xml
file example shows the trace specification for the Derby JDBC driver, which does not support the java.util.logging
library.
<logging traceSpecification="*=info:RRA=all:com.ibm.ws.derby.logwriter=all" />
Logging is initialized before the server.xml
file is processed. So, configuring logging through the server.xml
file can result in early log entries that use a different log configuration from later ones. To avoid this problem, you can configure the trace specification in your bootstrap.properties
file instead. The following bootstrap.properties
file example shows the trace specification for the Microsoft SQL Server DataDirect driver, which does not support the java.util.logging
library.
com.ibm.ws.logging.trace.specification=*=info:RRA=all:com.ibm.ws.sqlserver.logwriter=all
com.ibm.ws.logging.max.files=10
com.ibm.ws.logging.max.file.size=100
Drivers that support the java.util.logging
library can also be configured in the bootstrap.properties
file by specifying the package name for the driver instead of a log writer name.
Configure custom settings
If your JDBC driver has custom trace settings, you can configure these custom trace settings as JDBC driver vendor properties in the server.xml
file.
<logging traceSpecification="*=info:RRA=all:com.ibm.ws.db2.logwriter=all" ... />
<datasource ...>
<!-- traceLevel -1 is equivalent to ALL -->
<properties.db2.jcc traceLevel="-1"/>
</datasource>
In this example, the custom traceLevel
attribute for the DB2 JCC driver is set by using the driver-specific properties.db2.jcc subelement. For more information about the available driver-specific properties, see Data Source.
Trace configuration examples for common JDBC drivers
The following subsections contain JDBC driver specific configuration.
DB2 JCC
The DB2 JCC does not support the java.util.logging
library. This driver has its own logging implementations that output directly to a file by using a PrintWriter. Open Liberty provides the com.ibm.ws.db2.logwriter
log writer name for trace specification that configures a PrintWriter to output JCC driver trace to the ` trace.log` file. The following server.xml
file example shows you the configuration to enable trace for the DB2 JCC driver and specify a custom property.
<logging traceSpecification="*=info:RRA=all:com.ibm.ws.db2.logwriter=all" ... />
<datasource ...>
<!-- traceLevel -1 is equivalent to ALL -->
<properties.db2.jcc traceLevel="-1"/>
</datasource>
For more information about valid values for the traceLevel
attribute, see Table 1. DB2 JDBC trace constants.
Derby
The Derby driver does not support the java.util.logging
library. Open Liberty provides the com.ibm.ws.derby.logwriter
log writer name for trace specification. The following server.xml
file example shows you the configuration to enable trace for the Derby driver.
<logging traceSpecification="*=info:RRA=all:com.ibm.ws.derby.logwriter=all" />
Informix using JDBC
The Informix using JDBC driver supports the java.util.logging
library. The driver uses the com.informix
package name for trace specification. The following server.xml
file example shows you the configuration to enable trace for the Informix using JDBC driver.
<logging traceSpecification="*=info:RRA=all:com.informix=all" />
Informix using DB2 JCC
Informix using the DB2 JCC driver does not support the java.util.logging
library. This driver has its own logging implementations that output directly to a file by using a PrintWriter. Open Liberty provides the com.ibm.ws.db2.logwriter
log writer name for trace specification that configures a PrintWriter to output JCC driver trace to the trace.log
file. The following server.xml
file example shows you the configuration to enable trace for Informix by using the DB2 JCC driver and specify a custom property.
<logging traceSpecification="*=info:RRA=all:com.ibm.ws.db2.logwriter=all" />
<datasource ... >
<!-- traceLevel -1 is equivalent to ALL -->
<properties.informix.jcc traceLevel="-1"/>
</datasource>
For more information on additional valid values for the traceLevel, see Table 1. DB2 JDBC trace constants.
Microsoft SQL Server JDBC driver
The Microsoft SQL Server JDBC driver supports the java.util.logging
library. The driver uses the com.microsoft.sqlserver.jdbc
package name for trace specification. The following server.xml
file example shows you the configuration to enable trace for the Microsoft SQL Server JDBC driver.
<logging traceSpecification="*=info:RRA=all:com.microsoft.sqlserver.jdbc=all" />
Microsoft SQL Server DataDirect driver
The Microsoft SQL Server DataDirect driver does not support the java.util.logging
library. Open Liberty provides the com.ibm.ws.sqlserver.logwriter
log writer name for trace specification. The following server.xml
file example shows you the configuration to enable trace for the Microsoft SQL Server DataDirect driver.
<logging traceSpecification="*=info:RRA=all:com.ibm.ws.sqlserver.logwriter=all" />
Oracle
Prior to Oracle 23c, Oracle provided two different drivers, one for production and another for debugging purposes. The production driver does not produce any trace, so you need to download and replace your production driver with the debugging driver. The debugging driver has _g
in the driver name. For example, ojdbc8.jar
is ojdbc8_g.jar
. Configure the debugging driver by specifying the library
subelement within the jdbcDriver
element. The library
subelement defines the path to the debugging driver JAR file.
In Oracle 23c and later, Oracle no longer provides the _g
drivers and diagnostic trace is provided in the base JDBC driver.
The Oracle JDBC and Oracle JDBC debug drivers support the java.util.logging
library. The driver uses the oracle
package name for trace specification. The following server.xml
file example shows you how to configure the Oracle debugging driver and enable trace.
<logging traceSpecification="*=info:RRA=all:oracle=all" />
<jdbcDriver id="oracleDriver">
<library id="oracleDebug">
<file name="path_to_oracle_driver/ojdbcX[_g].jar"/>
</library>
</jdbcDriver>
For Oracle 23c and later, add the following option to the jvm.options
file to enable trace. To avoid the performance impact of logging trace strings, the Oracle JDBC driver does not log trace unless this property is set to true.
-Doracle.jdbc.diagnostic.enableLogging=true
Oracle customized tracing
By default, Oracle driver tracing is combined with Liberty tracing because both traces use the java.util.logging
library. However, Liberty also supports separating Oracle trace from Liberty trace. You can use this function to provide a stand-alone Oracle JDBC trace to Oracle support, if needed.
The following server.xml
file example shows you how to configure the Oracle debugging driver to produce separate trace. No trace specification is required.
<jdbcDriver id="oracleDriver">
<library id="oracleDebug">
<file name="path_to_oracle_driver/ojdbcX[_g].jar"/>
</library>
</jdbcDriver>
The remaining configuration is provided by system properties. Add the following required properties to the jvm.options
file to enable a new file-based logger.
-Doracle.jdbc.Trace=true
-DoracleLogFileName=<your-log-name>.log
-DoracleLogPackageName=<package-to-trace>
oracle.jdbc.Trace
: This property enables the global log handler for the Oracle JDBC driver.oracleLogFileName
: This property specifies the file name for custom logging. For example,oracle.log
.oracleLogPackageName
: This property specifies the package to trace. For example,oracle
,oracle.jdbc
, ororacle.net
You can also customize the configured trace in the jvm.options
file by using optional properties.
-DoracleLogFileSizeLimit=<size-in-bytes>
-DoracleLogFileCount=<number-of-files>
-DoracleLogFormat=<SimpleFormatter|XMLFormatter|your-fully-qualified-class>
-DoracleLogTraceLevel=<OFF|SEVERE|WARNING|INFO|CONFIG|FINE|FINER|FINEST|ALL>
The previous properties are optional. If no values are configured, Liberty uses the following default values:
oracleLogFileSizeLimit
: 0 (removes the size limit and the file can grow to any size)oracleLogFileCount
: 1 (all trace will be put into a single log file)oracleLogFormat
: SimpleFormatter (the simple formatter from java.util.logging)oracleLogTraceLevel
: INFO
For Oracle recommended settings, see the Oracle documentation.
PostgreSQL
The PostgreSQL driver version 42.0.0 and later supports the java.util.logging
library. The driver uses the org.postgresql
package name for trace specification. The following server.xml
file example shows the configuration to enable trace for the PostgreSQL driver.
<logging traceSpecification="*=info:RRA=all:org.postgresql=all" />
Prior to version 42.0.0, the PostgreSQL driver does not support the java.util.logging
library. Open Liberty provides the com.ibm.ws.postgresql.logwriter
log writer name for trace specification. The following server.xml
file example shows the configuration to enable trace for the PostgreSQL driver in versions earlier than 42.0.0.
<logging traceSpecification="*=info:RRA=all:com.ibm.ws.postgresql.logwriter=all" />
Sybase
The Sybase driver does not support the java.util.logging
library. Open Liberty provides the com.ibm.ws.sybase.logwriter
log writer name for trace specification. The following server.xml
file example shows you the configuration to enable trace for the Sybase driver.
<logging traceSpecification="*=info:RRA=all:com.ibm.ws.sybase.logwriter=all" />
Other databases
The generic com.ibm.ws.database.logwriter
log writer name is used for drivers that do not support the java.util.logging
library and for which Open Liberty does not provide a driver-specific log writer. The following server.xml
file example shows you how to enable trace with the generic log writer.
<logging traceSpecification="*=info:RRA=all:com.ibm.ws.database.logwriter=all" />