Tuesday, March 31, 2015

Enable DB2 statement monitoring using event trace


C:\Program Files\IBM\SQLLIB\BIN>db2 connect to SAMPLEDB

   Database Connection Information

 Database server        = DB2/NT64 9.7.6
 SQL authorization ID   = ADMINIST...
 Local database alias   = SAMPLEDB

C:\Program Files\IBM\SQLLIB\BIN>db2 get monitor switches

            Monitor Recording Switches
Buffer Pool Activity Information  (BUFFERPOOL) = OFF
Lock Information                        (LOCK) = OFF
Sorting Information                     (SORT) = OFF
SQL Statement Information          (STATEMENT) = OFF
Table Activity Information             (TABLE) = OFF
Take Timestamp Information         (TIMESTAMP) = ON  03/31/2015 15:34:19.915686
Unit of Work Information                 (UOW) = OFF


C:\Program Files\IBM\SQLLIB\BIN>db2 update monitor switches using statement on
DB20000I  The UPDATE MONITOR SWITCHES command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 create event monitor stmon for statements write to file 'C:\temp'
DB20000I  The SQL command completed successfully.


C:\Program Files\IBM\SQLLIB\BIN>db2 set event monitor stmon state=1
DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 create table tbl_donghua(id integer)
DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 insert into tbl_donghua values (1)
DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 select * from tbl_donghua

ID
-----------
          1

  1 record(s) selected.


C:\Program Files\IBM\SQLLIB\BIN>db2 drop table tbl_donghua
DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 set event monitor stmon state 0
DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2evmon -path c:\temp > c:\temp\db2stmt.sql

Reading c:\temp\00000000.EVT ...

C:\Program Files\IBM\SQLLIB\BIN>db2 drop event monitor stmon
DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 update monitor switches using statement off
DB20000I  The UPDATE MONITOR SWITCHES command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 get monitor switches

            Monitor Recording Switches

Switch list for db partition number 0
Buffer Pool Activity Information  (BUFFERPOOL) = OFF
Lock Information                        (LOCK) = OFF
Sorting Information                     (SORT) = OFF
SQL Statement Information          (STATEMENT) = OFF
Table Activity Information             (TABLE) = OFF
Take Timestamp Information         (TIMESTAMP) = ON  03/31/2015 15:34:19.915686
Unit of Work Information                 (UOW) = OFF




5) Statement Event ...
  Appl Handle: 125
  Appl Id: *LOCAL.DB2.150331075101
  Appl Seq number: 00004

  Record is the result of a flush: FALSE
  -------------------------------------------
  Type     : Dynamic
  Operation: Execute Immediate
  Section  : 203
  Creator  : NULLID  
  Package  : SQLC2H23
  Consistency Token  : AAAAABBc
  Package Version ID  : 
  Cursor   : 
  Cursor was blocking: FALSE
  Text     : create table tbl_donghua(id integer)
  -------------------------------------------
  Start Time: 03/31/2015 15:54:26.746074
  Stop Time:  03/31/2015 15:54:26.852143
  Elapsed Execution Time:  0.106069 seconds
  Number of Agents created: 1
  User CPU: 0.015625 seconds
  System CPU: 0.000000 seconds
  Statistic fabrication time (milliseconds): 0
  Synchronous runstats time  (milliseconds): 0
  Fetch Count: 0
  Sorts: 0
  Total sort time: 0
  Sort overflows: 0
  Rows read: 10
  Rows written: 7
  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
  Bufferpool xda logical page reads: 0
  Bufferpool xda physical page reads: 0
  Bufferpool temporary xda logical page reads: 0
  Bufferpool temporary xda physical page reads: 0
  SQLCA:
   sqlcode: 0
   sqlstate: 00000

42) Statement Event ...
  Appl Handle: 125
  Appl Id: *LOCAL.DB2.150331075101
  Appl Seq number: 00005

  Record is the result of a flush: FALSE
  -------------------------------------------
  Type     : Dynamic
  Operation: Execute Immediate
  Section  : 203
  Creator  : NULLID  
  Package  : SQLC2H23
  Consistency Token  : AAAAABBc
  Package Version ID  : 
  Cursor   : 
  Cursor was blocking: FALSE
  Text     : insert into tbl_donghua values (1)
44) Statement Event ...
  Appl Handle: 125
  Appl Id: *LOCAL.DB2.150331075101
  Appl Seq number: 00006

  Record is the result of a flush: FALSE
  -------------------------------------------
  Type     : Dynamic
  Operation: Prepare
  Section  : 201
  Creator  : NULLID  
  Package  : SQLC2H23
  Consistency Token  : AAAAABBc
  Package Version ID  : 
  Cursor   : SQLCUR201
  Cursor was blocking: FALSE
  Text     : select * from tbl_donghua
45) Statement Event ...
  Appl Handle: 125
  Appl Id: *LOCAL.DB2.150331075101
  Appl Seq number: 00006

  Record is the result of a flush: FALSE
  -------------------------------------------
  Type     : Dynamic
  Operation: Open
  Section  : 201
  Creator  : NULLID  
  Package  : SQLC2H23
  Consistency Token  : AAAAABBc
  Package Version ID  : 
  Cursor   : SQLCUR201
  Cursor was blocking: TRUE
  Text     : select * from tbl_donghua