IBM Support

How to display SQL statements executed by JEE applications using minimal tracing

Question & Answer


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 SQL statements being executed by a JEE application running on WebSphere Application Server. The combination of JDBC Driver trace and WebSphere J2C Component Mustgather trace provides many details about the application's use of DB2. However, a lot of this trace data does not relate in any way to the actual SQL statements being executed. The resulting trace volume could easily overrun the trace files, especially on a busy server.

Answer

There is a much less verbose trace you can use if your application performs its SQL statements using java.sql.PreparedStatement objects. In this case it is sufficient to trace just the java.sql.PreparedStatement class.

The trace specification is

      com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement=all
You can request the trace to
  • become active once the server is stopped and started again. To do this using the adminconsole, see the product documentation article titled "Enabling trace at server startup". To do this using scripting, see the product documentation article titled "Configuring traces using scripting".
  • become active immediately, without first requiring the server to be stopped. To do this using the adminconsole, see the product documentation article titled "Enabling trace on a running server". To do this using scripting, see the product documentation article titled "Turning traces on and off in servers processes using scripting". To do this using the MVS Operator command MODIFY see the product documentation article titled "Modify command".

Example

Consider the following SQL INSERT statement in an application which uses java.sql.PreparedStatement to invoke the INSERT:


.
.
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

Prepared Statement calls to set the input parameters are traced, but the values being given to input parameters are not 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

This approach for tracing only works for applications using PreparedStatement objects for SQL execution. SQL statements executed via methods such as regular java.sql.Statement objects or Stored Procedures will not be traced with this trace specification.

This tracing method is "relatively" lightweight compared to the J2C Mustgather and JDBC driver trace. Even so, each SQL statement invocation produces a non-trivial amount of trace output. The anticipated trace volume must be carefully evaluated before activating the trace string on a busy server.

This technique can be use with the following relational data base providers:


    Apache Derby
    DB2 on z/OS
    Informix
    Microsoft SQL Server
    Oracle
    Sybase

[{"Product":{"code":"SS7K4U","label":"WebSphere Application Server for z\/OS"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"DB Connections\/Connection Pooling","Platform":[{"code":"PF035","label":"z\/OS"}],"Version":"9.0.0.0;8.5.5;8.5;8.0;7.0","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
15 June 2018

UID

swg21496047

Page Feedback