DB2数据库锁问题的解决

查询数据库锁类问题的基本方法：
db2 list application |more
找到app. Handle
db2pd -db zbbb -applications
找到该app. Handle对应的L-AnchID和L-StmtUID。

db2pd -db zbbb -dynamic
通过L-AnchID和L-StmtUID找到该事务对应的sql

 

查找数据库中最早未提交事务的sql：

SELECT AI.APPL_STATUS as Status, AI.AGENT_ID as AGENT_ID, SUBSTR(AI.PRIMARY_AUTH_ID,1,10) AS "Authid", SUBSTR(AI.APPL_NAME,1,15) AS "ApplName", INT(AP.UOW_LOG_SPACE_USED/1024/1024) AS "LogUsed(M)", AP.APPL_IDLE_TIME, AP.APPL_CON_TIME AS "ConnectedSince" FROM SYSIBMADM.SNAPDB DB, SYSIBMADM.SNAPAPPL AP, SYSIBMADM.SNAPAPPL_INFO AI WHERE AI.AGENT_ID = DB.APPL_ID_OLDEST_XACT AND AI.AGENT_ID = AP.AGENT_ID

案例

1.
db2inst1@AMC_S_P550_1 $ db2diag -time 2015-12-09 -level error|more        //该命令可以过滤出db2diag.log中某日的某类型的报告。
2015-12-10-19.41.08.118099+000 I44129965A496      LEVEL: Error
PID     : 516512               TID  : 1           PROC : db2bp
INSTANCE: db2inst1             NODE : 000
APPID   : *LOCAL.db2inst1.161106112624
EDUID   : 1
FUNCTION: <0>, <0>, <0>, probe:2137
MESSAGE : SQL0911N  The current transaction has been rolled back because of a 
          deadlock or timeout.  Reason code "68".
DATA #1 : String, 12 bytes
sqluexpm.SQC
DATA #2 : String, 44 bytes
Error from Import/Export or Load Processing.

2015-12-10-22.41.31.459297+000 I44264614A496      LEVEL: Erro