DB2锁定超时、死锁检测步骤
0. 查看锁定参数
db2 get db cfg for crmdb | grep -i LOCK
在当前home下创建目录 dlock
1. 测试前
db2set DB2_CAPTURE_LOCKTIMEOUT=ON
db2 update dbm cfg using DFT_MON_LOCK on DFT_MON_STMT on
db2 "connect to crmdb"
db2 "CREATE EVENT MONITOR dlock FOR DEADLOCKS WITH DETAILS HISTORY
WRITE TO FILE '/home/db2inst1/dlock'"
db2 "SET EVENT MONITOR dlock STATE 1"
需要重起DB2
2. 测试中
出现交易失败时，查看快照：
db2 get snapshot for locks on crmdb
3. 测试后
db2 connect reset
查看 实例目录下的 文件（db2diag.log所在的目录）
根据需要，可执行 db2 "SET EVENT MONITOR dlock STATE 0" 使dlock监控失效，否则将影响一些性能。
--1)打开数据库监控开关
db2 update dbm cfg using DFT_MON_LOCK on DFT_MON_STMT on
db2  update monitor switches using lock ON sort ON bufferpool ON uow ON table ON statement ON
--2)调用执行监控SQL脚本，定位执行较慢 导致锁等待的SQL脚本
select  AGENT_ID ,
substr(STMT_TEXT,1,100) as statement,
STMT_ELAPSED_TIME_MS 
from table(SNAPSHOT_STATEMENT('SAMPLE',-1)) as B
where AGENT_ID in (
tabletimeselect AGENT_ID_HOLDING_LK
from table(SNAPSHOT_LOCKWAIT('SAMPLE',-1)) as A  order by  LOCK_WAIT_START_TIME ASC FETCH FIRST 20 ROWS ONLY )
order by STMT_ELAPSED_TIME_MS DESC
--3)监控脚本
#!/usr/bin/ksh
#
dbname=$1
#create a log file
filename=find.locksql.$(date+'%m%d%H%M%S')
touch $filename
#connect to database
echo now,connecting to database: $dbname
db2 "connect to $dbname"
db2 "update dbm cfg using DFT_MON_LOCK on DFT_MON_STMT on"
db2  "update monitor switches using lock ON sort ON bufferpool ON uow ON table ON statement ON"
echo now,finding the SQLs which made lockwait
db2  "select AGENT_ID ,substr(STMT_TEXT,1,100) as statement,STMT_ELAPSED_TIME_MS  from table(SNAPSHOT_STATEMENT('$dbname',-1)) as B where AGENT_ID in (select AGENT_ID_HOLDING_LK from table(SNAPSHOT_LOCKWAIT('$dbname',-1)) as A  order by  LOCK_WAIT_START_TIME ASC FETCH FIRST 20 ROWS ONLY ) order by STMT_ELAPSED_TIME_MS DESC" > $filename
echo The SQLs have saved to the file $filename
--4) 尝试使用 db2advis工具优化索引

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。