Lance Andersen's Blog

Using DataDirect Spy to trace Glassfish Database operations

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()
spy>> Connection[9].getMetaData()
spy>> OK (DatabaseMetaData[9])
spy>> DatabaseMetaData[9].getURL()
spy>> 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)
spy>>DatabaseMetaData[9].getDriverName()
spy>> OK (Sybase)
spy>> DatabaseMetaData[9].getDriverVersion()
spy>> OK (3.60.23 (023730.010811.009520))
spy>> DatabaseMetaData[9].getDatabaseProductName()
spy>> OK (Adaptive Server Enterprise)
spy>> DatabaseMetaData[9].getDatabaseProductVersion()
spy>> 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)
spy>>Connection Options :
spy>> CONNECTIONRETRYCOUNT=5
spy>> ALTERNATESERVERS=
spy>> DATABASENAME=lance
spy>> INITIALIZATIONSTRING=
spy>> PREPAREMETHOD=storedProcIfParam
spy>> BATCHPERFORMANCEWORKAROUND=false
spy>> AUTHENTICATIONMETHOD=UserIdPassword
spy>> CONVERTNULL=1
spy>> USEALTERNATEPRODUCTINFO=false
spy>> ERRORBEHAVIOR=Exception
spy>> RESULTSETMETADATAOPTIONS=0
spy>> TRANSACTIONMODE=explicit
spy>> SELECTMETHOD=direct
spy>> JAVADOUBLETOSTRING=false
spy>> LOADLIBRARYPATH=
spy>> SERVICEPRINCIPALNAME=
spy>> CONNECTIONRETRYDELAY=1
spy>> QUERYTIMEOUT=0
spy>> INSENSITIVERESULTSETBUFFERSIZE=2048
spy>> MAXPOOLEDSTATEMENTS=0
spy>> WORKAROUNDS=0
spy>> CODEPAGEOVERRIDE=
spy>> LOADBALANCING=false
spy>> Driver Name = Sybase
spy>> Driver Version = 3.60.23 (023730.010811.009520)
spy>> Database Name = Adaptive Server Enterprise
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
spy>> OK (Connection[9])
spy>> Connection[2].setAutoCommit(boolean autoCommit)
spy>> autoCommit = false
spy>> OK
spy>> Connection[2].createStatement()
spy>> OK (Statement[1])
spy>> Statement[1].executeUpdate(String sql)
spy>> sql = delete from ctstable2
spy>> OK (0)
spy>> Statement[1].close()
spy>> OK
spy>> Connection[2].createStatement()
spy>> OK (Statement[2])
spy>> Connection[2].prepareStatement(String sql) V spy>> sql = insert into ctstable1 values(?, ?)
spy>> OK (PreparedStatement[1])
spy>> PreparedStatement[1].setInt(int parameterIndex, int x)
spy>> parameterIndex = 1
spy>> x = 1
spy>> OK
spy>> PreparedStatement[1].setString(int parameterIndex, String x)
spy>> parameterIndex = 2
spy>> x = Type-1
spy>> OK
spy>> PreparedStatement[1].executeUpdate()
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.

Comments

• With JXInsight Transact (JDBInsight) you can get so much more information and it works with every JDBC driver or datasource.

  JDBC: Database Transaction Concurrency Analysis
  http://www.jinspired.com/products/jxinsight/concurrency.html

  JDBC: Database Lock Contention
  http://www.jinspired.com/products/jxinsight/olapvsoltp.html

  JXInsight Transact Factsheet
  http://www.jinspired.com/products/jxinsight/transact.pdf

  It is probably a bit too advanced for most developers but it is designed for complex problems not solved by other tools.

  William

  Posted by: wlouth on July 18, 2008 at 01:41 AM

• I'm evaluating P6Spy for my performance testing (putting assertions on P6Spy output), but I'm a bit worried that the last release of P6Spy dates back to five years ago... Is JXInsight opensource?

  Posted by: fabriziogiudici on July 19, 2008 at 03:40 AM

• JXInsight is not open sourced and the free development edition is not supported anymore. Because at the end of the day it is not just the tool that solves the problem it is the people and process.

  If a developer has not got the time to understand the process and determine what he is trying to achieve and how best to achieve then what is the point of giving them such a powerful tool. You get back what you pay and invest.

  By the way a software performance engineer picks tools that do not have their own performance problems.
  

  Posted by: wlouth on July 19, 2008 at 09:23 AM

This work is licensed under a Creative Commons License.