建站
咨詢
售后
建站咨詢
新聞中心
本文為您介紹如何在DB2數(shù)據(jù)庫中，查看存儲過程里SQL語句的執(zhí)行情況。其中可以用snapshot查看動態(tài)SQL，用event monitor查看存儲過程里SQL語句的執(zhí)行情況。如果您對此有興趣，不妨一看，相信對您會有所幫助。
專注于為中小企業(yè)提供成都網(wǎng)站制作、做網(wǎng)站服務(wù),電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業(yè)文水免費做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動了超過千家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實現(xiàn)規(guī)模擴充和轉(zhuǎn)變。
動態(tài)SQL可以用snapshot查看，存儲過程里SQL語句的執(zhí)行情況用event monitor查看。
但是event monitor沒有記錄SQL語句，而是記錄了package id和Section id。
根據(jù)package id和Section id再查詢系統(tǒng)表就可以得到原始的SQL語句。
例子如下：
(1)創(chuàng)建存儲過程
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#p#
@
(2)創(chuàng)建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 : #p#
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#p#
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)查詢系統(tǒng)表，就可以看到原始的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)
文章標(biāo)題：DB2中查看存儲過程里SQL語句的執(zhí)行情況
地址分享：http://www.dlmjj.cn/article/djiiogo.html
