Enabling JDBC Tracing for Liberty

JDBC tracing for Liberty is enabled either through a driver-specific custom trace setting, or using the application server supplemental JDBC tracing option.

In version 22.0.0.6 and later, see the documentation for JDBC driver tracing on the Open Liberty website.

About this task

There are two ways of using driver-specific custom trace facilities:

Using the Java™ built-in logging mechanism, java.util.logging, if the driver supports it.
Configuring a custom trace setting as a vendor property.

If your JDBC driver does not provide its own custom tracing or logging facilities, or the facilities it provides are minimal, you can use supplemental JDBC tracing from the application server.

If you enable tracing by using either a custom vendor property or supplemental JDBC tracing, you must add the logwriter name to the trace specification in the bootstrap.properties file. You can use any of the following logwriters:

DB2®: com.ibm.ws.db2.logwriter
Derby: com.ibm.ws.derby.logwriter
Informix® JCC (uses the same driver as DB2): com.ibm.ws.db2.logwriter
Informix JDBC: com.ibm.ws.informix.logwriter
Microsoft SQL Server JDBC Driver: com.ibm.ws.sqlserver.logwriter
DataDirect Connect for JDBC for Microsoft SQL Server: com.ibm.ws.sqlserver.logwriter
Sybase: com.ibm.ws.sybase.logwriter
Other databases (for example solidDB and MySQL): com.ibm.ws.database.logwriter

Because changes to trace enablement involve altering the bootstrap.properties file, you must restart the server for the changes to take effect.

Avoid trouble: Before you set a trace specification in the bootstrap.properties file, remove any trace specification from the server.xml file. Otherwise, in your trace log file, you see that initially the bootstrap setting takes effect and afterward, the server.xml setting overwrites this bootstrap setting. The useful logs are potentially lost.

The following examples illustrate the use of the various JDBC trace methods.

Procedure

Use java.util.logging.
If the driver you are using supports java.util.logging, you can enable it by appending the driver's trace level to com.ibm.ws.logging.trace.specification in the bootstrap.properties file. See the JDBC vendor documentation for levels and other trace information specific to your driver.
Here is an example for Microsoft SQL Server JDBC Driver:
- Example code for the bootstrap.properties file:
```
com.ibm.ws.logging.trace.specification=*=audit:com.microsoft.sqlserver.jdbc=FINE
```
Here is an example for Oracle JDBC or Oracle Universal Connection Pool (UCP):
- Example code for the bootstrap.properties file:
```
com.ibm.ws.logging.trace.specification=*=audit:oracle=FINE
```
- For Oracle, you must also enable tracing by setting the oracle.jdbc.Trace system property to true. Use one of the following two options:
  - In the bootstrap.properties file, add the oracle.jdbc.Trace=true setting.
  - In a Java program, add the System.setProperty("oracle.jdbc.Trace","true"); setting.

Use custom trace settings.

If the driver you are using has custom trace settings, you set them as JDBC driver vendor properties in the server.xml file. You also add the logwriter name to the trace specification in the bootstrap.properties file.

Here is an example for DB2 JCC, using the custom property traceLevel:

Example code for the server.xml file:

<dataSource id="db2" jndiName="jdbc/db2" jdbcDriverRef="DB2Driver" >
    <properties.db2.jcc databaseName="myDB" traceLevel="-1"/>
</dataSource>

Example code for the bootstrap.properties file:

com.ibm.ws.logging.trace.specification=*=audit:com.ibm.ws.db2.logwriter=all

Here is an example for Derby Network Client:

Example code for the server.xml file:

<dataSource id="derbyNC" jndiName="jdbc/derbyNC" jdbcDriverRef="DerbyNC" >
    <properties.derby.client databaseName="myDB" createDatabase="create" traceLevel="1"/>
</dataSource>

Example code for the bootstrap.properties file:

com.ibm.ws.logging.trace.specification=*=audit:com.ibm.ws.derby.logwriter=all

Here is an example for Informix JCC. This database uses the DB2 drivers for JCC connectivity.

Example code for the server.xml file:

<dataSource id="informixJCC" jndiName="jdbc/informixJCC" jdbcDriverRef="InformixDriverJCC" >
    <properties.informix.jcc databaseName="myDB" traceLevel="-1"/>
</dataSource>

Example code for the bootstrap.properties file:

com.ibm.ws.logging.trace.specification=*=audit:com.ibm.ws.db2.logwriter=all

Use supplemental JDBC tracing.

If your JDBC driver does not provide suitable tracing or logging facilities, you can use supplemental JDBC tracing from the application server. The application server automatically determines whether to enable supplemental JDBC tracing, based on the JDBC driver being used. To override this, set the data source property supplementalJDBCTrace to true or false.
1. Enable supplemental tracing.
  Here is an example for enabling supplemental tracing with the embedded Derby database. Supplemental JDBC tracing is enabled by default for this database, so you only need to set the logwriter in the bootstrap.properties file:
  - Example code for the bootstrap.properties file:
    com.ibm.ws.logging.trace.specification=*=audit:com.ibm.ws.derby.logwriter=all
  Here is an example for enabling supplemental tracing with Informix JDBC. Supplemental JDBC tracing is enabled by default for this database.
  - Example code for the bootstrap.properties file:
    com.ibm.ws.logging.trace.specification=*=audit:com.ibm.ws.informix.logwriter=all
  Here is an example for enabling supplemental tracing, and java.util.logging, with Microsoft SQL Server JDBC Driver:
  - Example code for the bootstrap.properties file:
    com.ibm.ws.logging.trace.specification=*=audit:com.ibm.ws.sqlserver.logwriter=all: com.microsoft.sqlserver.jdbc=all
  Here is an example for enabling supplemental tracing with DataDirect Connect for JDBC for Microsoft SQL Server:
  - Example code for the bootstrap.properties file:
    com.ibm.ws.logging.trace.specification=*=audit:com.microsoft.sqlserver.jdbc=all
  Here is an example for enabling supplemental tracing with solidDB. Supplemental JDBC tracing is enabled by default for this database.
  - Example code for the bootstrap.properties file:
    com.ibm.ws.logging.trace.specification=*=audit:com.ibm.ws.database.logwriter=all
  Here is an example for enabling supplemental tracing with Sybase. Supplemental JDBC tracing is enabled by default for this database.
  - Example code for the bootstrap.properties file:
    com.ibm.ws.logging.trace.specification=*=audit:com.ibm.ws.sybase.logwriter=all
  Here is an example for enabling supplemental tracing with other databases:
  - Example code for the bootstrap.properties file:
    com.ibm.ws.logging.trace.specification=*=audit:com.ibm.ws.database.logwriter=all
2. Disable supplemental tracing
  To disable supplemental JDBC tracing, either set the supplementalJDBCTrace data source property to false in the server.xml file, or remove the logwriter name from the com.ibm.ws.logging.trace.specification property in the bootstrap.properties file:
  - Example code for the server.xml file for solidDB:
    <dataSource id="soliddb" jndiName="jdbc/soliddb" jdbcDriverRef="solidDBDriver" supplementalJDBCTrace="false"> <properties databaseName="dba" URL="jdbc:solid://localhost:2315/dba/dba" /> </dataSource>
  - Example code for the bootstrap.properties file for solidDB:
    com.ibm.ws.logging.trace.specification=*=audit