Tracing SQL in Websphere Commerce

Posted by Hariharan Vadivelu on
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
 

6 comments:

  1. Great post. Thanks for sharing. Often when I am stuck in these situations I have preferred to use p6spy as well.

    anand

    ReplyDelete
  2. I am finding that WSJdbcPreparedStatement is not actually showing the SQL. Just things like executeQuery Entry com.ibm.ws.rsadapter.jdbc.WSJccPreparedStatement@4c624c62
    WSJdbcPrepare < executeQuery Exit com.ibm.ws.rsadapter.jdbc.WSJccResultSet@548c548c

    I'll give the EJB tracing a shot now...

    ReplyDelete
  3. That's interesting, which version of WCS/WAS are you using, I have tested this on V7 and Prepared statements does get printed.

    P6Spy is definitely a good tool, not sure if this tool is still available for download, I thought it was discontinued.

    ReplyDelete
  4. Thanks for providing the hint Hari.
    BTW, following would work for WCS 7, I have tested this with 7.0.0.4 version.
    *=info: com.ibm.ws.rsadapter.jdbc.WSJccPreparedStatement=all

    ReplyDelete
  5. Thanks for sharing this Informative content. Well explained. Got to learn new things from your Blog on SAP SD.SAP SD

    ReplyDelete