Thread History Lock Waits

This panel provides detailed information about lock waits that occurred for a thread.

Thread data must be collected by the Near-Term History Data Collector with the LOCKCONT(YES) or LOCKSUSP(YES) keywords. Otherwise, the panel displays a message that lock wait information is not available for a thread.


________________ ZHTWAIT  VTM     O2       V540.#P SE12 11/06/13 13:45:16   2  
> Help PF1               Back PF3               Up PF7                 Down PF8 
                                                                                
>          THREAD HISTORY:  Enter a selection letter on the top line.           
                                                                                
> A-THREAD DETAIL   B-LOCK COUNTS  *-LOCK WAITS     D-GLOBAL LOCKS  E-SORT/SCAN 
> F-DYNAMIC SQL     G-SQL COUNTS   H-DISTRIBUTED    I-BUFFER POOL   J-GROUP BP  
> K-PACKAGE SUMMARY L-RES LIMIT    M-PARALLEL TASKS                             
=============================================================================== 
>                           THREAD HISTORY LOCK WAITS                           
 HPLN                                                                           
+ Thread:  Plan=DSNESPCS  Connid=TSO      Corrid=MIS          Authid=MIS        
+ Attach:  TSO            DB2=SE12        MVS=PMO4                              
+ Time  :  Start=11/06/2013 13:30:02.142363     End=11/06/2013 13:31:39.784388  
+ Luwid=DEIBMIPS.IPSATE12.CC39197D8375                                          
 wait                                                                           
+                               Lock Suspension                                 
+                                                                               
+ Time         Resource                             Type Lvl Dur Elapsed Resume 
+ ------------ ------------------------------------ ---- --- --- ------- ------ 
+ 13:30:02.286 DB=DSNDB06                           DTBS S   CMT     .00 NORMAL 
+ 13:30:02.286 DB=DSNDB06  PS=SYSTSDBA              PALK IX  CMT     .00 NORMAL 
+ 13:30:02.287 DB=DSNDB06  PS=SYSTSDBU              PALK IS  CMT     .00 NORMAL 
+ 13:30:02.288 DB=DSNDB06  PS=SYSGPAUT              PSET IS  CMT     .00 NORMAL 
+ 13:30:02.288 DB=DSNDB06  PS=SYSSEQ2               PSET IX  CMT     .00 NORMAL 
+ 13:30:02.288 DBID=00006  TABL=00379               TABL IX  CMT     .00 NORMAL 
+ 13:30:02.289 DB=DSNDB06  PS=SYSSEQ                PSET IS  CMT     .00 NORMAL 
+ 13:30:02.289 DBID=00006  TABL=00372               TABL IS  CMT     .00 NORMAL 
+ 13:30:02.289 DB=DSNDB06  PS=SYSSEQ                SREC X   MNL     .00 NORMAL 
+ 13:30:02.289 N/A (Lock with non zero token)       N/A  X   INT     .00 NORMAL 
+ 13:30:02.311 DBID=00543                           DTBS U   CMT     .00 NORMAL

Navigation

For additional information about

related topics, choose on of the options at the top of the panel.
other topics, use the PF keys.

Fields

Thread identifier: This information identifies the thread to which the information in this panel applies.

Plan: The Db2 plan name of the active thread.
Connid: The Db2 connection identifier of the active thread.
Corrid: The Db2 correlation identifier of the active thread. If the correlation is not set, N/A is displayed.
Authid: The Db2 authorization identifier of the active thread.
Attach: Depending on the type of connection, the appropriate information is displayed.; Attach information is displayed only if the thread is a distributed allied thread (not for distributed database access threads).

Attachment identifier:

Connection Type

The connection type is displayed in the first field:

APPLDIR: Application directed access (database access threads)
BATCH: Other Db2 batch threads
CALLATCH: Db2 call attach
CICS®: CICS attach
DLI: DL/I batch
IMSBMP: IMS attach BMP
IMSMPP: IMS attach MPP
IMSCTRL: IMS control region
IMSBMPB: IMS transaction BMP
RRSAF: Recoverable Resource Manager Services Attachment Facility
TSO: TSO foreground and background
Utility: Utility thread

Db2

The Db2 subsystem identifier.

MVS

The MVS system identifier.

ORIGAUTH

The original (primary) Db2 authorization identifier of the thread. This field displays only when the original identifier is different from the Authid.

Time identifier:

Start: The start date and time of this thread execution.
End: The end date and time of this thread execution.

Distributed thread identifier: The following fields are displayed if the thread has a distributed relationship with a remote Db2 subsystem.

Type

The distributed thread type.

