Locks/Claims Owned by a Thread

This panel shows lock and claim information of a specific thread. Use this information to analyze locks and claims, for example, the number of locks owned, the type and level of the locks, and the object of the locks.

The locking information is divided into the following categories:

The totals of all the types of locks owned by the thread, as well as the percentage of total locks allowed that are currently held (the percent of the Db2 parameter NUMLKUS).
The type, level and object of each lock. HEX values are displayed for lock resources, if the resource is owned by a thread on a remote LPAR, and no OMPE task is available from that LPAR, or XCF is not setup correctly.

The claim information displays all of the claims currently owned by the thread. Claims are used to inform Db2 that an object is being accessed.

 
________________ ZLOCKO   VTM     O2       V550./I SE11 07/11/13 13:43:20   2  
> Help PF1               Back PF3               Up PF7                Down PF8  
                                                                                
>       THREAD INFORMATION:  Enter a selection letter on the top line.          
                                                                                
> A-THREAD DETAIL B-LOCK COUNTS C-LOCK WAITS     *-LOCKS OWNED  E-GLOBAL LOCKS  
> F-CURRENT SQL   G-SQL COUNTS  H-DISTRIBUTED    I-BUFFER POOL  J-GROUP BP      
> K-PACKAGES      L-RES LIMIT   M-PARALLEL TASKS N-UTILITY      O-OBJECTS       
> P-CANCEL THREAD Q-DB2 CONSOLE R-DSN ACTIVITY   S-APPL TRACE   T-ENCLAVE       
> U-LONG NAMES                  W-ACCEL ACTIVITY                                
=============================================================================== 
>                       LOCKS/CLAIMS OWNED BY A THREAD                          
 PLAN                                                                           
+ Thread:  Plan=DISTSERV  Connid=SERVER   Corrid=db2jcc_appli Authid=HONG       
+ Dist  :  Type=DATABASE ACCESS, Luwid=G9414CE5.G536.CBA4ADE06FAA=591           
+ Location  :  9.65.76.229,Host Name=sig-9-65-76-229.mts.ibm.com                
 own                                                                            
+                         Lock Ownership Information                            
+      Percent NUMLKUS       = .00        Total  Locks Owned        = 3         
+      Total Catalog Locks   = 0          Pageset and Dataset Locks = 1         
+      Catalog Pageset Locks = 0          Page/Row Locks            = 0         
+      Catalog Page/Row Locks= 0          Directory and Other Locks = 2         
+      Bind ACQUIRE option   = ALLOCATE   Bind RELEASE option       = COMMIT    
+      ISOLATION option      = Cursor Stability                                 
+                                                                               
+      Type    Level        Resource                             Number         
+      ----    -----        ------------------------------------ ------         
+      PSET       IS        DB=HONGLTBD PS=HONGLTBS                   1         
+      TABL       IS        DB=HONGLTBD PS=HONGLTBS                   1         
+      SKPT        S        N/A                                       1         
+                                                                ------         
+                                                        Total =      3         
+                         Claim Information                                     
+      Type       Class     Resource                                            
+      -------    -----     -------------------------------------------         
+      IX         CS        DB=DSNDB06        PS=DSNDTX05                       
+      TS         CS        DB=DSNDB06        PS=SYSTSTAB                       
+      IX         CS        DB=HONGLTBD       PS=HONGLTBX                       
+      TS         CS        DB=HONGLTBD       PS=HONGLTBS                       
===============================================================================

Highlighting

OMEGAMON® for Db2 PE highlights some fields in this panel to draw your attention to their current status:

Table 1. Highlighted fields in Locks/Claims Owned by a Thread panel
Field	Exception	Reason
Plan	ARCM	Backout requires an archive tape log mount.
	ETIM	This thread has reached the Elapsed Time threshold value.
	GETP	The Getpage to Read I/O ratio indicates poor read efficiency.
	INDB	The thread is indoubt and terminated.
	PREF	The Sequential Prefetch rate is high.
	RIO	The synchronous Read I/O rate is high.
	PGUP	The rate for system page updates is high.
	LKUS	The ratio of data locks owned to NUMLKUS is high.

Navigation

For additional information about

related topics, select one of the options on 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).

Connection Type

Batch

The MVS™ jobname and ASID.

Note: For threads from remote Db2, the MVS job name is N/A.

CICS®

The CICS jobname, task name, task number, terminal ID, and thread type. The thread type is:

Pool: The thread in use is a pool thread.
Entry: The thread in use is a nonprotected entry thread.
Prot: The thread in use is a protected thread. Protected threads are defined in an RCT entry definition using the THRDS operand.

IMS

The IMS region number, transaction name, region name, and terminal ID (LTERM).

RRSAF

The MVS job name and ASID.

Note: For threads from remote Db2, the MVS job name is N/A.

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.

TSO

The TSO user ID and region ASID.

Utility

No additional information.

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.

Package identifier: This information identifies the package to which the information in this panel applies.

