Using DataDirect Spy to trace Glassfish Database operations

In the blog "Using P6Spy and the GlassFish Connection Pool to Trace Database Operations", Jagadesh shows you how to use P6Spy.

If you are using the Datadirect or the Sun OEM version of the Datadirect JDBC drivers, it is extremely easy to start tracing and logging the JDBC driver's database operations using DataDirect Spy.

The steps are quite simple:

• include the spy.jar or smspy.jar (Sun OEM Datadirect drivers) in $AS_HOME/lib or $AS_HOME/domains/domain1/lib/ext, the location where you installed the JDBC drivers.
• add the property spyAttributes to your DataSource configuration
  indicating the path to log the trace output to. The value specified takes the form of: log=(file)<path_to_filename>

Here is an example setting the DataDirect JDBC driver properties for Sybase:

When you run your application a file, in our case: /tmp/ase15.trace
will be generated with the trace output for any JDBC driver access to the database.

Here is a sample of the output:

spy>> DataSource.getConnection()
<br>
spy&gt;&gt; Connection[9].getMetaData()
<br>
spy&gt;&gt; OK (DatabaseMetaData[9])
<br>
spy&gt;&gt; DatabaseMetaData[9].getURL()
<br>
spy&gt;&gt; OK (jdbc:sun:sybase://caseylou2.east:5000;CONNECTIONRETRYCOUNT=5;ALTERNATESERVERS=;DATABASENAME=lance;INITIALIZATIONSTRING=;PREPAREMETHOD=storedProcIfParam;BATCHPERFORMANCEWORKAROUND=false;AUTHENTICATIONMETHOD=UserIdPassword;CONVERTNULL=1;USEALTERNATEPRODUCTINFO=false;ERRORBEHAVIOR=Exception;RESULTSETMETADATAOPTIONS=0;TRANSACTIONMODE=explicit;SELECTMETHOD=direct;JAVADOUBLETOSTRING=false;LOADLIBRARYPATH=;SERVICEPRINCIPALNAME=;CONNECTIONRETRYDELAY=1;QUERYTIMEOUT=0;INSENSITIVERESULTSETBUFFERSIZE=2048;MAXPOOLEDSTATEMENTS=0;WORKAROUNDS=0;CODEPAGEOVERRIDE=;LOADBALANCING=false)
<br>
spy&gt;&gt;DatabaseMetaData[9].getDriverName()
<br>
spy&gt;&gt; OK (Sybase)
<br>
spy&gt;&gt; DatabaseMetaData[9].getDriverVersion()
<br>
spy&gt;&gt; OK (3.60.23 (023730.010811.009520))
<br>
spy&gt;&gt; DatabaseMetaData[9].getDatabaseProductName()
<br>
spy&gt;&gt; OK (Adaptive Server Enterprise)
<br>
spy&gt;&gt; DatabaseMetaData[9].getDatabaseProductVersion()
<br>
spy&gt;&gt; OK (Adaptive Server Enterprise/15.0.2/EBF 14332/P/NT (IX86)/Windows 2000/ase1502/2486/32-bit/OPT/Thu May 24 04:10:36 2007)
<br>

spy&gt;&gt;Connection Options : 
<br>
spy>> 		CONNECTIONRETRYCOUNT=5
<br>
spy>> 		ALTERNATESERVERS=
<br>
spy>> 		DATABASENAME=lance
<br>
spy>> 		INITIALIZATIONSTRING=
<br>
spy>> 		PREPAREMETHOD=storedProcIfParam
<br>
spy>> 		BATCHPERFORMANCEWORKAROUND=false
<br>
spy>> 		AUTHENTICATIONMETHOD=UserIdPassword
<br>
spy>> 		CONVERTNULL=1
<br>
spy>> 		USEALTERNATEPRODUCTINFO=false
<br>
spy>> 		ERRORBEHAVIOR=Exception
<br>
spy>> 		RESULTSETMETADATAOPTIONS=0
<br>
spy>> 		TRANSACTIONMODE=explicit
<br>
spy>> 		SELECTMETHOD=direct
<br>
spy>> 		JAVADOUBLETOSTRING=false
<br>
spy>> 		LOADLIBRARYPATH=
<br>
spy>> 		SERVICEPRINCIPALNAME=
<br>
spy>> 		CONNECTIONRETRYDELAY=1
<br>
spy>> 		QUERYTIMEOUT=0
<br>
spy>> 		INSENSITIVERESULTSETBUFFERSIZE=2048
<br>
spy>> 		MAXPOOLEDSTATEMENTS=0
<br>
spy>> 		WORKAROUNDS=0
<br>
spy>> 		CODEPAGEOVERRIDE=
<br>
spy>> 		LOADBALANCING=false
<br>
spy>> Driver Name = Sybase
<br>
spy>> Driver Version = 3.60.23 (023730.010811.009520)
<br>
spy>> Database Name = Adaptive Server Enterprise
<br>
spy>> Database Version = Adaptive Server Enterprise/15.0.2/EBF 14332/P/NT (IX86)/Windows 2000/ase1502/2486/32-bit/OPT/Thu May 24 04:10:36 2007
<br>
spy>> OK (Connection[9])

<br>
spy>> Connection[2].setAutoCommit(boolean autoCommit)
<br>
spy>> autoCommit = false
<br>
spy>> OK

<br>
spy>> Connection[2].createStatement()
<br>
spy>> OK (Statement[1])

<br>
spy>> Statement[1].executeUpdate(String sql)
<br>
spy>> sql = delete from ctstable2
<br>
spy>> OK (0)

<br>
spy>> Statement[1].close()
<br>
spy>> OK

<br>
spy>> Connection[2].createStatement()
<br>
spy>> OK (Statement[2])

<br>
spy>> Connection[2].prepareStatement(String sql)
V
spy>> sql = insert into ctstable1 values(?, ?)
<br>
spy>> OK (PreparedStatement[1])

<br>
spy>> PreparedStatement[1].setInt(int parameterIndex, int x)
<br>
spy>> parameterIndex = 1
<br>
spy>> x = 1
<br>
spy>> OK

<br>
spy>> PreparedStatement[1].setString(int parameterIndex, String x)
<br>
spy>> parameterIndex = 2
<br>
spy>> x = Type-1
<br>
spy>> OK
<br>
spy>> PreparedStatement[1].executeUpdate()
<br>
spy>> OK (1)

Of course this is just one additional arrow in your quill for analyzing the problem. You might need to look at the wire level protocol for a given backend. For example, you can use the Ribo utility, to dump out the TDS packets sent to Sybase ASE. You might also need to analyze the performance of the queries being executed and then adjust your indexes or various other database tuning parameters.