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.