DB2锁表相关笔记

1、看锁表的情况

db2的命令中：

db2 => get snapshot for locks on databasename

可以看到什么表被锁住了。

其中有一项： Application handle表示进程的标识号。该进程锁住什么表在下面会详细的列出来。

或者

list application for database yourdatabasename show detail

看看应用程序执行的情况

2、断掉连接：

通过

force application all //强行终止所有连接

terminate //清除所有db2的后台进程

将所有的进程全部清除

或者：

force application(进程号)，将特定的进程号kill

---------------------------------------------------------------------------------------------------------------------

---查看SAMPLE库上表的锁数量

SELECT TABSCHEMA, TABNAME, COUNT(*) AS NUMBER_OF_LOCKS_HELD 

   FROM SYSIBMADM.LOCKS_HELD 

   --WHERE DB_NAME = 'SAMPLE' 

   GROUP BY DBPARTITIONNUM, TABSCHEMA, TABNAME

 

----查询数据库锁表信息

db2 "select agent_id, tabschema,tabname from sysibmadm.snaplock"

 

----查看锁等待

SELECT SMALLINT(AGENT_ID) AS WAITING_ID,       SUBSTR(APPL_NAME, 1,10) AS WAITING_APP,       SUBSTR(AUTHID,1,10) AS WAITING_USER,       SMALLINT(AGENT_ID_HOLDING_LK) AS HOLDER_ID,       LOCK_MODE AS HELD,        LOCK_OBJECT_TYPE AS TYPE,        LOCK_MODE_REQUESTED AS REQUEST FROM SYSIBMADM.LOCKWAITS

 

查看锁表信息

-------------

SELECT substr(TABSCHEMA,1,10), substr(TABNAME,1,20), COUNT(*) AS NUMBER_OF_LOCKS_HELD FROM SYSIBMADM.LOCKS_HELD  GROUP BY DBPARTITIONNUM, TABSCHEMA, TABNAME