Background Image

DOCUMENTATION

?

Shortcut

PrevPrev Article

NextNext Article

Larger Font Smaller Font Up Down Go comment Print

Introduction: When using the CUBRID JDBC, you may often want to check the query logs as well as other JDBC logs. This document describes how to analyze JDBC logs by using log4jdbc.

Table of Contents

Enviromnment System

This environment system of this document is as follows:
CUBRID 2008 or higher

^ Top of page

Preparing Required Libraries

Preparing log4jdbc and slf4j Related Libraries

1. http://code.google.com/p/log4jdbc/ 

A. log4jdbc3-1.1.jar (JDK 1.4 or 1.5) – CUBRID jdbc currently supports JDK 1.5.

2. http://www.slf4j.org/ 

A. slf4j-api-1.5.0.jar (providing APIs for interworking of logging service with log4jdbc)
B. slf4j-log4j12-1.5.2.jar (providing the implementation libraries for interworking of Log4j-based logging service with log4jdbc)

^ Top of page

Selecting the Java Logging System

Install the following four libraries (3 required libraries + 1 optional library) and continue to the next step.

1.    Log4j
2.    java.util logging in JDK 1.4
3.    logback
4.    Jakarta Commons Logging

(This document is written based on log4j.)

^ Top of page

Specifying the JDBC Driver Class in Net.sf.log4jdbc.DriverSpy

List of Default Drivers Supported in log4jdbc

Driver Class Database Type
oracle.jdbc.driver.OracleDriver Oracle
com.sybase.jdbc2.jdbc.SybDriver Sybase
net.sourceforge.jtds.jdbc.Driver jTDS SQL Server & Sybase driver
com.microsoft.jdbc.sqlserver.SQLServerDriver Microsoft SQL Server 2000 driver
com.microsoft.sqlserver.jdbc.SQLServerDriver Microsoft SQL Server 2005 driver
weblogic.jdbc.sqlserver.SQLServerDriver Weblogic SQL Server driver
com.informix.jdbc.IfxDriver Informix
org.apache.derby.jdbc.ClientDriver Apache Derby client/server driver, aka the Java DB
org.apache.derby.jdbc.EmbeddedDriver Apache Derby embedded driver, aka the Java DB
com.mysql.jdbc.Driver MySQL
org.postgresql.Driver PostgresSQL
org.hsqldb.jdbcDriver HSQLDB pure Java database
org.h2.Driver H2 pure Java database

^ Top of page

Configuring db.properties

Type database information to be used in JDBC.
Example of configuring db.properties

drivers:net.sf.log4jdbc.DriverSpy
cubrid.url=jdbc:log4jdbc:cubrid:localhost:33000:demodb:::
cubrid.user=dba
cubrid.password

^ Top of page

Adding System Properties

For a supported driver, you simply need to input the corresponding database in the location where the above cubrid string is located. If a driver is not supported, you must add the following property to the system properties as shown below.

-Dlog4jdbc.drivers=[,...]

For CUBRID, the following property needs to be added:

-Dlog4jdbc.drivers=cubrid.jdbc.driver.CUBRIDDriver

^ Top of page

Example of Adding Eclipse-based System Properties

If you are developing an Eclipse-based application, configure the system properties as shown below.

^ Top of page

Configuring Logger

logger name Description
jdbc.sqlonly Logs SQL statements only.
If it is PreparedStatement, there is a SQL statement that is configured as a related argument value.
jdbc.sqltimming Includes time information (in ms) used to execute a SQL statement and its corresponding SQL.
jdbc.audit Logs all JDBC call information except for ResultSet.
(Because a large number of logs is created, it is not recommended to use this logger except for tracing a specific JDBC error.)
jdbc.resultset Logs all JDBC call information, including ResultSet; a large number of logs is created.
jdbc.connection Logs open/close transactions for connections. (1.2alpha1 or later)

To check SQL statements, configure jdbc.sqlonly to INFO level.

If you want to know how long SQL statements have been executed, use jdbc.sqltiming.

Because Jdbc.audit, jdbc.resultset, or jdbc.connection creates a large number of logs, it is not recommended to use it except for a special case.

For the log4j configuration file, see the followings.

  • - log4j.xml sample:

http://code.google.com/p/log4jdbc/source/browse/trunk/doc/log4j.xml

  • - log4j.properties sample:

http://code.google.com/p/log4jdbc/source/browse/trunk/doc/log4j.properties

* Locate these files under src or classes to make log4j work.

^ Top of page

Checking Logs

If all tasks are normally processed, you can see the following logs.

Log sample
2009-06-24 17:47:52.598 DEBUG debug: ... log4jdbc initializing ...
2009-06-24 17:47:52.598 DEBUG debug: x log4jdbc.debug.stack.prefix is not defined
2009-06-24 17:47:52.598 DEBUG debug: x log4jdbc.sqltiming.warn.threshold is not defined
2009-06-24 17:47:52.598 DEBUG debug: x log4jdbc.sqltiming.error.threshold is not defined
2009-06-24 17:47:52.599 DEBUG debug: log4jdbc.drivers = cubrid.jdbc.driver.CUBRIDDriver
2009-06-24 17:47:52.599 DEBUG debug: will look for additional driver cubrid.jdbc.driver.CUBRIDDriver
2009-06-24 17:47:52.600 DEBUG debug: FOUND DRIVER cubrid.jdbc.driver.CUBRIDDriver
2009-06-24 17:47:52.606 DEBUG debug: ... log4jdbc initialized! ...
2009-06-24 17:47:52.643 DEBUG sqlonly: Execute.service(null:-1)
1. {WARNING: Statement used to run SQL} select * from athlete where code=10111

 

