JDBC Tools

installation | usage | extension

JDBC Tools Installation

Once you have downloaded the JDBC tools zip file, to start monitoring your JDBC application you need to take the following steps.

Note: These steps assume that you are going to start by using the pre-built JDBC Listener tools. There is guidance if you are going to use your own monitoring classes in the Extension guide.

1. Unzip the jdbc-tools.zip file.The zip file contains the following files:
   • jdbc-tools/README
   • jdbc-tools/LICENSE
   • jdbc-tools/docs/index.html
   • jdbc-tools/docs/documentation.html
   • jdbc-tools/config/jdbc-listener.properties
   • jdbc-tools/config/jdbc-tools.properties
   • jdbc-tools/lib/jdbc-listener.jar
   • jdbc-tools/lib/jdbc-tools.jar
2. Edit the delegate.driver entry in the jdbc-tools.properties file to contain the name of the database driver class that your application currently uses. For example, if you were using Oracle then the entry would look like:
   driver.delegate=oracle.jdbc.driver.OracleDriver
3. Ensure that the jdbc-tools.jar file and the jdbc-tools.properties file are on your applications Java CLASSPATH.
4. Replace the driver name in your application with com.facdatum.jdbc.listener.driver.DriverListener and add listener: after jdbc: in the connection string. For example, the URL jdbc:oracle:thin:@localhost:1521:testdb would become jdbc:listener:oracle:thin:@localhost:1521:testdb.
5. Run your application.

That's it! A jdbc-logging.log file should appear in the directory that you started the application from. The default configuration of JDBC Tools will start logging all calls, exits and exceptions from the JDBC layer.

If you want to know how to change this configuration then read the usage guide.

Notes on Application Server Installation

Installing JDBC Tools in an application server environment requires some variations on the steps above that are largely dependent on how JDBC Drivers and Datasources are set up in your application server.

• The jdbc-tools.jar file should be placed in the application servers CLASSPATH. This is likely to most easily be achieved by placing the jar file into the application server ext directory.
• The jdbc-tools.properties file should also be placed within the application servers CLASSPATH. Some application servers have a specific directory for placing configuration or properties files.
• If you are using the application server to directly provide the JDBC Driver then change the configuration of the current driver (say oracle.jdbc.driver.Driver) to jdbc.facdatum.jdbc.listener.driver.DriverListener and configure the jdbc-tools.properties file as described above.
• If you are accessing the database via a DataSource that is managed by the application server then you will need to do something similar to the following steps.
  1. Change the name of the current DataSource configuration. For example, if it is called jdbc/MyDataSource then call it jdbc/MyDataSourceDelegate.
  2. Create a new DataSource with the name of the DataSource that has just been changed. jdbc/MyDataSource in the current example.
  3. Configure this DataSource to use the jdbc.facdatum.jdbc.listener.driver.Driver JDBC Driver.
  4. Update the delegate.datasource entry in the jdbc-tools.properties file to contain the new name of the delegate DataSource. For example,
     driver.datasource=jdbc/MyDataSourceDelegate
  5. Ensure that the new configuration is active within the application server and run your application.

There are more instructions related to Application Server installation in the Usage guide.

JDBC Tools Usage

The usage of JDBC Tools is controlled through a set of properties. The download package includes 2 properties files:

• jdbc-listener.properties should be used if you are developing your own JDBCListener classes and contains the core listener properties.
• jdbc-tools.properties includes the jdbc-listener properties plus has the properties for controlling the pre-built listeners.

In the rest of this guide we will detail each of the properties.

JDBC Listener Properties

The JDBC Listener allows you to add modules to the JDBC Driver that can be used for monitoring JDBC calls. This is done by specifying the listener classes and the name of the delegate JDBC Driver or DataSource.

JDBC Listener Call Filter Properties

The JDBC Listener Call Filter allows the calls that will be monitored to be filtered based on either the JDBC method that is being called or the application code that is calling into the JDBC layer.

Each of the following filter properties is specified as a comma separated list of class expressions. The class expression can include an asterix (*) to denote a match of any number of characters. Examples of legal class expressions are:

• com.mypackage.MyClass.myMethod to match a specific method
• com.mypackage.MyClass.* to match all methods within a specified class
• com.mypackage.* to match all classes within a specified package
• *.myMethod to match all method calls of a specified name

JDBC Listener Duration Filter

The JDBC Listener Duration Filter allows the execution of listeners to be filtered based on the execution time of a SQL statement. In this respect, it is only applied to calls that include or act on an SQL string.

