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
- 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
Related Information
J2C MustGather for WebSphere Application Server
Enabling trace at server startup
Enabling trace on a running server
Collecting Data: Tracing for the DB2 JDBC Type 2 Driver
Collecting Data: Tracing the Driver for JDBC and SQLJ
Configuring traces using scripting
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21496047