It is possible to monitor the execution of sql statements of a JDBC application using two open source tools: P6Spy (see http://www.p6spy.com/) and IronEye SQL (see http://www.irongrid.com/). P6Spy is, according to its web site, an open source framework to support applications that intercept and optionally modify database statements. It does so by acting as a JDBC proxy driver. IronEye SQL is a lightweight Java tool that provides statistics and various performance graphs of all SQL that flows between an application and a database. It in this instance, it acts as a Java Swing GUI frontend to P6Spy.
Jahia software, http://www.jahia.org/, provides a SQL Profiler, licensed under the Jahia Open Software License. The profiler is an alternative to the IronEye SQL tool.
Using IronEye SQL and P6Spy to monitor a DbForms application is quite easy. The P6Spy JDBC driver is inserted between DbForms and your actual JDBC driver class. Then, DbForms operations can be monitored by connection to P6Spy from IronEye SQL. The following checklist shows the steps needed.
Download and install P6Spy and IronEye SQL.
Copy the three JAR files
ironeyesql.jar,
p6spy.jar, and
log4j-1.2.8.jar.jar into the
WEB-INF/lib directory of your
web application.
Create a spy.properties file into
WEB-INF/classes directory of
your web application or include it into the JAR archive of your
application. An extract of a sample file is shown below.
# ---- module.log=com.p6spy.engine.logging.P6LogFactory #module.outage=com.p6spy.engine.outage.P6OutageFactory module.monitor=com.irongrid.monitor.server.MonitorFactory monitorport=2000 # oracle... realdriver=oracle.jdbc.driver.OracleDriver # are you sure you want to set me to true ? Have you read the docs ? deregisterdrivers=true # ----
Configure database access in
dbforms-config.xml
using the dbconnection element below
as a basic model. The key portion is the
conClass attribute.
<dbconnection isJndi = "false" isPow2 = "true" defaultConnection = "true" connectionProviderClass = "org.dbforms.conprovider.SingleConnectionProvider" conClass = "com.p6spy.engine.spy.P6SpyDriver" name = "jdbc:oracle:thin:@devel:1521:myDbService" username = "user" password = "passwd"/>
Start your JDBC web application.
Execute the IronEye SQL application and press the "connect" button to connect to P6Spy. As the web application produces queries, they will be visible through IronEye SQL.