ITPub博客

首页 > Linux操作系统 > Linux操作系统 > db2_查询锁方法

db2_查询锁方法

原创 Linux操作系统 作者:redhouser 时间:2011-08-03 16:56:08 0 删除 编辑

1,获取锁方法:
1.1管理视图:
SELECT * FROM SYSIBMADM.SNAPLOCK
select * from SYSIBMADM.LOCKS_HELD

1.2 表函数:
SELECT * FROM TABLE(SNAP_GET_LOCK('SAMPLE',-1)) AS SNAPLOCK;
SELECT * FROM TABLE(SNAPSHOT_LOCK('SAMPLE',-1)) AS SNAPLOCK;

1.3 CLP:
get snapshot for locks for application applid appl-id
get snapshot for locks for application agentid appl-handle
get snapshot for locks on dbname

1.4 db2bp工具:
db2pd -inst db2 -db sample -locks

2,案例
2.0 环境准备
--db2 connect to sample
--db2 get dbm monitor switches
     收集到的 DBM 系统监视器信息

数据库分区号 0 的开关列表
缓冲池活动信息        (BUFFERPOOL) = OFF
锁定信息                    (LOCK) = ON  2011-08-03 10:53:09.067114
排序信息                    (SORT) = OFF
QL 语句信息           (STATEMENT) = OFF
表活动信息                 (TABLE) = OFF
获取时间戳记信息(时间戳记)    = ON  2011-08-03 10:51:55.773736
工作单元信息                 (UOW) = OFF

--db2 get monitor switches
            监视器记录开关
数据库分区号 0 的开关列表
缓冲池活动信息        (BUFFERPOOL) = OFF
锁定信息                    (LOCK) = OFF
排序信息                    (SORT) = OFF
SQL 语句信息           (STATEMENT) = OFF
表活动信息                 (TABLE) = OFF
获取时间戳记信息(时间戳记)    = ON  2011-08-03 10:51:55.773736
工作单元信息                 (UOW) = OFF

--db2 update monitor switches using LOCK on
DB20000I  UPDATE MONITOR SWITCHES 命令成功完成。

--db2 get monitor switches
            监视器记录开关
数据库分区号 0 的开关列表
缓冲池活动信息        (BUFFERPOOL) = OFF
锁定信息                    (LOCK) = ON  2011-08-03 13:57:43.085020
排序信息                    (SORT) = OFF
SQL 语句信息           (STATEMENT) = OFF
表活动信息                 (TABLE) = OFF
获取时间戳记信息(时间戳记)    = ON  2011-08-03 10:51:55.773736
工作单元信息                 (UOW) = OFF

--db2 -c- declare c1 cursor for select * from staff where job='Sales' for update
DB20000I  SQL 命令成功完成。

--db2 -c- open c1
DB20000I  SQL 命令成功完成。

--db2 -c- fetch c1

ID     NAME      DEPT   JOB   YEARS  SALARY    COMM
------ --------- ------ ----- ------ --------- ---------
    20 Pernal        20 Sales      8  78171.25    612.45

  1 条记录已选择。


2.1通过管理视图查询
--db2 select * from SYSIBMADM.LOCKS_HELD
SNAPSHOT_TIMESTAMP         DB_NAME  AGENT_ID     APPL_NAME  AUTHID 
-------------------------- -------- ------------ ---------- -------
2011-08-03-16.20.17.373920 SAMPLE            300 db2bp.exe  mh 
2011-08-03-16.20.17.373920 SAMPLE            300 db2bp.exe  mh 
2011-08-03-16.20.17.373920 SAMPLE            300 db2bp.exe  mh 
2011-08-03-16.20.17.373920 SAMPLE            300 db2bp.exe  mh 
2011-08-03-16.20.17.373920 SAMPLE            300 db2bp.exe  mh 


TBSP_NAME  TABSCHEMA   TABNAME  TAB_FILE_ID    LOCK_OBJECT_TYPE  
---------- ----------- -------- -------------- ------------------
-          -           -                     0 INTERNALV_LOCK    
USERSPACE1 mh      STAFF                15 ROW_LOCK          
-          -           -                     0 INTERNALV_LOCK    
-          -           -                     0 INTERNALP_LOCK    
USERSPACE1 mh      STAFF                15 TABLE_LOCK        


LOCK_NAME                        LOCK_MODE  LOCK_STATUS LOCK_ESCALATION DBPARTITIONNUM
-------------------------------- ---------- ----------- --------------- --------------
0x01000000010000000100A02256     S          GRNT                      0              0
0x02000F00050000000000000052     U          GRNT                      0              0
0x02000000010000000100204056     S          GRNT                      0              0
0x53514C43324832307F4760B841     S          GRNT                      0              0
0x02000F00000000000000000054     IX         GRNT                      0              0


