Enable JDBC Tracing for the Liberty profile

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

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

• Use the Java built-in logging mechanism, java.util.logging, if the driver supports it.

• Configure a custom trace setting as a vendor property.

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

If we enable tracing using either a custom vendor property or supplemental JDBC tracing, add the logwriter name to the trace specification in bootstrap.properties. We 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 bootstrap.properties, we must restart the server for the changes to take effect.

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

• Use java.util.logging.

  If the driver we are using supports java.util.logging, we can enable it by appending the driver's trace level to com.ibm.ws.logging.trace.specification in bootstrap.properties. See Use Java logging in an application, and the JDBC vendor documentation for levels and other trace information specific to the driver.

  Here is an example for Microsoft SQL Server JDBC Driver:

  • Example code for bootstrap.properties:
    com.ibm.ws.logging.trace.specification=*=audit=enabled:com.microsoft.sqlserver.jdbc=FINE

  Here is an example for Oracle JDBC:

  • Example code for bootstrap.properties:
    com.ibm.ws.logging.trace.specification=*=audit=enabled:oracle=FINE

  • For Oracle, we must also enable the tracing using the system property oracle.jdbc.Trace, using one of the following two options:

    • In bootstrap.properties, add the setting oracle.jdbc.Trace=true

    • In a Java program, add the setting System.setProperty("oracle.jdbc.Trace","true");

• Use custom trace settings.

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

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

  • Example code for server.xml:

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

  • Example code for bootstrap.properties:
    com.ibm.ws.logging.trace.specification=*=audit=enabled:com.ibm.ws.db2.logwriter=all=enabled

  Here is an example for Derby Network Client:

  • Example code for server.xml:

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

  • Example code for bootstrap.properties:
    com.ibm.ws.logging.trace.specification=*=audit=enabled:com.ibm.ws.derby.logwriter=all=enabled

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

  • Example code for server.xml:

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

  • Example code for bootstrap.properties:
    com.ibm.ws.logging.trace.specification=*=audit=enabled:com.ibm.ws.db2.logwriter=all=enabled

• Use supplemental JDBC tracing.

  If the JDBC driver does not provide suitable tracing or logging facilities, we 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 we only need to set the logwriter in bootstrap.properties:

     • Example code for bootstrap.properties:
       com.ibm.ws.logging.trace.specification=*=audit=enabled:com.ibm.ws.derby.logwriter=all=enabled

     Here is an example for enabling supplemental tracing with Informix JDBC. Supplemental JDBC tracing is enabled by default for this database.

     • Example code for bootstrap.properties:
       com.ibm.ws.logging.trace.specification=*=audit=enabled:com.ibm.ws.informix.logwriter=all=enabled

     Here is an example for enabling supplemental tracing, and java.util.logging, with Microsoft SQL Server JDBC Driver:

     • Example code for bootstrap.properties:

       com.ibm.ws.logging.trace.specification=*=audit=enabled:com.ibm.ws.sqlserver.logwriter=all=enabled:
           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 bootstrap.properties:
       com.ibm.ws.logging.trace.specification=*=audit=enabled: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 server.xml:

       <dataSource id="soliddb" jndiName="jdbc/soliddb" jdbcDriverRef="solidDBDriver">
           <properties databaseName="dba" URL="jdbc:solid://localhost:2315/dba/dba" />
       </dataSource>

     • Example code for bootstrap.properties:
       com.ibm.ws.logging.trace.specification=*=audit=enabled:com.ibm.ws.database.logwriter=all=enabled

     Here is an example for enabling supplemental tracing with Sybase. Supplemental JDBC tracing is enabled by default for this database.

     • Example code for bootstrap.properties:
       com.ibm.ws.logging.trace.specification=*=audit=enabled:com.ibm.ws.sybase.logwriter=all=enabled

     Here is an example for enabling supplemental tracing with other databases:

     • Example code for bootstrap.properties:
       com.ibm.ws.logging.trace.specification=*=audit=enabled:com.ibm.ws.database.logwriter=all=enabled

  2. Disable supplemental tracing

     To disable supplemental JDBC tracing, either set the supplementalJDBCTrace data source property to false in server.xml, or remove the logwriter name from the com.ibm.ws.logging.trace.specification property in bootstrap.properties:

     • Example code for server.xml 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 bootstrap.properties for solidDB:
       com.ibm.ws.logging.trace.specification=*=audit=enabled

Parent topic: Deploy data access applications to the Liberty profile

Reference:

Logging and Trace