db2锁等待和死锁
telnet 182.100.100.51db2pd -db aaa -locks -transactions -applications -dynamic -file lock.txt
db2pd -db aaa -locks show detail -file lock.txt
db2pd -db aaa -locks showlock wait -file lock.txt
db2pd -db aaa -tcb
select tbspace,tabschema,tabname,tableid,tbspaceid from syscat.tables where tbspaceid=3 and tableid=335
C:\>db2 update monitor switches using lock on
C:\>db2 get snapshot for locks on datcmg | more
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCountAttReleaseFlg
0x070000002F7B6B40 43 0002010C0000000077A3000252 Row .NSG 43 1 0 0x00 0x00000001
0x07000000302241C0 23 0002010C0000000077A3000252 Row ..XW 43 1 0 0x00 0x40000000
//事物23被事物43锁了
Transactions:
Address AppHandl TranHdl Locks State Tflag Tflag2 Firstlsn Lastlsn LogSpace SpaceReserved TID AxRegCnt GXID ClientUserID ClientWrkstnName ClientApplName ClientAccntng
0x0700000020326500 22334 23 4 READ 0x00000000 0x00000000 0x000000000000 0x000000000000 0 0 0x000000A1B3E7 1 0 n/a JYKL0 n/a n/a
0x0700000020339B00 22322 43 29 READ 0x00000000 0x00000000 0x000000000000 0x000000000000 0 0 0x000000A1AFE5 1 0 n/a JYKL0 n/a n/a
//事物23关联应用22334，事物43关联应用22322
Applications:
Address AppHandl NumAgentsCoorEDUIDStatus C-AnchID C-StmtUIDL-AnchID L-StmtUIDAppid
0x0780000001070080 22334 1 26791 Lock-wait 53 5393 243 7524 182.100.100.50.51075.1002010812
0x0780000001203260 22322 1 6427 UOW-Waiting 0 0 188 7426 182.100.100.50.51023.1002010812
//通过sql坐标可以定位锁sql和被锁sql
Dynamic SQL Statements:
Address AnchID StmtUID NumEnv NumVar NumRef NumExe Text
0x070000002F019680 53 5393 1 1 13 13 DELETE FROM LJSGetDraw WHERE
GetNoticeNo = ? AND PolNo = ? AND DutyCode = ? AND GetDutyKind = ? AND GetDutyCode = ? AND Currency = ?
0x070000003A28CBE0 188 7426 1 1 1 1 SELECT * FROM LJSGetDraw a
WHERE not exists(select 'X' from LJAGetDraw where PolNo=a.PolNo and DutyCode=a.DutyCode and GetDutyCode=a.GetDutyCode and
GetDutyKind=a.GetDutyKind and GetNoticeNo=a.GetNoticeNo) and GetDate<=date('2010-01-29') and nvl(ComeFlag,'x')<>'1' and
RReportFlag='0' and (not exists(select 1 from lccontstate where PolNo=a.PolNo and statetype='Available' and state='1' and
enddate is null) and not exists(select 'X' from lcconthangupstate where contno =a.contno and hanguptype='2' ) and
exists(select 'X' from LCPol where PolNo=a.PolNo and AppFlag='1' ) or exists (select 'x' from LMDutyGetAlive where
MAXGETCOUNTTYPE='0' and GETDUTYCODE=a.GETDUTYCODE))
//WebSphere的jndi连接池自动设置高级别的db2隔离级别
不同一个数据库连接的事物会有隔离级别的控制，在本地连接db2级别低，但是WebSphere的jndi连接池自动设置高级别的db2隔离级别，
所以在长时间使用sql语句的时候，需要手工设置隔离级别为最低ur，来避免锁。
具体修改是在RSWrapper.java类增加
strSQL += " with ur";
所有sql都手工设置隔离级别ur
死锁监控器：
9.创建一个针对死锁的event monitor
db2 "create event monitor dlmon for deadlocks with details write to file '/home/db2inst1/evmon'";
db2 set event monitor dlmon state 1;激活
db2evmon -db datcmg -evm dlmon >/home/db2inst1/文件名
sql语句性能建议：
(请注意用相应db2用户telnet)
db2adivs -d dbname -i 1.sql>/home/db2inst1/文件名
抓快照：
db2 get snapshot for db on datcmg > /home/db2inst1/文件名
sql语句慢的快照：
db2 get snapshot for dynamic sql on sample
查询执行慢的sql：
select * from sysibmadm.top_dynamic_sql order by average_execution_time_s desc
页:
[1]