锁事件监控器可以抓取死锁、锁超时事件,包含这些事件发生时应用正在执行的SQL语句。 具体的介绍可以参考下面的官方文档:
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1004lockeventmonitor/
我这里只是做个测试,过程与上面链接中基本一致:
C:\windows\system32>db2 update db cfg for sample using locktimeout 10
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
C:\windows\system32> db2 update db cfg for sample using mon_lockwait HISTORY mon_deadlock history mon_locktimeout history
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
C:\windows\system32>db2 connect to sample
Database Connection Information
Database server = DB2/NT64 10.1.5
SQL authorization ID = MIAOQING...
Local database alias = SAMPLE
C:\windows\system32>db2 "CREATE EVENT MONITOR LOCKEVMON FOR LOCKING WRITE TO UNFORMATTED EVENT TABLE (TABLE LOCKEVMON)"
DB20000I The SQL command completed successfully.
C:\windows\system32>db2 set event monitor lockevmon state = 1
DB20000I The SQL command completed successfully.
//simulate a locktimeout, 模拟出一个锁超时
C:\windows\system32>db2 flush event monitor LOCKEVMON
DB20000I The SQL command completed successfully.
C:\windows\system32>db2 set event monitor LOCKEVMON state=0
DB20000I The SQL command completed successfully.
C:\windows\system32>db2level
DB21085I This instance or install (instance name, where applicable:
"DB2INST1") uses "64" bits and DB2 code release "SQL10056" with level
identifier "0607010E".
Informational tokens are "DB2 v10.5.600.232", "s150731", "IP23638", and Fix
Pack "6".
Product is installed at "C:\db2installpath" with DB2 Copy Name "DB2COPY1".
copy "C:\db2installpath\samples\java\jdbc\db2evmonfmt.java" .
copy "C:\db2installpath\samples\java\jdbc\DB2EvmonLocking.xsl" .
set PATH=C:\db2installpath\java\jdk\bin;%PATH%
javac db2evmonfmt.java
java db2evmonfmt -d sample -ue LOCKEVMON -ftext > c:\locktimeout1.txt
下面是locktimeout1.txt的内容:
SELECT evmon.xmlreport FROM TABLE ( EVMON_FORMAT_UE_TO_XML( 'LOG_TO_FILE',FOR EACH ROW OF ( SELECT * FROM LOCKEVMON ORDER BY EVENT_ID, EVENT_TIMESTAMP, EVENT_TYPE, MEMBER ))) AS evmon
-------------------------------------------------------
Event ID : 1
Event Type : LOCKWAIT
Event Timestamp : 2016-04-20-08.54.33.461851
Partition of detection : 0
-------------------------------------------------------
Participant No 1 requesting lock
----------------------------------
Lock Name : 0x02001000110000000000000052
Lock wait start time : 2016-04-20-08.54.28.395295
Lock wait end time : 2016-04-20-08.54.38.580195
Lock Type : ROW
Lock Specifics : ROWID=17,DATA_PARTITION_ID=0,PAGEID=0
Lock Attributes : 00000000
Lock mode requested : Update
Lock mode held : Exclusive
Lock Count : 0
Lock Hold Count : 0
Lock rrIID : 0
Lock Status : Waiting
Lock release flags : 00000000
Tablespace TID : 2
Tablespace Name : USERSPACE1
Table FID : 16
Table Schema : MIAOQINGSONG
Table Name : SALES
Attributes Requester Owner
--------------------- ------------------------------ ------------------------------
Participant No 1 2
Application Handle 074 062
Application ID *LOCAL.DB2INST1.160420005337 *LOCAL.DB2INST1.160420005247
Application Name db2bp.exe db2bp.exe
Authentication ID MIAOQINGSONG MIAOQINGSONG
Requesting AgentID 9184 6056
Coordinating AgentID 9184 6056
Agent Status UOW Executing UOW Waiting
Application Action No action No action
Lock timeout value 10 0
Lock wait value 5000 0
Workload ID 1 1
Workload Name SYSDEFAULTUSERWORKLOAD SYSDEFAULTUSERWORKLOAD
Service subclass ID 13 13
Service superclass SYSDEFAULTUSERCLASS SYSDEFAULTUSERCLASS
Service subclass SYSDEFAULTSUBCLASS SYSDEFAULTSUBCLASS
Current Request Execute Immediate Execute Immediate
TEntry state 1 2
TEntry flags1 00000000 00000000
TEntry flags2 00000200 00000200
Lock escalation no no
Client userid
Client wrkstnname
Client applname
Client acctng
Utility ID
Current Activities of Participant No 1
----------------------------------------
Activity ID : 1
Uow ID : 1
Package Name : SQLC2K26
Package Schema : NULLID
Package Version :
Package Token : AAAAAfAd
Package Sectno : 203
Reopt value : none
Incremental Bind : no
Eff isolation : CS
Eff degree : 0
Actual degree : 1
Eff locktimeout : 10
Stmt first use : 2016-04-20-08.54.28.385018
Stmt last use : 2016-04-20-08.54.28.385018
Stmt unicode : no
Stmt query ID : 0
Stmt nesting level : 0
Stmt invocation ID : 0
Stmt source ID : 0
Stmt pkgcache ID : 2070174236673
Stmt type : Dynamic
Stmt operation : DML, Insert/Update/Delete
Stmt no : 1
Stmt text : delete from sales where sales = 7
Past Activities of Participant No 1
-------------------------------------
Activities not available
Current Activities of Participant No 2
----------------------------------------
Activities not available
Past Activities of Participant No 2
-------------------------------------
Past Activities wrapped: no
Activity ID : 1
Uow ID : 3
Package Name : SQLC2K26
Package Schema : NULLID
Package Version :
Package Token : AAAAAfAd
Package Sectno : 203
Reopt value : none
Incremental Bind : no
Eff isolation : CS
Eff degree : 0
Actual degree : 1
Eff locktimeout : 10
Stmt first use : 2016-04-20-08.54.15.690439
Stmt last use : 2016-04-20-08.54.15.690439
Stmt unicode : no
Stmt query ID : 0
Stmt nesting level : 0
Stmt invocation ID : 0
Stmt source ID : 0
Stmt pkgcache ID : 768799145985
Stmt type : Dynamic
Stmt operation : DML, Insert/Update/Delete
Stmt no : 1
Stmt text : delete from sales where sales=7
-------------------------------------------------------
Event ID : 2
Event Type : LOCKTIMEOUT
Event Timestamp : 2016-04-20-08.54.38.580233
Partition of detection : 0
-------------------------------------------------------
Participant No 1 requesting lock
----------------------------------
Lock Name : 0x02001000110000000000000052
Lock wait start time : 2016-04-20-08.54.28.395295
Lock wait end time : 2016-04-20-08.54.38.580233
Lock Type : ROW
Lock Specifics : ROWID=17,DATA_PARTITION_ID=0,PAGEID=0
Lock Attributes : 00000000
Lock mode requested : Update
Lock mode held : Exclusive
Lock Count : 0
Lock Hold Count : 0
Lock rrIID : 0
Lock Status : Waiting
Lock release flags : 00000000
Tablespace TID : 2
Tablespace Name : USERSPACE1
Table FID : 16
Table Schema : MIAOQINGSONG
Table Name : SALES
Attributes Requester Owner
--------------------- ------------------------------ ------------------------------
Participant No 1 2
Application Handle 074 062
Application ID *LOCAL.DB2INST1.160420005337 *LOCAL.DB2INST1.160420005247
Application Name db2bp.exe db2bp.exe
Authentication ID MIAOQINGSONG MIAOQINGSONG
Requesting AgentID 9184 6056
Coordinating AgentID 9184 6056
Agent Status UOW Executing UOW Waiting
Application Action No action No action
Lock timeout value 10 0
Lock wait value 5000 0
Workload ID 1 1
Workload Name SYSDEFAULTUSERWORKLOAD SYSDEFAULTUSERWORKLOAD
Service subclass ID 13 13
Service superclass SYSDEFAULTUSERCLASS SYSDEFAULTUSERCLASS
Service subclass SYSDEFAULTSUBCLASS SYSDEFAULTSUBCLASS
Current Request Execute Immediate Execute Immediate
TEntry state 1 2
TEntry flags1 00000000 00000000
TEntry flags2 00000200 00000200
Lock escalation no no
Client userid
Client wrkstnname
Client applname
Client acctng
Utility ID
Current Activities of Participant No 1
----------------------------------------
Activity ID : 1
Uow ID : 1
Package Name : SQLC2K26
Package Schema : NULLID
Package Version :
Package Token : AAAAAfAd
Package Sectno : 203
Reopt value : none
Incremental Bind : no
Eff isolation : CS
Eff degree : 0
Actual degree : 1
Eff locktimeout : 10
Stmt first use : 2016-04-20-08.54.28.385018
Stmt last use : 2016-04-20-08.54.28.385018
Stmt unicode : no
Stmt query ID : 0
Stmt nesting level : 0
Stmt invocation ID : 0
Stmt source ID : 0
Stmt pkgcache ID : 2070174236673
Stmt type : Dynamic
Stmt operation : DML, Insert/Update/Delete
Stmt no : 1
Stmt text : delete from sales where sales = 7
Past Activities of Participant No 1
-------------------------------------
Activities not available
Current Activities of Participant No 2
----------------------------------------
Activities not available
Past Activities of Participant No 2
-------------------------------------
Past Activities wrapped: no
Activity ID : 1
Uow ID : 3
Package Name : SQLC2K26
Package Schema : NULLID
Package Version :
Package Token : AAAAAfAd
Package Sectno : 203
Reopt value : none
Incremental Bind : no
Eff isolation : CS
Eff degree : 0
Actual degree : 1
Eff locktimeout : 10
Stmt first use : 2016-04-20-08.54.15.690439
Stmt last use : 2016-04-20-08.54.15.690439
Stmt unicode : no
Stmt query ID : 0
Stmt nesting level : 0
Stmt invocation ID : 0
Stmt source ID : 0
Stmt pkgcache ID : 768799145985
Stmt type : Dynamic
Stmt operation : DML, Insert/Update/Delete
Stmt no : 1
Stmt text : delete from sales where sales=7
C:\windows\system32>db2 drop event monitor LOCKEVMON
DB20000I The SQL command completed successfully.
C:\windows\system32>db2 drop table LOCKEVMON
DB20000I The SQL command completed successfully.
注意:如果是AIX或者LINUX环境下,可以在sqllib目录下找到db2evmonfmt.java 和 DB2EvmonLocking.xsl 文件