Distributed Allied: A requesting thread; one that has issued an SQL call to a remote Db2 location.
Database Access: A responding thread; one that is serving a remote Db2 location by responding to an SQL call.

Db2=

The Db2 subsystem ID, indicating the member of the data sharing group of this thread.

Luwid

This value consists of two parts: the logical unit of work ID (luw-id) and a token. The token can be used in place of the luw-id in any Db2 command that accepts luw-id as input. Format:

luw-id=token

The luw-id consists of the network name, the originating VTAM® LUNAME, and a unique identifier (separated by periods). Thus, the Luwid field displays data like in the following example:

USCACO01.O2D22A.A1FE8E04B9D4=8

System

The originating Db2 job name and the resource manager that is the source of the thread. An additional line below the Attach line identifies the user thread, if any, being served by the system thread.

Lock Contention (Timeout): This section is displayed only when the thread execution ended due to a lock timeout and lock contention data was collected.

Resource:

The Db2 resource that was requested by the timed-out thread that was waiting for the resource.

The resource varies depending upon the type of lock requested. If it is possible, the DBid and the OBid of the resource are translated to the DBname and tablespace or to indexspace names.

Lock types such as DPAG, MDEL, TABL, or ROW might include a table record ID in the lock resource. The DBid and OBid of these resources cannot be translated. Therefore the values of the DBid and the OBid are displayed.

This field displays the following resources:

Lock Type

Resource and Explanation

ALBP

Alter buffer pool lock. BP=buffer pool ID

BIND

Autobind/remote bind lock.

BMBA

Buffer manager SCA MBA L-lock.

BMC_MBAO or BMC_MBAR

BPPS Buffer manager pageset RR P-lock

BP=buffer pool ID
DB=database name
PS=pageset name

CCAT

CATMAINT convert catalog lock.

CDIR

CATMAINT convert directory lock.

CDRN

Cursor Stability drain lock

DB=database name
PS=pageset name
PT=partition

CMIG

CATMAINT migration lock.

COLL

Collection lock.

COLL=collection ID

DBEX

Database exception LPL/GRECP lock.

DBXU

DB exception update lock.

DGTT

DGTT URID lock

URID=unit of recovery ID

DPAG

Page lock in a tablespace

DB=database name
PS=pageset name
PG=page

DSET

Partitioned lock

DB=database name
PS=pageset name
PT=partition number

DTBS

Database lock.

DB=database name
PS=pageset name
PT=partition number

GRBP

Group buffer pool start/stop lock.

BP=buffer pool ID

HASH

Hash anchor lock.

DB=database name
PS=pageset name
PG=page

IEOF

Index IEOF.

DB=database name
PS=pageset name
PT=partition

IPAG

Page lock in an indexspace

DB=database name
PS=pageset name
PG=page

MDEL

Mass delete lock.

DB=database name
PS=pageset name

PALK

DB=database name
PS=pageset name
PT=partition

PBPC

Group Bp level castout P-lock.

PCDB

DDF CDB P-lock.

PDBD

PDBD P-lock.

PDSO

Pageset or dataset open lock

DB=database name
PS=pageset name

PITR

Index manager tree P-lock.

DB=database name
PS=pageset name
PT=partition

PPAG

Page P-lock.

PPSC

Pageset/partition level castout P-lock.

PPSP

Pageset/partition P-lock.

PRLF

RLF P-lock.

PSET

Pageset lock.

DB=database name
PS=pageset name

PSPI

Pageset piece lock.

DB=database name
PS=pageset name

RDRN

Repeatable read drain lock.

DB=dataset name
PS=pageset name
PT=partition

RSTR

SCA access for restart/redo lock.

BMC-RSTP

ROW

Row lock.

SDBA

Start/stop lock on DBA table.

DB=dataset name
PS=pageset name

SKCT

Skeleton cursor table lock.

PLAN=plan name

SKPT

Skeleton package table lock.

TOKEN=(CONTOKEN) column from SYSIBM.SYSPACKAGE

SPRC Sys Level PIT Recovery lock

SYS_PITR

SREC

Log range lock.

DB=database name
TS=tablespace name

UIDA

Util I/O Damage Assessment

TABL

Table lock.

DBID=DBid
TABL=Tableid

UNDT

Resource ID (in hexadecimal).

UNKN

Lock without resource provided.

Resource does not exist.

UTEX

Utility exclusive execution lock

UTEXEC

UTID

Utility UID lock.

UID=utility id

UTOB

Utility Object Lock

DB=database name
PS=pageset name
PT=partition

UTSE

Utility serialization lock.

UTSERIAL

WDRN

Write drain lock.

DB=database name
PS=pageset name
PT=partition

Wait:

This section provides information about the lock that ended due to timeout. This section includes the following information:

