db2 connect to xxxxxx
db2 "create event monitor dlmon for tables,statements,deadlocks with details write to table"
db2 "set event monitor dlmon state 1"
db2 "select evmonname, event_mon_state(evmonname) as enabled from syscat.eventmonitors"
####Executions applications
db2 "set event monitor dlmon state 0"

####获取监控的时间范围内发生的死锁情况
SELECT AGENT_ID,APPL_ID,APPL_ID_HOLDING_LK,CURSOR_NAME,DEADLOCK_ID,TABLE_SCHEMA,TABLE_NAME,CAST(STMT_TEXT AS VARCHAR(4000)) STMT_TEXT FROM DB2INST1.DLCONN_DLMON WHERE START_TIME > CURRENT_TIMESTAMP - X HOURS ORDER BY DEADLOCK_ID WITH UR;
####用下面的SQL获取全部相关应用的SQL信息
SELECT DISTINCT AGENT_ID,APPL_ID,CURSOR_NAME,CAST(STMT_TEXT AS VARCHAR(4000)) STMT_TEXT FROM DB2INST1.STMT_DLMON WHERE
AGENT_ID IN (39890,39874) AND APPL_ID IN ('9.109.122.174.46936.160329112130','9.109.122.174.46934.160329112129')
AND CURSOR_NAME IN ('SQL_CURSN300C45','SQL_CURSN300C25')
ORDER BY AGENT_ID WITH UR;

在开发环境和测试环境使用这种方法快速找到死锁的SQL，进行SQL性能优化，或者联系开发人员对SQL的执行顺序做调整。
在生产环境下，不要写到表中，写到文件中。

ITPub博客

使用表查询监控DB2的死锁

最新文章