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>

Add the following option to the jvm.options file to ensure that all trace is produced.

-Doracle.jdbc.Trace=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>

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" />