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
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
Great post. Thanks for sharing. Often when I am stuck in these situations I have preferred to use p6spy as well.
ReplyDeleteanand
I am finding that WSJdbcPreparedStatement is not actually showing the SQL. Just things like executeQuery Entry com.ibm.ws.rsadapter.jdbc.WSJccPreparedStatement@4c624c62
ReplyDeleteWSJdbcPrepare < executeQuery Exit com.ibm.ws.rsadapter.jdbc.WSJccResultSet@548c548c
I'll give the EJB tracing a shot now...
That's interesting, which version of WCS/WAS are you using, I have tested this on V7 and Prepared statements does get printed.
ReplyDeleteP6Spy is definitely a good tool, not sure if this tool is still available for download, I thought it was discontinued.
Thanks for providing the hint Hari.
ReplyDeleteBTW, following would work for WCS 7, I have tested this with 7.0.0.4 version.
*=info: com.ibm.ws.rsadapter.jdbc.WSJccPreparedStatement=all