Package: The Db2 package name of the active thread. Up to 18 characters of the package name are returned.
Collection: The package collection identifier. This field is displayed only if a package is being used.

Distributed thread identifier: The following fields are displayed when 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.

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 such as the following:


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 attachment identifier displays the user thread, if any, that is served by the system thread.

Lock ownership information: The following fields are displayed when the thread has a distributed relationship with a remote Db2 subsystem.

Percent NUMLKUS

The current percentage of NUMLKUS reached by the thread. NUMLKUS is an installation parameter in DSNZPARM that specifies the maximum number of page, row, LOB, or XML locks that can be held by a single process at any one time. It includes locks for both, the Db2 catalog and directory and for user data.

Total Locks Owned

The total number of all locks owned by the thread. This includes all lock types.

Total Catalog Locks

Total of all catalog locks owned. This number includes any locks owned on a page or tablespace in database DSNDB06. This is the total of the catalog pageset and page locks.

Pageset and Dataset Locks

The total number of pageset locks held. This excludes pageset locks in which the lock object is a pageset owned by the catalog database or the directory database. Lock types included are PSET and DSET.

Catalog Pageset Locks

The total number of pageset locks in which the object of the lock is a pageset owned by the catalog (DSNDB06) database (PSET).

Page/Row Locks

Total number of page, row, LOB, and XML locks owned by the thread. It includes DPAG, IPAG, ROW, LOB, and XML locks. It includes any of the listed locks in which the lock object is owned by the catalog database or the directory database.

Catalog Page/Row Locks

The total number of page and row locks on the catalog (DSNDB06) database. This includes data page (DPAG), index page (IPAG), and row (ROW) locks.

Directory and Other Locks

The total number of directory and other locks owned by the thread. Directory locks are issued against resources contained in the DSNDB01 directory database.

Bind ACQUIRE Option

The ACQUIRE option specified at BIND time: ALLOCATE or USE. This allows you to control when the allocation of tablespace locks is to occur. If ACQUIRE(USE) is specified, tablespace locks will be acquired when the resource is used by the application. When ACQUIRE(ALLOCATE) is specified, all tablespace locks will be obtained at plan allocation time. Dynamic SQL users (QMF, SPUFI) always execute with the ACQUIRE(USE) option.

Bind RELEASE Option (Static) (QW01243F)

The RELEASE option specified at BIND time: COMMIT or DEALLOCATE.

The RELEASE option controls when tablespace and SKCT locks are freed. RELEASE(COMMIT) causes these locks to be released at each commit point. RELEASE(DEALLOCATE) causes these locks to be freed at application termination. Page locks are always released at commit time, regardless of the release option.

ISOLATION Option

The ISOLATION option specifies when the plan is bound.

The isolation parameter allows you to control the data consistency in the pages that the plan accesses. It has an effect on the number of page or row locks held concurrently by an application. The available options are:

Cursor Stability (CS)

Causes a page lock acquired for read processing to be released when the application subsequently accesses data contained on another data page. (Page locks acquired as a result of update activity are always retained until commit.)

Read Stability (RS)

Is similar to Repeatable Read but this isolation option allows to insert new rows or update rows that did not satisfy the original search condition of the application.

Repeatable Read (RR)

Causes all application locks obtained for read processing to be retained until application commit time.

RR with X-lock

Means Repeatable Read with X-lock.

RS with X-lock

Means Read Stability with X-lock.

Uncommitted Read (UR)

Causes data to be read without acquiring locks. This can result in accessing data that has been updated but not yet committed. It applies only to read-only operations: SELECT, SELECT INTO, or FETCH from a read-only result table. Use this isolation level only when:

Data consistency is not necessary or inconsistencies already exist
Errors cannot occur with its use, such as with a reference table that is rarely updated

Type

The lock type owned. Every lock type owned by the thread will be displayed.

Level

The lock levels of the various lock types owned. All lock levels owned within a lock type will be listed. Lock levels can occur repetitively for a single lock type due to the different resources owned by the locks.

Resource

The resource that is the object of the lock. The content of this field is dependent on lock type. For data page (DPAG) and index page (IPAG) locks, the resource does not contain the actual data page number that is locked; it lists the database and pageset owning the data or index page lock. The Number field then displays how many data or index page locks exist within the resource (pageset) listed.

Number

The number of locks meeting the type, level, and resource description of the lock.

Claim information:

Type

The type of object being claimed. Possible object types are:

TS: Tablespace
TS LPRT: Tablespace logical partition
TS PART: Data Partition
IX: Indexspace
IX LPRT: Indexspace logical partition
IX PART: Index Partition

Class

The claim class. Possible claim classes are:

CS: Cursor Stability
RR: Repeatable Read
WRITE: Write access

Resource

The name of the object which is locked or claimed. The resource name can include the database name, the pageset name, the partition number, the page number, the record ID, the collection ID, the package name, or the plan name.

Note: DB=database name and PS=pageset name can be represented as a DBID=identifier or PSID=identifier correspondingly. DBID represents the decimal identifier of the database. PSID represents the decimal identifier of the table space or the index space.