27.10. How To Monitor SQL Statements

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.

  1. Download and install P6Spy and IronEye SQL.

  2. 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.

  3. 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.

    # ----
    # oracle...
    # are you sure you want to set me to true ? Have you read the docs ?
    # ----

  4. Configure database access in dbforms-config.xml using the dbconnection element below as a basic model. The key portion is the conClass attribute.

     isJndi                  = "false"
     isPow2                  = "true"
     defaultConnection       = "true"
     connectionProviderClass = 
     conClass                = "com.p6spy.engine.spy.P6SpyDriver"
     name                    = "jdbc:oracle:thin:@devel:1521:myDbService"
     username                = "user"
     password                = "passwd"/>

  5. Start your JDBC web application.

  6. 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.