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
- Environment System
- Preparing Required Libraries
- Preparing log4jdbc and slf4j Related Libraries
- Selecting the Java Logging System
- Specifying the JDBC Driver Class in Net.sf.log4jdbc.DriverSpy
- List of Default Drivers Supported in log4jdbc
- Configuring db.properties
- Adding System Properties
- Example of Adding Eclipse-based System Properties
- Configuring Logger
- Checking Logs
Enviromnment System
This environment system of this document is as follows:
CUBRID 2008 or higher
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.
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)
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.)
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 |
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
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
Example of Adding Eclipse-based System Properties
If you are developing an Eclipse-based application, configure the system properties as shown below.
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.
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.
- Download the latest version of slf4j from http://www.slf4j.org/download.html.
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.) |
- 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.
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/
