使用表查詢監控DB2的死鎖

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的執行順序做調整。
在生產環境下，不要寫到表中，寫到檔案中。