--db2 SELECT * FROM SYSIBMADM.SNAPLOCK
SNAPSHOT_TIMESTAMP         AGENT_ID TAB_FILE_ID  LOCK_OBJECT_TYPE   LOCK_MODE 
-------------------------- -------- ------------ ------------------ ----------
2011-08-03-16.21.58.921579      300           0  INTERNALV_LOCK     S         
2011-08-03-16.21.58.921579      300           0  INTERNALP_LOCK     S         


LOCK_STATUS LOCK_ESCALATION TABNAME  TABSCHEMA   TBSP_NAME LOCK_ATTRIBUTES LOCK_COUNT
----------- --------------- -------- ----------- --------- --------------- ----------
GRNT                      0 -        -           -         NONE            1         
GRNT                      0 -        -           -         NONE            1         


LOCK_CURRENT_MODE LOCK_HOLD_COUNT  LOCK_NAME                        LOCK_RELEASE_FLAGS   
----------------- ---------------- -------------------------------- -------------------- 
-                                0 0x01000000020000000100207356               1073741824 
-                                0 0x53514C43324832307F4760B841               1073741824 


DATA_PARTITION_ID DBPARTITIONNUM   
----------------- --------------   
                -              0   
                -              0   


2.2使用表函数
--db2 SELECT * FROM TABLE(SNAP_GET_LOCK('SAMPLE',-1)) AS SNAPLOCK
SNAPSHOT_TIMESTAMP         AGENT_ID    TAB_FILE_ID   LOCK_OBJECT_TYPE  
-------------------------- ----------- ------------- ------------------
2011-08-03-16.47.14.996360         300             0 INTERNALV_LOCK    
2011-08-03-16.47.14.996360         300             0 INTERNALP_LOCK    

LOCK_MODE  LOCK_STATUS LOCK_ESCALATION TABNAME  
---------- ----------- --------------- ---------
S          GRNT                      0 -        
S          GRNT                      0 -        

TABSCHEMA  TBSP_NAME  LOCK_ATTRIBUTES  LOCK_COUNT 
---------- ---------- ---------------- -----------
-          -          NONE                       1
-          -          NONE                       1

LOCK_CURRENT_MODE LOCK_HOLD_COUNT      LOCK_NAME                       
----------------- -------------------- --------------------------------
-                                    0 0x01000000020000000100805E56    
-                                    0 0x53514C43324832307F4760B841    

LOCK_RELEASE_FLAGS   DATA_PARTITION_ID DBPARTITIONNUM
-------------------- ----------------- --------------
          1073741824                 -              0
          1073741824                 -              0

--db2 SELECT * FROM TABLE(SNAPSHOT_LOCK('SAMPLE',-1)) AS SNAPLOCK
SNAPSHOT_TIMESTAMP         AGENT_ID    TABLE_FILE_ID  LOCK_OBJECT_TYPE 
-------------------------- ----------- -------------- -----------------
2011-08-03-16.50.44.806587         300              0                 9
2011-08-03-16.50.44.806587         300              0                 8

LOCK_MODE  LOCK_STATUS   LOCK_OBJECT_NAME     PARTITION_NUMBER
---------- ------------- -------------------- ----------------
         3             1                    0                -
         3             1                    0                -

LOCK_ESCALATION TABLE_NAME TABLE_SCHEMA  TABLESPACE_NAME
--------------- ---------- ------------- ----------------
              0 -          -             -              
              0 -          -             -             

2.3CLP
--db2 get snapshot for locks on sample

            数据库锁定快照

数据库名称                               = SAMPLE
数据库路径                          = C:\DB2\NODE0000\SQL00001\
输入数据库别名                      = SAMPLE
挂起的锁定                      = 16
当前已连接的应用程序            = 4
当前正等待锁定的代理程序数      = 0
快照时间戳记                    = 2011-08-03 16:17:06.280072

...

应用程序句柄                               = 300
应用程序标识                        = *LOCAL.DB2.110803055722
序号                                = 00006
应用程序名                          = db2bp.exe
CONNECT 授权标识                           = mh
应用程序状态                        = UOW 正在等待
状态更改时间                        = 未收集
应用程序代码页                             = 1386
挂起的锁定                      = 4
总计等待时间(毫秒)            = 0

