ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DB2查看存储过程里SQL语句的执行情况

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

原创 Linux操作系统 作者:myfriend2010 时间:2019-07-16 13:42:02 0 删除 编辑

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

动态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: mp'
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)


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/694276/viewspace-51430/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2018-09-01

  • 博文量
    187
  • 访问量
    127802