The properties that can be used to control this listener filter are as follows.

<sql string>:<duration in milliseconds>
<sql string>:<duration in milliseconds>
...

JDBC Logger Properties

The JDBC Logger logs all calls that it receives through to the JDBC Driver. It will log JDBC method calls and returns and SQLExceptions. The log is in the format:

timestamp|duration|calling method|message type|message

Where the message format is dependent on the message type:

• CALL: object@JDBC interface method name/JDBC driver method name(args...)
  • If method is executing a piece of SQL then the parameters are listed after the standard message format.
• EXIT: object@JDBC interface method name/ JDBC driver method name:return
• FAIL: object@JDBC interface method name/JDBC driver method name:Exception

JDBC Planner Properties

The JDBC Planner is dependent on the JDBC Logger and will log the query execution plan for SQL statements the first time they are called. These appear as PLAN messages in the log, where the query plan lines form the messsage body separated by ^ symbols.

Oracle, Microsoft SQL Server and MySQL are supported by the default configuration. Instructions on how to add additional database platform support are provided in the Extension documentation.

Extending JDBC Tools

There are two areas of JDBC Tools that allow for extension. These are to enable you to build your own monitoring plugin that will run within the JDBC listener infrastructure, and the second is to enable the query planning plugin to work on different database platforms. We take you through each of these steps in the following sections.

Building your own JDBC Listener

If you want to create your own monitoring code to plugin into the JDBC Tools framework then you simply need to implement a single Interface, the JDBCListener.

package com.facdatum.jdbc.listener;

public interface JDBCListener {

/**
 * Method called before listener is used to set up any necessary
 * infrastructure.
 */
void init();

/**
 * Method called before a JDBC method is called on a JDBC interface.
 * @param event the details of the method call
 */
void acceptBegin(ListenerCallEvent event);

/**
 * Method called after a JDBC method is called on a JDBC interface.
 * @param event the details of the method return
 */
void acceptEnd(ListenerReturnEvent event);

/**
 * Method called when an exception is thrown by a JDBC method
 * on a JDBC interface.
 * @param event the details of the exception thrown by the method
 */
void acceptException(ListenerExceptionEvent event);
}

The events that are passed into each of the acceptXXX methods contain all the information you need about the call that is being made to implement your monitoring code. Each inherits from the base ListenerEvent Interface.

package com.facdatum.jdbc.listener;

public interface ListenerEvent {

/**
 * Returns the time of the JDBC event.
 * @return the event time
 */
long getTime();

/**
 * Returns the object called on the JDBC Layer.
 * @return the call object
 */
Object getObject();

/**
 * Returns the stack trace of calls into the JDBC layer.
 * @return the stack trace
 */
StackTraceElement[] getStackTrace();

/**
 * Helper method to return JDBC Method call from the stack trace.
 * @return the JDBC Method call
 */
String getJDBCMethod();

/**
 * Helper method to return the JDBC Method call from the JDBC interface
 * that has been called in the stack trace.
 * @return the method call on the JDBC Interface
 */
String getJDBCInterfaceMethod();

/**
 * Helper method to return the method that called into the JDBC Layer
 * from the stack trace.
 * @return the calling method
 */
String getCallingMethod();

/**
 * Sets whether subsequent calls to listeners should be filtered or not.
 * @param filter set to true to filter all calls to subsequent listeners
 */
void setFilterListeners(boolean filter);

/**
 * Returns whether subsequent calls to listeners for this call should
 * be filtered or not.
 * @return true if listeners should be filtered; false otherwise
 */
boolean getFilterListeners();
}

This is extended for each of the specific details of a call, return or exception.

package com.facdatum.jdbc.listener;

public interface ListenerCallEvent extends ListenerEvent {

/**
 * Returns the arguments to the JDBC method that was called.
 * @return the arguments
 */
Object[] getArgs();
}

public interface ListenerReturnEvent extends ListenerEvent {

/**
 * Returns the result from the call to the JDBC method.
 * @return the return value
 */
Object getResult();

/**
 * Returns the wrapped result from the call to the JDBC method. Added in
 * jdbc-tools version 1.1.)
 * @return the wrapped return value; or null if result is not wrapped
 */

/**
 * Returns the duration of the JDBC method call.
 * @return the duration
 */
long getDuration();
}