.Analyzing JDBC Logs with LOG4JDBC

When using a database JDBC driver, you may often want to check the query logs as well as other JDBC logs. This tutorial will show how to analyze CUBRID JDBC logs using popular tool called log4jdbc.

Table of Contents


System Requirements

  • CUBRID 1.1 or higher
  • log4jdbc
  • slf4j

Downloads

log4jdbc Library

log4jdbc is a Java JDBC driver that can log SQL and/or JDBC calls (and optionally SQL timing information) for different JDBC drivers using the SLF4J logging system, which provides APIs for interworking of logging service with log4jdbc.

Download the latest version of log4jdbc compatible with JDK 1.5 or higher (CUBRID JDBC supports JDK 1.5 and higher) from the link below.

SLF4J Library

log4jdbc uses the SLF4J (Simple Logging Facade for Java), a very simple and flexible small library that enables logging at runtime without modifying the application binary. Moreover, users are free to pick any java logging systems such as Log4j, java.util logging in JDK 1.4, logback, Jakarta Commons Logging. In this tutorial we will be using Log4j as our logging system. SLF4J is designed to de-couple your application from the java logging system so you can choose the one you want. This is the same goal of Jakarta Commons Logging. However, many people have had headaches and issues with classloading problems in complex environments using Jakarta Commons Logging. SLF4J solves these problems with it's much simpler design, and you can even integrate SLF4J to use Jakarta Commons Logging, if you really want to (or are required to) use it.

SLF4J is distributed in zip archive which has many jar files in it. You will need only two of them:

1.    slf4j-api-1.5.0.jar or any other latest available version. This library will provide APIs for interworking of logging service with log4jdbc.
2.    slf4j-log4j12-1.5.2.jar or any latest version. This library will provide the implementation libraries for interworking of Log4j-based logging service with log4jdbc.

Place these two jar files into your application's classpath. Read the SLF4J manual, if you need a guide. It's straightforward to set up!


Configure log4jdbc

Set Driver Class

Set the CUBRID Driver Class as shown below.

1.<driver class='cubrid.jdbc.driver.CUBRIDDriver'>

To see the full list of supported Databases by log4jdbc, visit http://code.google.com/p/log4jdbc/#3._Set_your_JDBC_driver_class_to_net.sf.log4jdbc._DriverSpy_in_y.

Configure db.properties

It is necessary to add the database information for CUBRID JDBC. You can use the following sample configurations for db.properties.

1. drivers:net.sf.log4jdbc.DriverSpy
2. cubrid.url=JDBC:log4jdbc:cubrid:localhost:33000:demodb:::
3. cubrid.user=dba
4. cubrid.password

Add System Properties

Add the following property to the system properties. You can refer to the below image which illustrates how to set the system property in Eclipse-based system.

1.-Dlog4jdbc.drivers=cubrid.JDBC.driver.CUBRIDDriver

Configure Logger

Logger name Description
JDBC.sqlonly

Logs SQL statements only.

If it is PreparedStatement, there is a SQL statement that is configured as a related argument value.

JDBC.sqltimming Includes time information (in ms) used to execute a SQL statement and its corresponding SQL.
JDBC.audit

Logs all JDBC call information except for ResultSet.

(Because a large number of logs is created, it is not recommended to use this logger except for tracing a specific JDBC error.)

JDBC.resultset Logs all JDBC call information, including ResultSet; a large number of logs is created.
JDBC.connection Logs open/close transactions for connections. (log4jdbc version 1.2alpha1 or later.)

Check Logs

If all tasks are normally processed, you should be able to see your logs. The following is an example of such logs.

01.2009-06-24 17:47:52.598 DEBUG debug: ... log4jdbc initializing ...
02.2009-06-24 17:47:52.598 DEBUG debug: x log4jdbc.debug.stack.prefix is not defined
03.2009-06-24 17:47:52.598 DEBUG debug: x log4jdbc.sqltiming.warn.threshold is not defined
04.2009-06-24 17:47:52.598 DEBUG debug: x log4jdbc.sqltiming.error.threshold is not defined
05.2009-06-24 17:47:52.599 DEBUG debug:   log4jdbc.drivers = cubrid.JDBC.driver.CUBRIDDriver
06.2009-06-24 17:47:52.599 DEBUG debug:     will look for additional driver cubrid.JDBC.driver.CUBRIDDriver
07.2009-06-24 17:47:52.600 DEBUG debug:   FOUND DRIVER cubrid.JDBC.driver.CUBRIDDriver
08.2009-06-24 17:47:52.606 DEBUG debug: ... log4jdbc initialized! ...
09.2009-06-24 17:47:52.643 DEBUG sqlonly:  Execute.service(null:-1)
10.1. {WARNING: Statement used to run SQL} select * from athlete where code=10111

Tutorials

More detailed information on log4jdbc can be found on their home page http://code.google.com/p/log4jdbc/.


Getting Help

If you have any difficulties with log4jdbc, post your question to CUBRID Forum at https://www.reddit.com/r/CUBRID/


Join the CUBRID Project on