30 Diagnosability in JDBC

Configuring the CLASSPATH Environment Variable

Oracle ships several JAR files for each version of the JDBC drivers. The optimized JAR files do not contain any logging code and, therefore, do not generate any log output when used. To get log output, you must use the debug JAR files, which are indicated with a "_g" in the file name, like ojdbc5_g.jar or ojdbc6_g.jar. The debug JAR file must be included in the CLASSPATH environment variable.

Enabling Logging

You can enable logging in the following ways:

• Setting a Java system property

  You can enable logging by setting the oracle.jdbc.Trace system property.

  java -Doracle.jdbc.Trace=true ...
  

  Setting the system property enables global logging, which means that logging is enabled for the entire application. You can use global logging if you want to debug the entire application, or if you cannot or do not want to change the source code of the application.

• Programmatically

  You can programmatically enable or disable logging in the following way:

  First, get the ObjectName of the Diagnosability MBean. The ObjectName is

  com.oracle.jdbc:type=diagnosability,name=<loader>
  

  where, loader is a filtered version of the result of calling toString on the context ClassLoader.

  Now, write the following lines of code:

  // compute the ObjectName
  String loader = Thread.currentThread().getContextClassLoader().toString().replaceAll("[,=:\"]+", "");
  javax.management.ObjectName name = new javax.management.ObjectName("com.oracle.jdbc:type=diagnosability,name="+loader);
  
  // get the MBean server
  javax.management.MBeanServer mbs = java.lang.management.ManagementFactory.getPlatformMBeanServer();
  
  // find out if logging is enabled or not
  System.out.println("LoggingEnabled = " + mbs.getAttribute(name, "LoggingEnabled"));
  
  // enable logging
  mbs.setAttribute(name, new javax.management.Attribute("LoggingEnabled", true));
  
  // disable logging
  mbs.setAttribute(name, new javax.management.Attribute("LoggingEnabled", false));
  

  Programmatic enabling and disabling of logging helps you to control what parts of your application need to generate log output.

Configuring Logging

To generate a useful and detailed log, you must configure java.util.logging. This can be done either through a configuration file or programmatically.

A sample configuration file, OracleLog.properties, is provided as part of the JDBC installation in the demo directory. It contains basic information about how to configure java.util.logging and provides some initial settings that you can start with. You may use this sample file as is, edit the file and use it, rename the file and edit it, or create an entirely new file of any name.

To use a configuration file, you must identify it to the Java run-time. This can be done by setting a system property. For example:

java -Djava.util.logging.config.file=/jdbc/demo/OracleLog.properties.

It is read by the java.util.logging system. This file can reside anywhere.

You can use both java.util.logging.config.file and oracle.jdbc.Trace at the same time.

java -Djava.util.logging.config.file=/jdbc/demo/OracleLog.properties -Doracle.jdbc.Trace=true

You can use the default OracleLog.properties file. It may or may not get you the desired output. You can also create and use your own configuration file by following these steps:

1. Create a file named myConfig.properties. You can use any name that you wish to.

2. Insert the following lines of text in the file:

   .level=SEVERE
   oracle.jdbc.level=INFO
   oracle.jdbc.handlers=java.util.logging.ConsoleHandler
   java.util.logging.ConsoleHandler.level=INFO
   java.util.logging.ConsoleHandler.formatter=java.util.logging.SimpleFormatter
   

3. Save the file.

4. Set the system property to use this configuration file.

   java -Djava.util.logging.config.file=<filepath>/myConfig.properties ...
   

   where filepath is the path of the folder where you have saved the myConfig.properties file.

You can also configure java.util.logging to dump the log output into a file. To do so, modify the configuration file as follows:

.level=SEVERE
oracle.jdbc.level=INFO
oracle.jdbc.handlers=java.util.logging.FileHandler
java.util.logging.FileHandler.level=INFO
java.util.logging.FileHandler.pattern=jdbc.log
java.util.logging.FileHandler.count=1
java.util.logging.FileHandler.formatter=java.util.logging.SimpleFormatter

This will generate exactly the same log output and save it in a file named jdbc.log in the current directory.

You can control the amount of detail by changing the level settings. The defined levels from the least detailed to the most detailed are:

• OFF

  Turns off logging.

• SEVERE

  Logs SQLExceptions and internal errors.

• WARNING

  Logs SQLWarnings and bad but not fatal internal conditions.

• INFO

  Logs infrequent but significant events and errors. It produces a relatively low volume of log messages.

• CONFIG

  Logs SQL strings that are executed.

• FINE

  Logs the entry and exit to every public method providing a detailed trace of JDBC operations. It produces a fairly high volume of log messages.