锁定列表
 锁定名称                       = 0x02000F00050000000000000052
 锁定属性                       = 0x00000000
 发行版标志                     = 0x00000001
 锁定计数                       = 1
 挂起计数                       = 0
 锁定对象名                     = 5
 对象类型                       = 行
 表空间名                       = USERSPACE1
 表模式               = mh
 表名                 = STAFF
 方式                           = U

 锁定名称                       = 0x02000000010000000100204056
 锁定属性                       = 0x00000000
 发行版标志                     = 0x40000000
 锁定计数                       = 1
 挂起计数                       = 0
 锁定对象名                     = 0
 对象类型                       = 内部变化锁定
 方式                           = S

 锁定名称                       = 0x53514C43324832307F4760B841
 锁定属性                       = 0x00000000
 发行版标志                     = 0x40000000
 锁定计数                       = 1
 挂起计数                       = 0
 锁定对象名                     = 0
 对象类型                       = 内部方案锁定
 方式                           = S

 锁定名称                       = 0x02000F00000000000000000054
 锁定属性                       = 0x00000000
 发行版标志                     = 0x00000001
 锁定计数                       = 1
 挂起计数                       = 0
 锁定对象名                     = 15
 对象类型                       = 表
 表空间名                       = USERSPACE1
 表模式               = mh
 表名                 = STAFF
 方式                           = IX

--db2 get snapshot for locks for application agentid 300

            应用程序锁定快照

快照时间戳记                    = 2011-08-03 16:19:18.814303

应用程序句柄                               = 300
应用程序标识                        = *LOCAL.DB2.110803055722
序号                                = 00006
应用程序名                          = db2bp.exe
CONNECT 授权标识                           = mh
应用程序状态                        = UOW 正在等待
状态更改时间                        = 未收集
应用程序代码页                             = 1386
挂起的锁定                      = 4
总计等待时间(毫秒)            = 0

锁定列表
 锁定名称                       = 0x02000F00050000000000000052
 锁定属性                       = 0x00000000
 发行版标志                     = 0x00000001
 锁定计数                       = 1
 挂起计数                       = 0
 锁定对象名                     = 5
 对象类型                       = 行
 表空间名                       = USERSPACE1
 表模式               = mh
 表名                 = STAFF
 方式                           = U

 锁定名称                       = 0x02000000010000000100204056
 锁定属性                       = 0x00000000
 发行版标志                     = 0x40000000
 锁定计数                       = 1
 挂起计数                       = 0
 锁定对象名                     = 0
 对象类型                       = 内部变化锁定
 方式                           = S

 锁定名称                       = 0x53514C43324832307F4760B841
 锁定属性                       = 0x00000000
 发行版标志                     = 0x40000000
 锁定计数                       = 1
 挂起计数                       = 0
 锁定对象名                     = 0
 对象类型                       = 内部方案锁定
 方式                           = S

 锁定名称                       = 0x02000F00000000000000000054
 锁定属性                       = 0x00000000
 发行版标志                     = 0x00000001
 锁定计数                       = 1
 挂起计数                       = 0
 锁定对象名                     = 15
 对象类型                       = 表
 表空间名                       = USERSPACE1
 表模式               = mh
 表名                 = STAFF
 方式                           = IX


2.4 db2pd工具
--db2pd -inst db2 -db sample -locks
Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 05:38:35 
Locks:
Address    TranHdl Lockname                   Type  Mode Sts Owner Dur HoldCount Att        ReleaseFlg rrIID
0x7FC75200 8       02001C00000000000000000054 Table .IN  G   8     1   1         0x00002000 0x40000000 0    
0x7FC71A80 6       02001600000000000000000054 Table .IX  G   6     1   1         0x00003000 0x40000000 0    
0x7FC74300 9       02001D00000000000000000054 Table .IN  G   9     3   1         0x00002000 0x40000000 0    
0x7FC74C80 8       02001700000000000000000054 Table .IN  G   8     3   1         0x00002000 0x40000000 0    
0x7FC74400 9       02001E00000000000000000054 Table .IN  G   9     1   1         0x00002000 0x40000000 0    
0x7FC74D80 8       02001800000000000000000054 Table .IN  G   8     1   1         0x00002000 0x40000000 0    
0x7FC74E80 8       02001900000000000000000054 Table .IN  G   8     1   1         0x00002000 0x40000000 0    
0x7FC71B00 6       02001300000000000000000054 Table .IX  G   6     1   1         0x00003000 0x40000000 0    
0x7FC74F80 8       02001A00000000000000000054 Table .IN  G   8     1   1         0x00002000 0x40000000 0    
0x7FC71980 6       02001400000000000000000054 Table .IX  G   6     1   1         0x00003000 0x40000000 0    
0x7FC75100 8       02001B00000000000000000054 Table .IN  G   8     1   1         0x00002000 0x40000000 0    
0x7FC71A00 6       02001500000000000000000054 Table .IX  G   6     1   1         0x00003000 0x40000000 0    

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-704004/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    810241