[TechNote] How to display SQL statements executed from J2EE applications using minimal tracing

How to display SQL statements executed from J2EE applications using minimal tracing

Question

Is there a minimal trace string to view SQL statements being executed by an application running on WebSphere Application Server?

Cause

It is often desirable to view or trace SQL statements being executed by A J2EE application running on WebSphere Application Server. Enabling JDBC Driver trace and WebSphere J2C component mustgather trace can help one determine the SQL Query being executed. However, that tracing is very verbose and may be unsuitable for a busy server.

Answer

If the application uses java.sql.PreparedStatement objects, then there is a relatively lightweight way to obtain the SQL statements being executed. PreparedStatement objects are often used in applications because of their performance over regular java.sql.Statement objects.
To view the SQL statements being executed using a PreparedStatement object, enable WebSphere tracing for the class that implements java.sql.PreparedStatement:com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement. The corresponding trace specification is: com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement=all
Examples:
Consider the following sample SQL INSERT statement being executed from the application using a java.sql.PreparedStatement:

String insertCoffeeSaleString
= "INSERT INTO SALES(ITEM_NAME,QUANTITY,PRICE) values('Coffee',5,1.25)";
PreparedStatement insertCoffeeSale
= connection.prepareStatement(insertCoffeeSaleString);
insertCoffeeSale.executeUpdate();

With com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement=all trace enabled, the following output can be seen when the Prepared Statement is initialized:

Trace: 2011/04/07 14:37:12.100 01 t=6C9020 c=UNK key=P8 (13007002)
ThreadId: 0000002b
FunctionName: < init>
SourceId: com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement
Category: FINER
ExtendedMessage: Entry; org.apache.derby.impl.jdbc.EmbedPreparedStatement30@401c401c, com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@3
c4a3c4a, HOLD CURSORS OVER COMMIT (1), PSTMT: INSERT INTO SALES (ITEM_NAME,QUANTITY,PRICE) values('Coffee',5,1.25) 1003 1007 1 0 0
Please note that the contents of input parameters for the Prepared Statement are not traced, however the calls to set the input parameters are traced.
For example, consider the following SQL UPDATE statement being executed from the application, and two parameters are being set for the PreparedStatement:

//Compiling the PreparedStatement:
String dbname = "SALES";
String updatePriceString = "UPDATE " + dbname + " SET PRICE = ? where ITEM_NAME = ?";
PreparedStatement updatePrice = connection.prepareStatement(updatePriceString);
...
//Setting parameters for the PreparedStatement and executing:
//Updating the price of Coffee to 1.35:
updatePrice.setDouble(1,1.35);
updatePrice.setString(2,"Coffee");
updatePrice.executeUpdate();

With com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement=all trace enabled, the following output can be seen when the Prepared Statement is initialized:

Trace: 2011/04/07 14:37:12.122 01 t=6C9020 c=UNK key=P8 (13007002)
ThreadId: 0000002b
FunctionName: < init>
SourceId: com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement
Category: FINER
ExtendedMessage: Entry; org.apache.derby.impl.jdbc.EmbedPreparedStatement30@7a807a80, com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@3
c4a3c4a, HOLD CURSORS OVER COMMIT (1), PSTMT: UPDATE SALES SET PRICE = ? where ITEM_NAME = ? 1003 1007 1 0 0
When the setDouble and setString methods are called, the contents are not traced. However the calls to these methods are traced:
Trace: 2011/04/07 14:37:12.123 01 t=6C9020 c=UNK key=P8 (13007002)
ThreadId: 0000002b
FunctionName: com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement
SourceId: com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement
Category: FINEST
ExtendedMessage: setDouble #1
Trace: 2011/04/07 14:37:12.123 01 t=6C9020 c=UNK key=P8 (13007002)
ThreadId: 0000002b
FunctionName: com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement
SourceId: com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement
Category: FINEST
ExtendedMessage: setString #2
Please note the following limitations of this method to trace SQL statements:
* Queries executed via methods other than PreparedStatements, such as regular java.sql.Statement or Stored Procedures will not be traced.
* While this tracing method is "relatively" lightweight compared to the J2C mustgather and JDBC driver tracing, with each statement being executed, a series of trace entries are printed. The volume of tracing must be carefully evaluated before planning to activate the trace string on a busy server.

Related information

J2C MustGather document for WebSphere Application Serve