每天一条DB2命令-033
#关于死锁监控器
Message: target type 0 failed to open DB2DETAILDEADLOCK with rc -2146631637

[db2inst1@centos1 ~]$ db2diag -rc -2146631637

Input ZRC string '-2146631637' parsed as 0x800D002B (-2146631637).

ZRC value to map: 0x800D002B (-2146631637)
V7 Equivalent ZRC value: 0xFFFF8E2B (-29141)

ZRC class :
SQL Error, User Error,... (Class Index: 0)
Component:
SQLM ; database monitor (Component Index: 13)
Reason Code:
43 (0x002B)

Identifer:
SQLM_RC_EVFULL
Identifer (without component):
SQLZ_RC_EVFULL

Description:
monitor full of data

Associated information:
Sqlcode -1617
SQL1617N The specified event monitor has already reached its MAXFILES and
MAXFILESIZE limit.

Number of sqlca tokens : 0
Diaglog message number: 8052

一． 清理数据库死锁监控日志操作
涉及主机：主机名（IP） (请修改以下文档的实例名、路径等)
1、 查看设置的事件监视器
db2 "select substr(evmonname,1,20) as evmonname,substr(owner,1,10) as owner,ownertype,substr(target,1,30) as target,maxfiles,maxfilesize,buffersize,IO_MODE,WRITE_MODE,AUTOSTART,DBPARTITIONNUM,MONSCOPE,EVMON_ACTIVATES,NODENUM,substr(DEFINER,1,10) as DEFINER,substr(REMARKS,1,10) as REMARKS from syscat.eventmonitors"
查看状态：
db2 "select evmonname,EVENT_MON_STATE(evmonname) as state from syscat.eventmonitors"

2、 关闭死锁监视器
用db2hbms用户登陆
db2 connect to hbmsdb
db2 “set event monitor DB2DETAILDEADLOCK state=0”
db2 "select evmonname,EVENT_MON_STATE(evmonname) as state from syscat.eventmonitors"

3、 迁移死锁监视器目录，并归档
用db2hbms用户登陆
cd /backup/deadlock_bak
mkdir -p /backup/deadlock_bak/20180603/
mv /db2data/db2hbms/NODE0000/SQL00001/MEMBER0000/db2event/db2detaildeadlock/* /backup/deadlock_bak/20180603/

4、 删除死锁监视器
db2 “drop event monitor DB2DETAILDEADLOCK”

5、 重建死锁监视器
db2 “ CREATE EVENT MONITOR DB2DETAILDEADLOCK FOR DEADLOCKS WRITE TO FILE '/db2data/db2hbms/NODE0000/SQL00001/MEMBER0000/db2event/db2detaildeadlock/' MAXFILES 50 MAXFILESIZE 512 AUTOSTART”

6、 启动死锁监视器
db2 “set event monitor DB2DETAILDEADLOCK state=1”
db2 "select evmonname,EVENT_MON_STATE(evmonname) as state from syscat.eventmonitors"

7、 验证设置的死锁监视器
db2 "select substr(evmonname,1,20) as evmonname,substr(owner,1,10) as owner,ownertype,substr(target,1,30) as target,maxfiles,maxfilesize,buffersize,IO_MODE,WRITE_MODE,AUTOSTART,DBPARTITIONNUM,MONSCOPE,EVMON_ACTIVATES,NODENUM,substr(DEFINER,1,10) as DEFINER,substr(REMARKS,1,10) as REMARKS from syscat.eventmonitors"

8、把监视器记录的数据转成文本文件，分析死锁。并删除原来的目录.
cd /backup/deadlock_bak/20180603/
db2evmon -path /backup/deadlock_bak/20180603/ > 20180603lock.txt
grep "Deadlock detection time:" 20180603lock.txt
rm –rf /backup/deadlock_bak/20180603/

二．回退步骤
建立死锁监视器是为了监控数据库，变更已经把死锁日志导出，本变更没有必要回退。