The P6Spy driver seamlessly (no code changes required to applications) intercepts and logs all database statements of any application that uses JDBC. This application is particularly useful for developers to monitor the SQL statements produced by EJB servers, enabling the developer to write code that achieves maximum efficiency on the server.

The application has been tested on application servers such as JBoss, BEA, and ATG as well as databases such as Oracle, DB2, SQL Server, MySQL, and MS Access.

Download at http://www.provision6.com/index.htm.

I was searching for a logging mechanism for JDBC for a long time. JDBinsight looked good but there is no product yet. I tried P6Spy and it was very easy to install (orion, interbase) and works as advertised. Monitor your container ;-)

bye
-stephan

Stephan,

I am very busy at the moment getting JDBInsight ready for downloading pretty soon from www.jinspired.com/products.jdbinsight

At the moment I am working on providing support for the multitude of SQL engines on the market. This is required if you want to offer much more than some simple sql statement parsing or string.indexof of a table name for sql statement.

JDBInsight currently supports many of the sql extensions in Oracle including their sets, unions, decodes and so on. This means that when JDBInsight parses sql it knows what fields are accessed and the type of access even when they are embedded in conditional expressions nested within sub-selects.

William Louth
www.jinspired.com

one correction:

www.jinspired.com/products.jdbinsight

->

www.jinspired.com/products/jdbinsight/

Im waiting, I still need JDBInsight for SQL performance monitoring,

bye
-stephan
--
CTO
neotis wissensmanagement GmbH

We were using p6spy and IronGrid but we could not find the IronGrid code to fix some bugs. Then, we started implementing a client for p6spy and we finished implementing the full solution. This application is Elvyx and can be downloaded from: http://www.elvyx.com I think this tool could be useful to the community. Armando ;-) http://www.elvyx.com

DB2 V6/V7 provides a builtin event monitor which basically does the same thing. It logs sql statements for later viewing. This is very useful for tracking down dead locks etc as you can see the statements causing the dead lock in the trace from the event monitor.

You just create an event monitor for your database and then run your application. You can then view the trace using the DB2 tooling.

I'd imagine that most databases provide this sort of functionality at the database level.

Billy

Billy,

Many of the databases on the market provide some sort of trace facility. The problem is that these tools can be pretty hard to use and are usually not available to the developer; DBA's are usually not so willing to provide this permission.

This leads to another problem which is that there is now a requirement for all J2EE developers to be trained in different DB tracing tools. You cannot take your favorite tracing tool with you form project to project.

I am not familiar with all DB tracing tools but the ones I have used DO NOT provide anything close to what JDBInsight provides. JDBInsight is designed specifically for J2EE/JDBC usuage and takes into many other things which database trace tools canonot, for example network costs.

Having said this I believe that the DB trace tools play a part in performance tuning but this comes after you have identified the bottlenecks or errors and handed them over to your DBA colleague who can then trace it in terms of excecution part with the db engine.

William Louth
www.jinspired.com/products/jdbinsight/tour

Just FYI, BEA Weblogic 6.1 offers profiling of SQL statements including SQL text, execution time, parameter values for prepared statements and stored procedures:

http://e-docs.bea.com/wls/docs61/javadocs/weblogic/management/runtime/JDBCConnectionPoolRuntimeMBean.html#getStatementProfiles(int, int)

http://e-docs.bea.com/wls/docs61/javadocs/weblogic/management/runtime/JDBCStatementProfile.html

Do you have an example or documentation on how to use this API? Thanks in advance,

Cristian

Hello, I'm looking for Cristian Jansenson from La Plata, Argentina.

My e-mail is toni_discala at hotmail dot com

Thank you very much in advance,

toni

Hi,

we're selling J2EE applications to very different customers and each one has a different RDBMS. So a tool which utilizes JDBC is useful for some people out there,

bye
-stephan

I seen this post a few days ago , thought it sounded cool and forgot all about i. A friend asked me today did i ever heard of any tool that monitored the SQL statements produced by EJB servers. I pointed this post to him. He tried it and was very impressed. Simple to setup and works extremely well.
Good work
jim

P6Spy is now open source:

http://p6spy.sourceforge.net/