Zorka

Zorka agent can monitor and trace SQL queries as seen by JDBC driver methods. It instruments execute*() methods of JDBC driver, calculates some statistics that are exposed as MBeans, logs problematic (or long running methods) and submits execution details via tracer. Common SQL queries profiling and monitoring functionality is implemented in sql.bsh script. There is a set of ready to use scripts for various databases:

jdbc/db2.bsh - IBM DB2 database;
jdbc/h2.bsh - H2 database;
jdbc/mssql.bsh - Microsoft SQL Server (sqljdbc4 driver from Microsoft);
jdbc/mysql.bsh - MySQL (standard mysql-connector driver);
jdbc/oracle.bsh - Oracle (ojdbc6 driver);
jdbc/pgsql.bsh - PostgreSQL (standard jdbc4 driver);
jdbc/wasjdbc.bsh - Websphere JDBC wrapper driver; it should work for Websphere Application Server regardless ofunderlying database, granted that applications use Websphere resource adapters and do not use JDBC drivers directly(nor via custom pooling implementations);

SQL monitoring provides the following features:

performance statistics - presented via JMX;
slow SQL calls and SQL errors logging;
trace SQL calls;

SQL support script also has capability of logging slow SQL queries and SQL errors. As it is integrated with traps.bsh script, standard properties like traps.slow, traps.error, traps.file etc. can be used to configure slow/error logs. See traps.bsh documentation for more details.

Configuration properties for SQL monitoring

Settings for SQL statistics:

sql.stats = yes - monitor SQL statistics;
sql.stats.congestion = no - monitor SQL congestion statistics (not implemented yet);
sql.stats.mbean = zorka:type=ZorkaStats,name=SqlStats - mbean for SQL statistics;

SQL tracing:

sql.trace = yes - enable or disable tracing of SQL requests;
sql.trace.time = 1000 - minimum query execution time to be submitted;

Slow SQL query log:

sql.slow = ${traps.slow} - enable or disable slow query log;
sql.slow.time = 5000 - minimum query time;
sql.slow.file.max = 8M - maximum slow query log file size;
sql.slow.file.num = 8 - maximum number of archived log files;
sql.slow.file.path = ${zorka.log.dir}/sql-slow.log - path to slow query log file;
sql.slow.format = [${TIME}] ${DB}: ${SQL} - query log messages format;

SQL error log settings:

sql.error = ${traps.error} - enable or disable SQL error log;
sql.error.file.max = 8M - maximum error log file size;
sql.error.file.num = 8 - maximum number of archived error logs;
sql.error.file.path = ${zorka.log.dir}/sql-error.log - path to log file;
sql.error.format = [${TIME}] ${DB}: ${SQL} -> ${ERR} - query log messages format;

SQL Monitoring

Configuration properties for SQL monitoring