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的执行顺序做调整。
在生产环境下,不要写到表中,写到文件中。
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博客 ” ,链接:http://blog.itpub.net/725820/viewspace-2072055/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/725820/viewspace-2072055/