• FINER

  Logs calls to internal methods.

• FINEST

  Logs calls to high volume internal methods.

• ALL

  Logs all the details. This is the most detailed level of logging.

In the example provided earlier, to reduce the amount of detail, change the java.util.logging.FileHandler.level setting from ALL to INFO:

java.util.logging.FileHandler.level=INFO

Although you can, it is not necessary to change the level of the oracle.jdbc logger. Setting the FileHandler level will control what log messages are dumped into the log file.

Using Loggers

Setting the level reduces all the logging output from JDBC. However, sometimes you need a lot of output from one part of the code and very little from other parts. To do that you must understand more about loggers.

Loggers exist in a tree structure defined by their names. The root logger is named "", the empty string. If you look at the first line of the configuration file you see .level=SEVERE. This is setting the level of the root logger. The next line is oracle.jdbc.level=INFO. This sets the level of the logger named oracle.jdbc. The oracle.jdbc logger is a member of the logger tree. Its parent is named oracle. The parent of the oracle logger is the root logger (the empty string).

Logging messages are sent to a particular logger, for example, oracle.jdbc. If the message passes the level check at that level, then the message is passed to the handler at that level, if any, and to the parent logger. So a log message sent to oracle.log is compared against that logger's level, INFO if you are following along. If the level is the same or less (less detailed) then it is sent to the FileHandler and to the parent logger, 'oracle'. Again it is checked against the level. If as in this case, the level is not set then it uses the parent level, SEVERE. If the message level is the same or less it is passed to the handler, which there isn't one, and sent to the parent. In this case the parent in the root logger.All this tree stuff didn't help you reduce the amount of output. What will help is that the JDBC drivers use several subloggers. If you restrict the log messages to one of the subloggers you will get substantially less output. The loggers used by Oracle JDBC drivers include:

• oracle.jdbc

• oracle.jdbc.driver

• oracle.jdbc.pool

• oracle.jdbc.rowset

• oracle.jdbc.xa

• oracle.sql

An Example

Suppose you want to trace what is happening in the oracle.sql component and also want to capture some basic information about the rest of the driver. This is a more complex use of logging. The following are the entries in the config file:

# 
     # set levels 
     # 
.level=SEVERE
oracle.level=INFO
oracle.jdbc.driver.level=INFO
oracle.jdbc.pool.level=OFF
oracle.jdbc.util.level=OFF
oracle.sql.level=INFO
     # 
     # configure handlers 
     # 
oracle.handlers=java.util.logging.ConsoleHandler
java.util.logging.ConsoleHandler.level=INFO
java.util.logging.ConsoleHandler.formatter=java.util.logging.SimpleFormatter

Let us consider what each line in the configuration file is doing.

.level=SEVERE

Sets the logging level of the root logger to SEVERE. We do not want to see any logging from other, non-Oracle components unless something fails badly. Therefore, we set the default level for all loggers to SEVERE. Each logger inherits its level from its parent unless set explicitly. By setting the level of the root logger to SEVERE we ensure that all other loggers inherit that level except for the ones we set otherwise.

oracle.level=INFO

We want log output from both the oracle.sql and oracle.jdbc.driver loggers. Their common ancestor is oracle. Therefore, we set the level of the oracle logger to INFO. We will control the detail more explicitly at lower levels.

oracle.jdbc.driver.level=INFO

We only want to see the SQL execution from oracle.jdbc.driver. Therefore, we set the level to INFO. This is a fairly low volume level, but will help us to keep track of what our test is doing.

oracle.jdbc.pool.level=OFF

We are using a DataSource in our test and do not want to see all of that logging. Therefore, we turn it OFF.

oracle.jdbc.util.level=OFF

We do not want to see the logging from the oracle.jdbc.util package. If we were using XA or rowsets we would turn them off as well.

oracle.sql.level=INFO

We want to see what is happening in oracle.sql. Therefore, we set the level to INFO. This provides a lot of information about the public method calls without overwhelming detail.

oracle.handlers=java.util.logging.ConsoleHandler

We are going to dump everything to stderr. When we run the test we will redirect stderr to a file.

java.util.logging.ConsoleHandler.level=INFO

We want to dump everything to the console which is System.err. In this case, we are doing the filtering with the loggers rather than the handler.

java.util.logging.ConsoleHandler.formatter=java.util.logging.SimpleFormatter

We will use a simple, more or less human readable format.

When you run your test with this configuration file, you will get moderately detailed information from the oracle.sql package, a little bit of information from the core driver code, and nothing from any other code.

You can also use XMLFormatter. XMLFormatter is the best choice for logs that you send to Oracle Support, because it enables us to more easily use automated processing of the log output.