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

wslhs

　　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