查看锁表 db2
2010-11-11 17:34
方法一：用db2pd
注意 执行db2pd命令时 如果数据库没有激活，则会报这个错误：Database dbname not activated on database partition 0.
首先激活一下数据库 ： db2 activate database dbname
看应用在等什么
#db2pd -db eos -locks showlock wait
Database Partition 0 -- Database EOS -- Active -- Up 0 days 07:42:56
Locks:
Addre TranHdl Lockname Type Mode Sts Owner Dur HldCnt Att Rlse
0x2C8E0760 3 02001806078066020000000052 Row ..X W 2 1 0 0 0x0 TbspaceID 2 TableID 156 RecordID 0x2668007
锁的类型为Row(行锁)，X锁(排他锁)，下面是我们最关心的锁的位置
TbspaceID 2 TableID 156 RecordID 0x2668007
其中TbspaceID为表空间ID，TableID为表的ID，RecordID代表具体位置，全部应该是0x0266807,其中前面三个字节为page number，为0x02668，后面一个字节代表solt identifier，为0x07
3、找到相应的表
#db2 \"select tbspace,tabschema,tabname,tableid,tbspaceid from syscat.tables where tbspaceid=2 and tableid=1560\"
TBSPACE TABSCHEMA TABNAME TABLEID TBSPACEID
------------ ----------- ---------- ------- ---------
USERSPACE1 DB2INST1 AA_TEST 156 2
1 record(s) selected.
方法二：用快照
UPDATE MONITOR SWITCHES USING LOCK ON
GET SNAPSHOT FOR LOCKS ON
update monitor switches using lock off
方法三：
#show all the switches
db2 get monitor switches
#switch-name：
BUFFERPOOL、LOCK、SORT、STATEMENT、TABLE、TIMESTAMP 和 UOW
#set the switches ON/OFF
db2 update monitor switches using table off
#DB2 snapshot 用于锁定位(管理视图)
db2 get snapshot for locks on
Select substr(tabschema,1,8) as tabschema, substr(tabname,1,15) as tabname,lock_object_type,lock_mode, lock_mode_requested, agent_id_holding_lk
From sysibmadm.lockwaits
注：db2 +c为不自动提交(commit)SQL语句，也可以通过 db2 update command options using c off关闭自动提交
(autocommit，缺省是自动提交)
db2 +c insert into lgxswfc values(2)
+++++++++++++ExAMPLE+++++++++++
#seion1
db2 +c insert into lgxswfc values(2)
+++
#seion2
db2 select * from lgxswfc
#此时seion2 挂起
+++
#seion3
Select substr(tabschema,1,8) as tabschema, substr(tabname,1,15) as tabname,lock_object_type,lock_mode, lock_mode_requested, agent_id_holding_lk
From sysibmadm.lockwaits
db2 FORCE APPLICATION(agent_id_holding_lk);－－ 结束进程