Lock Level

The level or mode of the lock request. This information describes the level of resource access demanded by the lock request. Possible lock levels are:

IS: Intent share
IX: Intent exclusive
NSU: Nonshared update
S: Share
SIX: Share intent exclusive
U: Update
UNS: Unprotected share
X: Exclusive

Duration

The duration of the requested lock. Possible duration categories are:

Allocation: Lock is held until thread terminates or until plan is deallocated.
COMMIT: Lock is held until Commit is processed.
Commit+1: Lock is held until Commit is processed, unless Lock is needed to maintain the position of the cursor that was opened with the Hold attribute.
Free: Duration to free all locks.
Interest: Lock is held as long as Db2 has interest in the resource. This duration is used only for P-locks.
Manual: A lock of short duration that DB2 acquires to perform tasks such as authorization checking.
Manual+1: A lock of short duration that DB2 acquires to temporarily change from CS to RR during bind or DDL.
Plan: Lock is held until plan is deallocated.
Undetermined: Lock is held for undetermined duration.

IRLM Function

The type of request to the Internal Resource Lock Manager (IRLM). The possible request types are:

LOCK: Lock request
UNLOCK: Unlock request
CHANGE: Change lock request

Request Type

The type of lock request. Possible types are:

Conditional: Conditional lock request.
Unconditional: Unconditional lock request.

Holder's Program Name

The plan name of the thread holding the resource. This might be an application plan name or a Db2 system plan name.

Holder's Coll

Holder's package or DBRM name, which consists of the fields:

QW0172Q1: Program name
QW0172Q2: Package collection ID
QW0172Q3: Location name

Holder's Location

Holder's package or DBRM name, which consists of the fields:

QW0172Q1: Program name
QW0172Q2: Package collection ID
QW0172Q3: Location name

Statement ID

The SQL statement identifier.

Statement Type

The SQL statement type.

Own

Lock ownership information. The following fields provide detailed information about the holders of incompatible locks on the resource that was requested by the timed-out thread.

Plan: The plan name of the lock owners.
Connid: The connection identifiers of the lock owners.
Corrid: The correlation identifier of the lock owners.
Luwid: The Logical Unit-of-Work identifier. This field is displayed only for database access threads (DBATs).
Lock Level: The level of the lock. (See possible lock levels listed for the Wait: section before).
Duration: The duration of the threads holding incompatible locks.

PWait

Priority waiter information. The following fields provide detailed information about any threads that were priority waiters for the resource during the lock request timeout.

Plan: The plan name of the priority waiter.
Connid: The connection identifiers of the priority waiter.
Corrid: The correlation identifier of the priority waiter.
Luwid: The logical unit-of-work identifier. This field is displayed only for database access threads (DBATs).
Lock Level: The level of the lock. For a list of lock levels, see the Wait: section above.
Duration: Indicates the duration of the requested lock. For a list of duration values, see the Duration section above.

Lock contention (deadlock): Deadlock information is displayed only when the thread ended due to a deadlock.

Time of Deadlock: The time that the locking deadlock occurred.
Deadlock Interval: The Db2 deadlock detection interval.
Resource: The Db2 resource that was requested by the deadlocked thread (see table above). The resource format varies depending upon the type of lock requested.
Wait: See Wait fields described under Timeout section above.
Own: See Own fields described under Timeout section above.
DBMS Subsystem ID: The database management subsystem identifier.

Lock Suspension: Lock suspension information is displayed only when lock suspension data was collected.

Time

The time that the lock was suspended.

Resource

The name of the resource for which the lock suspension occurred. The contents of this field depend on the Lock Type (see table above).

If the name of the resource is not available, N/A and the reason why the resource name is not available is displayed. One of the following reasons might be the cause:

Notify IRLM function: Anything to do with lock state, duration, etc. is invalid.
Query IRLM function: Anything to do with lock state, duration, etc. is invalid.
Change with non zero token: Change a non zero value indicates the lock to free. The values of resource and type are not valid.
Unlock with non zero token: Unlock a non zero value indicates the lock to free. The values of resource and type are not valid.
Lock with non zero token: Token should be zero on Lock.
Reason for the suspend=XX: Is displayed if none of the above reasons is found. See QW0044WS for possible XX values.

Type

The type of lock requested. This field determines the contents of the resource name (see table above).

Lvl

The level of the lock.

Dur

The duration of the requested lock. For a list of duration values, see the Wait section above.

Elapsed

The elapsed time (in seconds) for each suspended lock that was resumed.

Resume

The reason why the lock suspension was resumed. Possible reasons:

DEADLK: Deadlock
IDENT: Identify to IRLM
NORMAL: Normal resume
TIMOUT: Timeout