DB2 application snapshot中的Statement SQL语句对应的视图/表函数

如果使用db2 get snapshot for aplication agentid xx,可以看到某个未提交的应用最后执行的SQL语句,进而定位到问题,比如:

$ db2 get snapshot for application agentid 441

            Application Snapshot

Application handle                         = 441
Application status                         = UOW Waiting                 <<--应用状态为UOW Waiting
...

Statement type                             = Dynamic SQL Statement
Statement                                  = Close
Section number                             = 201
Application creator                        = NULLID
Package name                               = SQLC2K26
Consistency Token                          =
Package Version ID                         =
Cursor name                                = SQLCUR201
Statement member number                    = 0
Statement start timestamp                  = 2017-03-09 17:23:15.067789
Statement stop timestamp                   = 2017-03-09 17:23:15.068893 
Elapsed time of last completed stmt(sec.ms)= 0.000024
Total Statement user CPU time              = 0.000000
Total Statement system CPU time            = 0.000000
..
Dynamic SQL statement text:      
select * from t1
 

如果用视图和表函数,可以考虑先从MON_GET_UNIT_OF_WORK表函数中获取LAST_EXECUTABLE_ID,然后根据这个EXECUTABLE_ID去MON_GET_PKG_CACHE_STMT表函数中查找对应的STMT_TEXT,但这样做有一个缺点,就是一旦SQL被从package cache中刷出去,就查不到了。这种情况下可以从上面的快照命令中获取,如果想直接从表函数/视图中获取,可以考虑SNAPSTMT视图 或SNAP_GET_STMT表函数,具体说明见下面的链接:

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0021994.html

上一篇:jenkins自动化部署


下一篇:ORACLE---ORA-19606(RMAN删除obsolete报错)