public interface ListenerExceptionEvent extends ListenerEvent {

/**
 * Returns the exception that was thrown by the JDBC method that
 * was called.
 * @return the arguments
 */
Exception getException();

/**
 * Returns the duration of the JDBC method call.
 * @return the duration
 */
long getDuration();
}

Once you have implemented the JDBCListener interface and used the ListenerEvents to get the information you require, you can plug your code into the JDBC Tools infrastructure by editing the jdbc-listener.properties property file and adding your JDBCListener class to the jdbc.listener.classes property.

jdbc.listener.classes=mypackage.MyListenerClass

You can then run the application you want to monitor, ensuring that the jdbc-listener.properties, jdbc-listener.jar and your class files are on the CLASSPATH. (Note: if you want to use your listener in combination with some of the pre-built listeners then you should use the jdbc-tools.properties and jdbc-tools.jar files.)

Additional Notes:

• If you are implementing a filter then you should perform your check in the appropriate method and then call ListenerEvent.setFilterListeners(true). For it to have an effect, it needs to be placed before other listeners in the jdbc.listener.classes list.
• To implement different functionality for different JDBC calls, your method needs to check the JDBC Interface name (ListenerEvent.getJDBCInterfaceMethod()) and perform the required operations based on the returned value.

Porting the Query Planner JDBC Listener

A GenericPlanStatements query planner has been implemented which can be configured using a properties file. To create a new query planner properties you need to take the following steps.

• Create a new directory com/facdatum/jdbc/planner/generic that is going to be picked up on the CLASSPATH of the application. (For example, if the directory config is on the CLASSPATH then create the new directory hierarchy within there.)
• Create a properties file in the directory called GenericPlanStatements_<dbname>, where <dbname> is replaced with the database product name that is returned when calling getDatabaseProductName() on java.sql.DatabaseMetaData. (For example, an Oracle database properties file would be called GenericPlanStatements_oracle.)
• Create entries in the properties file for the keys generateSQL, displaySQL and cleanupSQL.
  • generateSQL should contain the SQL that needs to be executed to create the query plan on the chosen database platform.
  • displaySQL should contain the SQL that needs to be executed to return the query plan on the chosen database platform.
  • cleanupSQL should contain the SQL that needs to be executed to delete the query plan on the chosen database platform.
  • supportsParameters should contain true if the database platform you are using supports parameters in query plan SQL. Otherwise false should be specified.

  Two parameters are passed to these statements '%1$s' contains the statement reference and '%2$s' contains the SQL that will be planned. These should be used appropriately in the properties.

Once you have done this and set the JDBCPlanner properties explained in the Usage documentation, you should see the query plans being logged. Here is an example of the GenericPlanStatements property file for Oracle.

generateSQL=explain plan set statement_id = '%1$s' for %2$s
displaySQL=select * from table(DBMS_XPLAN.DISPLAY( \
'PLAN_TABLE','%1$s','ALL'))
cleanupSQL=delete from plan_table where statement_id = '%1$s'

If the way your database generates query plans does not fit into the GenericPlanStatements structure then you can implement the IPlanStatements interface.

package com.facdatum.jdbc.planner;

...

public interface IPlanStatements {

/**
 * Returns the SQL string that will run the generate query execution plan
 * step on the supported platform.
 * @param statementID the id of the planned statement
 * @param sql the sql statement to be planned
 * @return the execution plan query string
 */
String getGenerateStep(String statementID, String sql);

/**
 * Returns the SQL string that will display the results of a previously
 * generated execution plan.
 * @param statementID the id of the planned statement
 * @param sql the sql statement to be planned
 * @return the display plan query string
 */
String getDisplayStep(String statementID, String sql);

/**
 * Returns the SQL string that will remove the generated plans resources.
 * @param statementID the id of the planned statement
 * @return the delete plan query string
 */
String getCleanupStep(String statementID);

/**
 * Returns whether the driver supports parameters within SQL used in
 * query plans.
 * @return true if parameters are supported; false otherwise
 */
boolean supportsParameters();

/**
 * Sets the connection to be used when running the query plan steps.
 * @param conn the connection
 * @throws SQLException if there is a problem connecting
 * @throws IOException if there is a problem connecting
 */
void setConnection(Connection conn) throws SQLException, IOException;
}

You then need to specify your class in the com/facdatum/jdbc/planner/PlanStatementFactory.properties file. By default this has the entry:

planClass=com.facdatum.jdbc.planner.generic.GenericPlanStatements

Ensure that your version of this properties file appears before the jdbc-tools.jar in the CLASSPATH.