WebSphere Application Server - How to display SQL statements executed from J2EE applications using minimal tracing

Is there a minimal trace string to view SQL statements being executed by an application running on WebSphere Application 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:
   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