数据库死锁监视器清理变更

数据库死锁监视器清理变更

一． 清理数据库死锁监控日志操作
涉及主机：主机名（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/

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