【范例】DB2的锁等待与事务的分析与定位

[dbonl]: /dbonl> db2 get snhot for application agentid 1253                                                                        


            Application Snhot


Application handle = 1253
Application status = UOW Waiting
Status change time = Not Collected
Application code page = 1386
Application country/region code = 1
DUOW correlation token = *LOCAL.dbonl.120203054653
Application name = db2bp
Application ID = *LOCAL.dbonl.120203054653
Sequence number = 00009
TP Monitor client user ID =
TP Monitor client workstation name =
TP Monitor client application name =
TP Monitor client accounting string =


Connection request start timestamp = 02/03/2012 13:46:53.024282
Connect request completion timestamp = 02/03/2012 13:46:53.024401
Application idle time = 1 minute 10 seconds
CONNECT Authorization ID = dbonl
Client login ID = dbonl
Configuration NNAME of client = tldeva
Client database manager product ID = SQL09010
Process ID of client application = 2519162
Platform of client application = AIX 64BIT
Communication protocol of client = Local Client


Inbound communication address = *LOCAL.dbonl


Database name = dbonl
Database path = /onllog/dbonl/NODE0000/SQL00001/
Client database alias = dbonl
Input database alias =
Last reset timestamp =
Snhot timestamp = 02/03/2012 14:04:22.402229
The highest authority level granted =
        Direct DBADM authority
        Direct CREATETAB authority
        Direct BINDADD authority
        Direct CONNECT authority
        Direct CREATE_NOT_FENC authority
        Direct LOAD authority
        Direct IMPLICIT_SCHEMA authority
        Direct CREATE_EXT_RT authority
        Direct QUIESCE_CONN authority
        Indirect SYSADM authority
        Indirect CREATETAB authority
        Indirect BINDADD authority
        Indirect CONNECT authority
        Indirect IMPLICIT_SCHEMA authority
Coordinating database partition number = 0
Current database partition number = 0
Coordinator agent process or thread ID = 1335438
Agents stolen = 0
Agents waiting on locks = 0
Maximum associated agents = 1
Priority at which application agents work = 0
Priority type = Dynamic


Lock timeout (seconds) = -1
Locks held by application = 3
Lock waits since connect = 0
Time application waited on locks (ms) = 0
Deadlocks detected = 0
Lock escalations = 0
Exclusive lock escalations = 0
Number of Lock Timeouts since connected = 0
Total time UOW waited on locks (ms) = Not Collected


Total sorts = 1
Total sort time (ms) = Not Collected
Total sort overflows = 0


Buffer pool data logical reads = Not Collected
Buffer pool data physical reads = Not Collected
Buffer pool temporary data logical reads = Not Collected
Buffer pool temporary data physical reads = Not Collected
Buffer pool data writes = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool index physical reads = Not Collected
Buffer pool temporary index logical reads = Not Collected
Buffer pool temporary index physical reads = Not Collected
Buffer pool index writes = Not Collected
Buffer pool xda logical reads = Not Collected
Buffer pool xda physical reads = Not Collected
Buffer pool temporary xda logical reads = Not Collected
Buffer pool temporary xda physical reads = Not Collected
Buffer pool xda writes = Not Collected
Total buffer pool read time (milliseconds) = Not Collected
Total buffer pool write time (milliseconds)= Not Collected
Time waited for prefetch (ms) = Not Collected
Unread prefetch pages = Not Collected
Direct reads = Not Collected
Direct writes = Not Collected
Direct read requests = Not Collected
Direct write requests = Not Collected
Direct reads eled time (ms) = Not Collected
Direct write eled time (ms) = Not Collected


Number of SQL requests since last commit = 1
Commit statements = 3
Rollback statements = 5
Dynamic SQL statements attempted = 24
Static SQL statements attempted = 8
Failed statement operations = 6
Select SQL statements executed = 3
Xquery statements executed = 0
Update/Insert/Delete statements executed = 3
DDL statements executed = 0
Inactive stmt history memory usage (bytes) = 0
Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 1
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Binds/precompiles attempted = 0
Rows deleted = 0
Rows inserted = 3
Rows updated = 0
Rows selected = 216
Rows read = 537
Rows written = 3


