Tracing SQL in Websphere Commerce
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
Blogger | Freelancer |Operations Manager | Marketing and Sales Manager | Lancer Evo 4 Community.
Indonesian Bloggers Who Become Bloggers Because of Hobby.
Feel free to connect with me on Social Media Instagram @alberandesko
0 Response to "Tracing SQL in Websphere Commerce"
Post a Comment