Snapshot monitor | DB2 tuning in WebSphere Commerce


16.2.3 DB2 Event monitor


+

Search Tips   |   Advanced Search

Start your SQL event monitoring...

  1. Open a new DB2 command-line processor session and execute the following DB2 UDB commands:

    db2 connect to RAskyway user username using password
    db2 update monitor switches using statement on
    db2 create event monitor SampleMon for statements write to file `/tmp/sample' maxfiles 10 maxfilesize 10000
    db2 set event monitor SampleMon state=1

    Keep this session open until the database activities are complete. Make sure that the /temp/sample directory is sufficiently large to hold the trace files. The /tmp/sample directory is chosen, as all the users have access to this directory. However, this directory could be replaced with any other directory.

  2. Perform the normal database activities.

    You will set the criteria for those regular database activities as well as potential issues that you seek to track during a specific period to be monitored. During monitoring, you will see a group of files in the /tmp/sample directory with an .evt extension. The size of the files is determined by the parameters and duration of time set by you. During monitoring, you should be able to see a group of files in the /tmp/sample directory with an .evt extension, and the sizes of these files are up to what information you set to be monitored and how long it lasts.

  3. Go to the session that you opened in step 1 and issue the statement:

    db2 set event monitor SampleMon state=0
    db2 terminate

  4. Execute the following command from a normal command-line session:

    $ db2evmon -path /temp/sample > small.statement

    All the captured SQL statements and their details will be captured in the single file small.statement.

Since abundant SQL statements are generated into the file mall.statement, it is important for you to identify what exactly you want.

Example 16-2 Sample output of SQL statement monitoring

409) Statement Event ...
Appl Handle: 1373
Appl Id: P7957356.D9C4.044423183000
Appl Seq number: 0057
Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Close
Section : 37
Creator : NULLID
Package : SYSSH200
Consistency Token : SYSLVL01
Package Version ID :
Cursor : SQL_CURSH200C37
Cursor was blocking: TRUE
Text : SELECT T1.XATTRIBUTE_ID, T1.LANGUAGE_ID, T1.ENUM, T1.NAME, T1.DESCRIPTION, T1.DISPLAYLABEL, T1.XGLOSSARY_ID, T1.ISSTORESPECIFIC, T1.LASTUPDATE, T1.CONTENTSOURCE, T1.USAGE, T1.FIELD1, T1.FIELD2, T1.OPTCOUNTER FROM XATTRIBUTE T1 WHERE T1.DESCRIPTION=?
-------------------------------------------
Start Time: 07/08/2007 12:50:04.372305
Stop Time: 07/08/2007 12:50:04.375307
Exec Time: 0.003002 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 1
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 230
Rows written: 0
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 1123
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: 100
sqlstate: 02000

Generally, four types of SQL statements need to be identified carefully: