利用DB2事件监控语句执行情况

最新推荐文章于 2020-12-11 10:53:14 发布

iteye_3058

最新推荐文章于 2020-12-11 10:53:14 发布

阅读量91

点赞数

文章标签：数据库

本文介绍如何使用DB2的事件监听功能来跟踪SQL语句，特别是STATEMENT监听，它可以帮助开发者通过观察数据库变化来理解业务流程，而无需逐行阅读代码。文章提供了具体的命令示例，并解释了如何设置和解析监听数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DB2提供了一个创建事件监听的功能，用于检查数据库的日常运行情况，这里给出一个对于日常开发我认为最有用的监听——STATEMENT，它的好处就是不必逐行逐行的读代码，直接从应用的操作所产生的数据变化来理解业务。

-- 连接数据库
db2 "connect to 【dbname】"
-- 创建事件监听
db2 "create event monitor stmtmon for statements write to file '/home/【login_username】/'"
-- 启动事件监听
db2 "set event monitor stmtmon state=1"

--
-- 切换至应用程序进行数据库操作
--

-- 停止事件监听
db2 "set event monitor stmtmon state=0"
-- 删除事件监听
db2 "drop event monitor stmtmon"
-- 结束连接
db2 "terminate"
-- 整理监听内容
db2evmon -db 【dbname】 -evm stmtmon </home/【login_username】/sqltrace.out
-- 去除SELECT语句
grep -E 'Text +:' ./sqltrace | grep -v -i 'SELECT' | uniq > dml.sql.out

下面是一小部分输出内容：

  Text     : UPDATE xxx SET NOTREAD=1 WHERE id=730927
  Text     : INSERT INTO yyy(asign_id, taskcause, comments, submitter) VALUES(730927, 1, '', 93)
  Text     : UPDATE xxx SET NOTREAD=1 WHERE id=730927
  Text     : UPDATE xyz SET needappr=2178.38 WHERE idtask = 730927

在启动监听的时候，最常见的就是目录路径与读写权限的问题，以下内容可供参考，根据错误返回码做对应调整即可。

PS:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.messages.sql.doc/doc/msql01614n.html
SQL1614N
An I/O error occurred when activating an event monitor. Reason code = reason-code.

Explanation
An I/O error was detected when an event monitor was activated. >reason-code< is one of the following:
1
Encountered an unknown event monitor target type.
2
The Event monitor target path was not found.
3
Access to event monitor target path was denied.
4
Event monitor target path is not the name of a pipe.
5
No process has opened the event monitor target pipe for reading.
6
Encountered an unexpected I/O error.
User response
Where possible, fix the problem described by the reason code, and resubmit the SET EVENT MONITOR statement.

sqlcode: -1614

sqlstate: 58030

参考文档：Tuning DB2 Universal Database Using the Statement Event Monitor