Results 1 to 1 of 1
  1. #1
    Join Date
    Jan 2003
    Posts
    74

    Unanswered: About query statement monitor

    Hello everyone!
    I open a session(named SESSION1) and execute the following commands:
    db2 "connect to sample user db2admin using db2admin"
    db2 "update monitor switches using statement on"
    db2 "create event monitor stmtmon for statements write to file 'c:\zhm'"
    db2 "set event monitor stmtmon state 1"

    I open another session(named SESSION2) and execute the following commands:
    db2 "connect to sample user db2admin using db2admin"
    db2 "select * from employee"

    and then I execute the following commands in SESSION1:
    db2 "set event monitor stmtmon state 0"
    db2 "terminate"

    final I execute the following command in SESSION2:
    db2evmon -path c:\zhm > c:\zhm\sqltrace.txt

    the contents of the sqltrace.txt is:

    ...........
    ...........

    4) Statement Event ...
    Appl Handle: 10
    Appl Id: *LOCAL.DB2.00A9C7122508
    Appl Seq number: 0005

    Record is the result of a flush: FALSE
    -------------------------------------------
    Operation: Static Commit
    Package :
    Consistency Token :
    Package Version ID :
    Cursor :
    Cursor was blocking: FALSE
    -------------------------------------------
    Start Time: 08/17/2006 20:27:15.962562
    Stop Time: 08/17/2006 20:27:15.962611
    Exec Time: 0.000049 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

    .......

    6) Statement Event ...
    Appl Handle: 8
    Appl Id: *LOCAL.DB2.01B007121729
    Appl Seq number: 0001

    Record is the result of a flush: FALSE
    -------------------------------------------
    Type : Unknown
    Operation: Set
    Section : 1
    Creator : NULLID
    Package : SQLDEFLT
    Consistency Token : CONTOKN1
    Package Version ID :
    Cursor :
    Cursor was blocking: FALSE
    -------------------------------------------
    Start Time: 08/17/2006 20:27:34.136217
    Stop Time: 08/17/2006 20:27:34.136874
    Exec Time: 0.000657 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

    7) Statement Event ...
    Appl Handle: 8
    Appl Id: *LOCAL.DB2.01B007121729
    Appl Seq number: 0001

    Record is the result of a flush: FALSE
    -------------------------------------------
    Type : Dynamic
    Operation: Prepare
    Section : 201
    Creator : NULLID
    Package : SQLC2E03
    Consistency Token : AAAAAJHR
    Package Version ID :
    Cursor : SQLCUR201
    Cursor was blocking: FALSE
    Text : select * from employee
    -------------------------------------------
    Start Time: 08/17/2006 20:27:34.137094
    Stop Time: 08/17/2006 20:27:34.244450
    Exec Time: 0.107356 seconds
    Number of Agents created: 1
    User CPU: 0.000000 seconds
    System CPU: 0.015625 seconds
    Fetch Count: 0
    Sorts: 0
    Total sort time: 0
    Sort overflows: 0
    Rows read: 1
    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

    8) Statement Event ...
    Appl Handle: 8
    Appl Id: *LOCAL.DB2.01B007121729
    Appl Seq number: 0001

    Record is the result of a flush: FALSE
    -------------------------------------------
    Type : Dynamic
    Operation: Open
    Section : 201
    Creator : NULLID
    Package : SQLC2E03
    Consistency Token : AAAAAJHR
    Package Version ID :
    Cursor : SQLCUR201
    Cursor was blocking: TRUE
    Text : select * from employee
    -------------------------------------------
    Start Time: 08/17/2006 20:27:34.244809
    Stop Time: 08/17/2006 20:27:34.244867
    Exec Time: 0.000058 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

    9) Statement Event ...
    Appl Handle: 8
    Appl Id: *LOCAL.DB2.01B007121729
    Appl Seq number: 0001

    Record is the result of a flush: FALSE
    -------------------------------------------
    Type : Dynamic
    Operation: Describe
    Section : 201
    Creator : NULLID
    Package : SQLC2E03
    Consistency Token : AAAAAJHR
    Package Version ID :
    Cursor : SQLCUR201
    Cursor was blocking: TRUE
    Text : select * from employee
    -------------------------------------------
    Start Time: 08/17/2006 20:27:34.244809
    Stop Time: 08/17/2006 20:27:34.277439
    Exec Time: 0.032630 seconds
    Number of Agents created: 1
    User CPU: 0.000000 seconds
    System CPU: 0.000000 seconds
    Fetch Count: 33
    Sorts: 0
    Total sort time: 0
    Sort overflows: 0
    Rows read: 33
    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

    10) Statement Event ...
    Appl Handle: 8
    Appl Id: *LOCAL.DB2.01B007121729
    Appl Seq number: 0001

    Record is the result of a flush: FALSE
    -------------------------------------------
    Type : Dynamic
    Operation: Close
    Section : 201
    Creator : NULLID
    Package : SQLC2E03
    Consistency Token : AAAAAJHR
    Package Version ID :
    Cursor : SQLCUR201
    Cursor was blocking: TRUE
    Text : select * from employee
    -------------------------------------------
    Start Time: 08/17/2006 20:27:34.244809
    Stop Time: 08/17/2006 20:27:34.312643
    Exec Time: 0.067834 seconds
    Number of Agents created: 1
    User CPU: 0.000000 seconds
    System CPU: 0.000000 seconds
    Fetch Count: 33
    Sorts: 0
    Total sort time: 0
    Sort overflows: 0
    Rows read: 33
    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

    ......
    *****END*****

    MY question is "Operation: Describe" or "Operation: Close"
    Which imply "select * from employee" is executing? I want to see the execute cost of the select statement!!!

    I test "Insert" "delete" and update SQL statement, they have " Operation: Execute" or "Operation: Execute Immediate" tag
    Last edited by zhouhaiming; 08-17-06 at 10:25.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •