Tracing SQL generated by OOB or custom code is very helpful for development and at times during post production support as well.

There are many techniques to perform this activity on Commerce toolkit and LIVE environment, In my previous blog I have provided an option for developer toolkit http://techhari.blogspot.com/2011/01/websphere-commerce-developer-sql.html , in this blog we will explore two more options which can be used both on developer toolkit and server environment.

Option 1
Enable following trace component at WAS level.
*=info: com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement=all

This trace component will print any SQL statement which uses JDBC Preparedstament API to execute SQL.

As you can see from the trace, we are able to print the SQL being executed from the application code , you won't be able to trace SQL's which use java.sql.Statement or Stored Procedures using this technique.

[9/18/11 18:45:16:531 CDT] 0000002d ManagerAdmin I   TRAS0018I: The trace state has changed. The new trace state is *=info:com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement=all.

[9/18/11 18:50:00:625 CDT] 00000044 WSJdbcPrepare > <init> Entry
                                 com.ibm.issw.jdbc.wrappers.WrappedPreparedStatement@1f731f73
                                 com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@1f421f42
                                 HOLD CURSORS OVER COMMIT (1)
                                 PSTMT: INSERT INTO SCHERRORLOG (SCSINSTREFNUM, SCSERROR, OPTCOUNTER) VALUES (?, ?, ?) 1003 1007 1 0 0

[9/18/11 18:51:34:812 CDT] 00000028 WSJdbcPrepare > <init> Entry
                                 com.ibm.issw.jdbc.wrappers.WrappedPreparedStatement@62836283
                                 com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@62526252
                                 HOLD CURSORS OVER COMMIT (1)
                                 PSTMT: SELECT T1.STOREENT_ID, T1.STADDRESS_ID_LOC, T1.DISPLAYNAME, T1.STADDRESS_ID_CONT, T1.LANGUAGE_ID, T1.DESCRIPTION, T1.OPTCOUNTER FROM STOREENTDS T1 WHERE T1.STOREENT_ID = ? AND T1.LANGUAGE_ID = ? 1003 1007 1 0 0

Option2
you can also enable EJB tracing to print application code which makes use of EJB to execute the SQL, this option can be used if you want to trace the EJB components and the SQL's emitted by them

*=info:EJBContainer=all:PMGR=all.

0000002d ManagerAdmin I   TRAS0018I: The trace state has changed. The new trace state is *=info:EJBContainer=all:PMGR=all.

[9/18/11 19:11:47:312 CDT] 0000002d OptCEntityAct 3   CallbackBeanO = ContainerManagedBeanO(BeanId(WC#Enablement-BaseComponentsData.jar#Keys, keysId=-63 ), state = ACTIVE)
[9/18/11 19:11:47:312 CDT] 0000002d ContainerMana > load(tx, forupdate): ContainerManagedBeanO(BeanId(WC#Enablement-BaseComponentsData.jar#Keys, keysId=-63 ), state = ACTIVE) Entry
[9/18/11 19:11:47:312 CDT] 0000002d ContainerMana > load(forupdate): ContainerManagedBeanO(BeanId(WC#Enablement-BaseComponentsData.jar#Keys, keysId=-63 ), state = ACTIVE) Entry
[9/18/11 19:11:47:312 CDT] 0000002d ContainerMana 3   Update Intent(false) Load For Update(false)
[9/18/11 19:11:47:312 CDT] 0000002d EJSJDBCPersis > getPreparedStatement SELECT T1.KEYS_ID, T1.TABLENAME, T1.LOWERBOUND, T1.UPPERBOUND, T1.COUNTER, T1.PREFETCHSIZE, T1.COLUMNNAME, T1.OPTCOUNTER FROM KEYS T1 WHERE T1.KEYS_ID = ? Entry

Option 3

Final option would be to enable tracing at database level, steps would vary based on database type, refer following documentation for tracing SQL in database for Oracle

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#sthref2020

More Reading

How to display SQL statement from J2EE apps.
http://www-01.ibm.com/support/docview.wss?uid=swg21496047

Performance problems with Oracle Database

http://www-01.ibm.com/support/docview.wss?uid=swg21260710

Performance problems with DB2 database

https://www-304.ibm.com/support/docview.wss?uid=swg21224920

Tracing SQL in WebSphere Commerce

http://www.ibm.com/developerworks/websphere/library/techarticles/0802_doumbia/0802_doumbia.html

Tracing SQL in Websphere Commerce