An Ingres Blog

The Ingres JDBC driver, iijdbc.jar, has the capability to log the queries that pass through it. This is extremely useful for obtaining query timings as well as working out what goes between an application and the DBMS server. For example, below is a snippet of a trace obtained from Jira:

2008-07-08 10:04:12.319: Ingres-ResultSet[40296].next()
2008-07-08 10:04:12.319: Curs[40296].load()
2008-07-08 10:04:12.319: Curs[40296]: row cache ref=3, off=1, size=1, log=1, stat=0, row=2
2008-07-08 10:04:12.319: Curs[40296].load: AFTER
2008-07-08 10:04:12.319: Ingres-ResultSet[40296].next: false
2008-07-08 10:04:12.319: Ingres-Connection[2].commit()
2008-07-08 10:04:12.320: Conn[2]: end of transaction
2008-07-08 10:04:12.320: Ingres-ResultSet[40296].close()
2008-07-08 10:04:12.320: Curs[40296]: result-set closed
2008-07-08 10:04:12.320: Ingres-PreparedStatement[40293].close()
2008-07-08 10:04:12.320: Ingres-Connection[2].isClosed(): false
2008-07-08 10:04:12.320: Ingres-Connection[2].getAutoCommit(): false
2008-07-08 10:04:12.320: Ingres-Connection[2].isReadOnly(): false
2008-07-08 10:04:12.320: Ingres-Connection[2].rollback()
2008-07-08 10:04:12.320: Ingres-Connection[2].clearWarnings()
2008-07-08 10:04:12.320: Ingres-Connection[2].setAutoCommit( true )
2008-07-08 10:04:12.321: Ingres-Connection[2].setAutoCommit( true )
2008-07-08 10:04:12.321: Ingres-Connection[2].setAutoCommit( false )
2008-07-08 10:04:12.321: Ingres-Connection[2].prepareStatement(’SELECT ID, username, PASSWORD_HASH FROM….

As you can see the trace gives the timings down to the millisecond giving you a reasonable idea as to the relative (noting that logging will slow down things) speed of the queries passing through.

To enable this tracing create a file called iijdbc.properties with the following contents:

ingres.jdbc.trace.log=/tmp/iijdbc.log
ingres.jdbc.trace.timestamp=true
ingres.jdbc.trace.drv=5

Then place iijdbc.properties in to a directory that is part of the CLASSPATH. If you are running a standalone Java application, include the directory of iijdbc.properties in your CLASSPATH. Fire up the application and you should get JDBC log entries in the file specified in ingres.jdbc.trace.log.

When running under an application server such as Tomcat, you need to place this file in one of two places:

• $CATALINA_HOME/common/classes - for system wide logging
• $APP_HOME/WEB-INF/classes - Where $APP_HOME is the location of your application

Note that the latter will enable tracing for just one application rather than the whole instance of tomcat. Useful if you are trying to diagnose/trace a single application in an app server that hosts more than one application.

For more information on the contents of iijdbc.properties see http://docs.ingres.com/connectivity/JDBCDriverInterface and http://docs.ingres.com/connectivity/JDBCTracing.