Using log4jdbc with DB2

Ever needed to know what was going on with your database calls? What parameters are being passed in my query? What query is being generated?

Log4JDBC can help... Here's my experience with getting it to work with a DB2 datasource...

The official project site describes log4jdbc like this “log4jdbc is a Java JDBC driver that can log SQL and/or JDBC calls (and optionally SQL timing information) for other JDBC drivers using the Simple Logging Facade For Java (SLF4J) logging system.”

Log4jdbc can be a useful tool for debugging database interactions; however, getting it to work when fetching a datasource using JNDI proved a little difficult for me initially.

Here’s how to use log4jdbc with a DB2 datasource.

After reading the documentation on the log4jdbc website, I couldn’t get my mind around how to wrap my DB2 connection with the “ConnectionSpy” class that log4jdbc uses.  That is when I’m simply doing a JNDI lookup to get a datasource configured in Websphere.

I found an article on using log4jdbc with oracle and websphere that pointed in the right direction:

http://code.google.com/p/log4jdbc/wiki/DataSourceExampleForWebSphere

This prompted me to create the following two classes that basically allow us to create our own datasource class and pooled connection, that allow us to wrap the connections with the log4jdbc ConnectionSpy class.

import java.sql.SQLException;

import java.util.Properties;

import javax.sql.PooledConnection;

import org.apache.log4j.PropertyConfigurator;

import com.ibm.db2.jcc.DB2ConnectionPoolDataSource;

public class DB2LoggedConnection extends DB2ConnectionPoolDataSource {

/**

*

*/

private static final long serialVersionUID = -3294438664490224135L;

public DB2LoggedConnection() throws SQLException {

super();

}

protected void initLogging() {

try {

PropertyConfigurator.configure(getClass().getClassLoader()

.getResource("log4jdbc_log4j.properties"));

} catch (Exception pEx) {

System.err.println("Error configuring log4jdbc logging: "

+ pEx.toString());

}

}

public PooledConnection getPooledConnection()

throws SQLException {

return new PooledLoggingConnection(super.getPooledConnection());

}

public PooledConnection getPooledConnection(Properties arg0)

throws SQLException {

return new PooledLoggingConnection(super.getPooledConnection(arg0));

}

public PooledConnection getPooledConnection(String arg0, String arg1)

throws SQLException {

return new PooledLoggingConnection(super.getPooledConnection(arg0, arg1));

}

}

package com.nationwide.db2;

import java.sql.Connection;

import java.sql.SQLException;

import javax.sql.ConnectionEventListener;

import javax.sql.PooledConnection;

import net.sf.log4jdbc.ConnectionSpy;

public class PooledLoggingConnection implements PooledConnection {    

protected PooledConnection parent;    

public PooledLoggingConnection( PooledConnection pConnection ) {     

parent = pConnection;   

}    

public void addConnectionEventListener( ConnectionEventListener pListener ) {     

parent.addConnectionEventListener( pListener );   }    

public void close() throws SQLException {     

parent.close();   

}   

public Connection getConnection() throws SQLException {

return new ConnectionSpy( parent.getConnection() ); // -- log4jdbc entry point!!!   

}    

public void removeConnectionEventListener( ConnectionEventListener pListener ) {     

parent.removeConnectionEventListener( pListener );   

}

}

I then jar’d these two classes up and put the jar on my local file system.  I then created a new JDBC Provider like below (NOTE: when creating you have to choose Custom JDBC Provider):

Then create a datasource that uses this provider:

You will have to create JAAS-J2C Authentication if your DB2 connection requires that.  

Defining connection properties…

However, you will also have to add several custom properties to set the server connection data…

Go to the datasource and click on “Custom Properties” and create a property for the following:

These will set the info on the DB2 datasource to allow it to connect.

Now, if you save this and restart the server, then try to test it, it will complain about not being able to load the log4jdbc classes…. So, make sure you add the following jars to your server’s classpath.  I did this by putting the jars in my server’s lib/ext folder…

C:\Program Files (x86)\IBM\SDP80\runtimes\base_v61\lib\ext

I’m using log4j as my logging implementation that’s why I needed the log4j-1.2.14.jar and the slf4j-log4j12-1.6.6.jar.  If you are using a different logging implementation you will need to add the appropriate jars.

After doing this, you should be able to restart the server and then test the connection from the admin console.

And now for setting up the logging…

All I had to do was to add the logger configs in my existing log4j.properties file.

It turns out I didn’t need to have a different config file for the log4jdbc logging config.  So, the initLogging() method above is not needed if you already have log4j working in your app.

Here’s a snippet of how I configure my log4j.

log4j.logger.jdbc.audit=DEBUG,stdout

log4j.additivity.jdbc.audit=false

#log4j.logger.java.sql=ERROR, stdout

#log4j.additivity.java.sql=false

! Log connection open/close events and connection number dump

log4j.logger.jdbc.connection=DEBUG,stdout

log4j.additivity.jdbc.connection=false  

! Log timing information about the SQL that is executed.

#log4j.logger.jdbc.sqltiming=DEBUG,stdout

#log4j.additivity.jdbc.sqltiming=false

log4j.logger.jdbc.sqlonly=DEBUG,stdout

log4j.additivity.jdbc.sqlonly=false

log4j.logger.jdbc.resultset=DEBUG,stdout

log4j.additivity.jdbc.resultset=false

The documentation on the log4jdbc site explains each of these settings.

My “stdout” appender is just a simple ConsoleAppender.

You’re datasource should be ready to use now.  Just change your app to use the logging datasource by changing the jndi name.

Have fun!