UOW log space used (Bytes) = Not Collected
Previous UOW completion timestamp = Not Collected
Eled time of last completed uow (sec.ms)= Not Collected
UOW start timestamp = Not Collected
UOW stop timestamp = Not Collected
UOW completion status = Not Collected


Open remote cursors = 0
Open remote cursors with blocking = 0
Rejected Block Remote Cursor requests = 0
Accepted Block Remote Cursor requests = 3
Open local cursors = 0
Open local cursors with blocking = 0
Total User CPU Time used by agent (s) = 0.012200
Total System CPU Time used by agent (s) = 0.000805
Host execution eled time = 0.041733


Package cache lookups = 15
Package cache inserts = 3
Application section lookups = 24
Application section inserts = 4
Catalog cache lookups = 17
Catalog cache inserts = 0
Catalog cache overflows = 0
Catalog cache high water mark = 0


Workspace Information


 Shared high water mark = 0
 Total shared overflows = 0
 Total shared section inserts = 0
 Total shared section lookups = 0
 Private high water mark = 26288
 Total private overflows = 0
 Total private section inserts = 4
 Total private section lookups = 6


Most recent operation = Execute Immediate
Most recent operation start timestamp = 02/03/2012 14:03:12.497939
Most recent operation stop timestamp = 02/03/2012 14:03:12.498063
Agents associated with the application = 1
Number of hash joins = 0
Number of hash loops = 0
Number of hash join overflows = 0
Number of small hash join overflows = 0
Statement type = Dynamic SQL Statement
Statement = Execute Immediate
Section number = 203
Application creator = NULLID
Package name = SQLC2F0A
Consistency Token = AAAAAHLV
Package Version ID =
Cursor name =
Statement database partition number = 0
Statement start timestamp = 02/03/2012 14:03:12.497939
Statement stop timestamp = 02/03/2012 14:03:12.498063
Eled time of last completed stmt(sec.ms)= 0.000124
Total Statement user CPU time = 0.000000
Total Statement system CPU time = 0.000000
SQL compiler cost estimate in timerons = 8
SQL compiler cardinality estimate = 1
Degree of parallelism requested = 1
Number of agents working on statement = 0
Number of subagents created for statement = 1
Statement sorts = 0
Total sort time = 0
Sort overflows = 0
Rows read = 0
Rows written = 1
Rows deleted = 0
Rows updated = 0
Rows inserted = 0
Rows fetched = 0
Buffer pool data logical reads = Not Collected
Buffer pool data physical reads = Not Collected
Buffer pool temporary data logical reads = Not Collected
Buffer pool temporary data physical reads = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool temporary index logical reads = Not Collected
Buffer pool temporary index physical reads = Not Collected
Buffer pool xda logical reads = Not Collected
Buffer pool xda physical reads = Not Collected
Buffer pool temporary xda logical reads = Not Collected
Buffer pool temporary xda physical reads = Not Collected
Blocking cursor = NO
Dynamic SQL statement text:
insert into num_inf(num,desc,rec_opr_id,rec_upd_opr) values('9990','测试插入','1','initOpr')



Agent process/thread ID = 1335438
  Agent Lock timeout (seconds) = -1
  Memory usage for agent:


    Memory Pool Type = Other Memory
       Current size (bytes) = 131072
       High water mark (bytes) = 655360
       Configured size (bytes) = 4294967296


    Memory Pool Type = Application Control Heap
       Current size (bytes) = 65536
       High water mark (bytes) = 65536
       Configured size (bytes) = 655360


    Memory Pool Type = Application Heap
       Current size (bytes) = 196608
       High water mark (bytes) = 196608
       Configured size (bytes) = 1245184