Monitor database calls using log4jdbc jar file.

When testing WSO2 API manager gateway resource cache I needed to monitor database calls in order to verify whether resources are taken from the cache or not. In this blog post I will explain how to use "log4jdbc.jar" to monitor db calls.

Note: 

• I have used mysql database as my database connection. You can also use this for other databases as h2, Oracle.  
• Create database tables before configuring 'log4jdbc'. To do that you can remove 'log4jdbc.jar' from the /repository/components/lib folder start with -Dsetup, once server is started successfully shutdown server add log4jdbc.jar and restart. 

How to configure

1. Add 'log4jdbc.jar' to /repository/component/lib folder. You may also have the  JDBC connector jar in this folder based on the database type you are using. You can download 'log4jdbc.jar' from here 

2. Edit /repository/conf/datasources/master-datasources.xml by giving "net.sf.log4jdbc.DriverSpy" as the driverClassName and  specify URL as "jdbc:log4jdbc:mysql://localhost:3306/apim1100_carbon".

        

3. Add below entries to /repository/conf/log4j.properties file

#Log all JDBC calls except for ResultSet calls 
#Log timing information about the SQL that is executed. 
log4j.logger.jdbc.sqltiming=DEBUG,sqltiming 
log4j.additivity.jdbc.sqltiming=false 

#the appender used for the JDBC API layer call logging above, sql timing 
log4j.appender.sqltiming=org.apache.log4j.FileAppender 
log4j.appender.sqltiming.File=./repository/logs/sqltiming.log 
log4j.appender.sqltiming.Append=false 
log4j.appender.sqltiming.layout=org.apache.log4j.PatternLayout 

log4j.appender.sqltiming.layout.ConversionPattern=-----> %d{yyyy-MM-dd HH:mm:ss.SSS} %m%n%n 

4. you can navigate to /repository/logs and tail 'sqltiming.log' file in order to monitor which queries are been executed. 

Reference

[1]. http://cnapagoda.blogspot.com/2014/06/wso2-governance-registry-monitor.html