DB2查看存储过程里SQL语句的执行情况

南来一味凉 · 发表于 2007-8-9 15:03

动态SQL可以用snapshot查看，存储过程里SQL语句的执行情况用event monitor查看。
但是event monitor没有记录SQL语句，而是记录了package id和Section  id。
根据package id和Section  id再查询系统表就可以得到原始的SQL语句。

例子如下：
(1)创建存储过程
create procedure sales_status
(in quota integer)
dynamic result sets 2
language sql
begin
  declare SQLSTATE char(5);

  declare rs cursor with return for
select sales_person, sum(sales) as total_sales
   from sales
   group by sales_person
   having sum(sales) > quota;

  open rs;


  insert into tt1 values (1),(2),(3),(4),(5);

end
@

(2)创建event monitor，并捕获statement信息
db2 create event monitor ev2 for statements write to file 'D:\tmp'
db2 set event monitor ev2 state 1
db2 "call sales_status(10)"
db2 FLUSH EVENT MONITOR ev2
db2evmon -db sample -evm ev2 > 1.out

(3)查看输出文件
在call sales_status(10)语句后面，可以找到
8) Statement Event ...
  Appl Handle: 7
  Appl Id: *LOCAL.DB2.070809034142
  Appl Seq number: 00053

  Record is the result of a flush: FALSE
  -------------------------------------------
  Type    : Static
  Operation: Execute
  Section  : 2
  Creator  : DB2ADMIN
  Package  : P2323139
  Consistency Token  : oAfgMJIX
  Package Version ID  :
  Cursor :
  Cursor was blocking: FALSE
  -------------------------------------------
  Start Time: 2007-08-09 12:33:22.394140
  Stop Time:  2007-08-09 12:33:22.394599
  Exec Time:  0.000459 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: 1
  Rows written: 5
  ...

  10) Statement Event ...
  Appl Handle: 7
  Appl Id: *LOCAL.DB2.070809034142
  Appl Seq number: 00053

  Record is the result of a flush: FALSE
  -------------------------------------------
  Type    : Static
  Operation: Close
  Section  : 1
  Creator  : DB2ADMIN
  Package  : P2323139
  Consistency Token  : oAfgMJIX
  Package Version ID  :
  Cursor : RS
  Cursor was blocking: TRUE
  -------------------------------------------
  Start Time: 2007-08-09 12:33:22.390159
  Stop Time:  2007-08-09 12:33:22.398984
  Exec Time:  0.008825 seconds
  Number of Agents created: 1
  User CPU: 0.000000 seconds
  System CPU: 0.000000 seconds
  Fetch Count: 3
  Sorts: 1
  Total sort time: 0
  Sort overflows: 0
  Rows read: 45
  Rows written: 0
  ...

  看到Package=P2323139, Section=1的SQL有  Rows read: 45
  看到Package=P2323139, Section=2的SQL有  Rows read: 1,  Rows written: 5

(4)查询系统表，就可以看到原始的SQL语句了
select s.STMTNO, s.SECTNO, s.TEXT
from SYSCAT.STATEMENTS s
where s.PKGNAME='P2323139' ;

STMTNO    SECTNO TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        ----------- ------ -------
      8    1 DECLARE RS cursor with return for select SALES_PERSON, SUM(SALES) as TOTAL_SALES    from SALES    group by SALES_PERSON    having SUM(SALES) > :HV00008  :HI00008
      17    2 insert into TT1 values (1),(2),(3),(4),(5)

  2 条记录已选择。