JDBC tracing rules
Pavel Sher had an interesting use-case he mentioned in thread: http://community.jboss.org/thread/146132 where he wanted rules to check if a JDBC statement was closed (using the $! notation, which is now implemented in Byteman 1.2.1).
I would like to start a new thread here where we can colloborate and come up with some JDBC rules that can trace JDBC queries.
Here's the use case - I want to know all the SQL queries (either select, insert, update or delete) that were executed AND I want to know how long each query took (in fact, I want to know the number of times a particular SQL was executed, its maximum execution time, minimum execution time and average execution time). I think this is now possible, now that $! is implemented.
Here's my thoughts. Its not finished, I was hoping others had some ideas to help complete this.
First, we need to know when each prepare statement is created, because its during its creation when we know the SQL:
RULE Prepare SQL Statement 1
INTERFACE java.sql.Connection
METHOD prepareStatement(String)
HELPER helper.JdbcTracerHelper
AT EXIT
IF TRUE
DO prepareStatement($!, $1),
traceln("JDBCTrace: prepare1=[" + $1 + "]")
ENDRULE
Note that we'll need several of these because java.sql.Connection.prepareStatement is overloaded - but all have "String sql" as their first argument - and we really only care about the SQL string here (we can ignore the other parameters). Notice I assume there is a helper method called "helper.JdbcTracerHelper.prepareStatement(PreparedStatement stmt, String sql)". This will be used to cache the SQL in some data structure. It needs to remember the SQL string, but it also has to remember the instance of the PreparedStatement for the next rule.
RULE Start Execute Prepared Statement
INTERFACE java.sql.PreparedStatement
METHOD executeQuery
HELPER helper.JdbcTracerHelper
AT ENTRY
IF TRUE
DO startTimerExecutePreparedStatement($0),
traceln("JDBCTrace: startExecutePS=[" + $0 + "]")
ENDRULE
Note again there will need to be a few more of these rules, when executing the "execute" and "executeUpdate" methods in java.sql.PreparedStatement. This rule will start or reset a timer for the given prepared statement (it can utilize the built-in helper methods for Timer functionality)
Of course, now we need an AT EXIT to stop the timer and get the elapsed time:
RULE Stop Execute Prepared Statement
INTERFACE java.sql.PreparedStatement
METHOD executeQuery
HELPER helper.JdbcTracerHelper
AT EXIT
IF TRUE
DO stopTimerExecutePreparedStatement($0),
traceln("JDBCTrace: stopExecutePS=[" + $0 + "]")
ENDRULE
We'll probably want another rule when when an exception occurs (we could use this to track SQL errors too!). I don't have an example of that.
Finally, we'll need to trace java.sql.Statement instances, too (rather than PreparedStatements). This is in the case when someone directly invokes java.sql.Statement.executeQuery(String) and related methods. We'll have similar rules as above, but I don't have examples.
So the things I'm hoping some others can help with are:
1) fill out the rest of the rules to cover all overloaded methods in java.sql.Connection, java.sql.Statement, java.sql.PreparedStatement
2) how do we actually dump reports? Rather than rely on traceln log messages, it would be cool if somehow, someway we could get these rules to dump their data/reports in some file that can be read later.
Hopefully, others out there might find this kind of thing useful and can help fill out these rules.
