Using DB2 Event Monitor
DB2 Event Monitor helps with taking a deeper look at the database activity while commerce is in use. It comes very handy in debugging commerce issues - especially when you suspect the problem is related to the database. It also exposes you to some interesting details on what database tables are accessed/used when you load/run commerce pages such as user registration and shopping cart. After all there are over 600 database tables and they all must be used in some form or the other! Be careful in using this in production environments, this is very resource intensive.
I used db2 event monitor on my Windows XP machine running WCS Developer 6.0.0.3 and DB2 v8.3. Essentially there are two steps. One create and enable the event monitor. Two, after loading/running your pages disable the event monitor and format the output to a file. It is important to note that you cannot use this from a db2 client machine. This has to be run on a db2 server machine. If you run db2 event monitor from a client machine you will see the following error for some of the commands: SQL1096N The command is not valid for this node type.
Here are the steps for enabling statement event monitor:
Complete the following steps to enable an event monitor for statements:
- Create a directory to store the event monitor files. I used C:\DB2EventMonitor\Issue1:
- Connect to your local database using appropriate dbName, userName and userPassword
db2 connect to {dbName} user {userName} using {userPassword} - To collect start/end and execution times enable timestamp monitor switch :
db2 update monitor switches using timestamp on
Note: You will see the following output when you enable timestamp monitor switch:
DB20000I The UPDATE MONITOR SWITCHES command completed successfully. - Create the event monitor using the full path to the directory with an appropriate monitorName. As always there are several variations of db2 event monitor. For additional information refer to instructions:
db2 "create event monitor {monitorName} for statements write to file ‘C:\DB2EventMonitor\Issue1" Note: You will see the following output if you the command is executed successfully.
DB20000I The UPDATE MONITOR SWITCHES command completed successfully. - Enable the event monitor:
db2 "set event monitor monitorName STATE=1" Note: You will see the following output if you specify invalid path to the directory in step 4:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL1614N An I/O error occurred when activating an event monitor. Reason code = "2". SQLSTATE=58030
Complete the following steps to disable event monitor and format the output to a file:
- Disable the event monitor using the following command:
db2 "set event monitor {monitorName} STATE=0" - Format the event monitor logs using the following command using the appropriate dbName and monitorName. It will take a few seconds have patience!:
db2evmon -db {dbName} -evm {monitorName} > C:\DB2EventMonitor\Issue1\MonitorOutput.txt
Note: You will see output similar to the following when you format the event monitor logs:
Reading C:\DB2EventMonitor\Issue1\00000000.EVT …
Reading C:\DB2EventMonitor\Issue1\00000001.EVT …
Reading C:\DB2EventMonitor\Issue1\00000002.EVT … - Drop the event monitor:
db2 drop event monitor {monitorName}
Here is a sample of what you may see in the output. All the elements are self-explanatory. I am not sure at this point if we can see the actual values used in the execution of the prepared statements.
1296) Statement Event …
Appl Handle: 485
Appl Id: *LOCAL.DB2.080424094346
Appl Seq number: 0016
Record is the result of a flush: FALSE
——————————————-
Type : Dynamic
Operation: Describe
Section : 11
Creator : NULLID
Package : SYSSN200
Consistency Token : SYSLVL01
Package Version ID :
Cursor : SQL_CURSN200C11
Cursor was blocking: FALSE
Text : UPDATE MEMBER SET STATE = ?, OPTCOUNTER = ? WHERE MEMBER_ID = ? AND OPTCOUNTER = ?
——————————————-
Start Time: 04/24/2008 05:49:56.328948
Stop Time: 04/24/2008 05:49:56.328975
Exec Time: 0.000027 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 0
Rows written: 0
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 0
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 0
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 0
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0
SQLCA:
sqlcode: 0
sqlstate: 00000
If you find additional or other creative ways of using db2 event monitor please let us know!. Thanks.