Local Db2 Lock Conflict Attributes

Use Local Db2 Lock Conflict Group attributes to create situations that monitor Db2 database lock conflicts.

Authorization ID The primary authorization ID from connection or signon, that identifies the execution privileges an application has to a Db2 object or service. It is an alphanumeric text string with a maximum of 8 characters; for example, TRAN5.

Authorization ID (Unicode) The primary authorization ID from connection or signon, that identifies the execution privileges an application has to a Db2 object or service. Valid format is alphanumeric with a maximum of 8 characters; for example, TRAN5.

Begin Time Beginning store clock value (STCK) for the period covered by this accounting record. You can determine the elapsed time of the application by subtracting this field from QWACESC (ending store clock value). Threads that do not terminate (such as CICS primed threads and ims wait-for-input message regions) can have an ending clock value that includes the time the thread was inactive and waiting to perform work.

Local Db2 Lock Conflict Attributes Identifies the connection of an application to a Db2 system.

It is an alphanumeric text string, with a maximum length of eight characters.

Local Db2 Lock Conflict Attributes The type of connection associated with the thread.

It can be:
Value Description
BATCH Batch job
CICS CICS attach
DLIBATCH DL/I batch
Distributed Distributed Database Access thread
IMS_BMP IMS attach BMP
IMS_CTL IMS control region
IMS_MPP IMS attach MPP
IMS_TBMP IMS transaction BMP
DistAllied Distributed Allied thread
RRSAF Db2 Resource Recovery Services attachment facility
SYSTEM System directed access
TSO TSO foreground and background
UNKNOWN Unrecognizable connection type
UTILITY Db2 Utility

Correlation ID The correlation between an application and the connection to a Db2 system. It is an alphanumeric text string, with a maximum of 12 characters; for example, DLKEX212.

Db2 ID The name of a Db2 subsystem.

Display Elapsed Time The total amount of elapsed time since thread creation or Db2 sign-on.

IDNAME An internal attribute used for navigation, it contains either the Data Sharing Group Name or the DB2ID.

It is an alphanumeric text string with a maximum of 8 characters; for example TDDB242G.

Lock Elapsed Time The amount of time (in seconds) a waiter has been waiting for the resource.

Lock Level Identifies the lock resource usage.

Valid values are:
Value Description
IS Intent share
IX Intent exclusive
NSU Non-shared Update
S Share
SIX Share intent exclusive
U Update
UNS Unprotected shared
X Exclusive

Lock Resource The resource a lock owner currently has locked or the resource that caused Db2 to suspend a lock request. It is an alphanumeric text string, with a maximum of 50 characters; for example, DB=TBLBLK2 PS=PAGELK2.

Lock Status The status of a job holding or waiting on a lock: If the status is OWN with a Db2 subsystem name, the lock is owned by another Db2 subsystem in the Data Sharing Group (DSG); for example, if lock status is OWN-DB2A, the lock is owned by Db2 subsystem DB2A.

Lock Token Lock Token.

Lock Type The lock type of the lock request.

Valid values are:
Table 1. Lock types
Lock type Description
ACSC The Accelerator Services commands (ACSC) lock.
ALBP The Alter buffer pool (ALBP) lock indicates a lock on a buffer pool during execution of an ALTER BUFFERPOOL command.
BIND The BIND lock indicates an autobind or remote bind lock.
BMBA The Buffer manager SCA MBA (BMBA) L-lock.

The Buffer Manager (BM) gets this lock when it needs to read, insert, or update a multiple buffer pool (MBA) record in a Shared Communications Area (SCA).

(BMC_MBAO or BMC_MBAR)

BPPS The Buffer Manager Pageset (BPPS) RR (repeatable read) P-lock:
  • BP = buffer pool ID
  • DB = database name
  • PS = pageset name
CCAT The CATMAINT convert catalog (CCAT) lock is acquired when catalog conversion is performed.
CDBL The Compress dictionary build (CDBL) lock.
CDIR The CATMAINT convert directory (CDIR) lock is acquired when directory conversion is performed.
CDRN The Cursor Stability drain (CDRN) lock is acquired to drain all CS read access to an object:
  • DB = database name
  • PS = pageset name
  • PT = partition
CMDS The DB2® Command Serialization (CMDS) lock.
CMIG The CATMAINT migration (CMIG) lock is acquired when catalog migration is performed.
COLL The Collection (COLL) lock
DBDL The DBD load (DBDL) lock is the database descriptor load lock.
DBEX The Database exception (DBEX) lock indicates a lock on a "Logical page list" (LPL) or "Group buffer pool recovery pending" (GRECP) database exception status.

This lock is only used in a data sharing environment.

DBXU The DB exception update lock is used for updating the database exception status.
DGTT The DGTT URID lock is acquired to protect segments that belong to a Declared Global Temporary Table (DGTT). These segments are deallocated during Commit 1 by logging them and serializing them using the Unit of Recovery ID (URID) lock.
DPAG The Db2 page (DPAG) lock in a tablespace. When programs read data or update data, they acquire a page lock containing the data.
  • DB = database name
  • PS = pageset name
  • PG = page
DSET The partitioned lock.

A partitioned tablespace contains one or more partitions (up to 64). It is created when you create a table space using the SQL CREATE TABLESPACE statement with the NUMPARTS parameter.

Only one table can be stored on a partitioned tablespace. Each partition contains one part of a table. The partitioned lock only locks the partition with the data that is referenced.

  • DB = database name
  • PS = pageset name
  • PT = partition number
DTBS The Database lock indicates a lock on the database.
  • DB = database name
  • PS = pageset name
  • PT = partition number
GRBP The Group buffer pool (GRBP) start/stop lock.

BP=buffer pool ID

HASH The Hash anchor (HASH) lock.
  • DB = database name
  • PS = pageset name
  • PG = page
HPSP The Header Page (HP) Bucket or Stored Procedure (SP) Command lock.
IEOF The Index end of file (IEOF) lock is acquired at the index end of file.
  • DB = database name
  • PS = pageset name
  • PT = partition
IPAG The Index page (IPAG) lock in an index space. When application programs read or update data, they acquire a lock on the page containing the index when indexing is used.
  • DB = database name
  • PS = pageset name
  • PG = page
IXKY The Index key (IXKY) lock.
LBLK The Large object (LOB) lock.
LPLR The Logical page list recovery (LPLR) lock.
MDEL The Mass delete (MDEL) lock is acquired when doing a mass delete from a table (for example, when you DELETE FROM a table) within a segmented tablespace.

It is used to prevent another user from reusing freed segments before a delete operation is committed.

  • DB = database name
  • PS = pageset name
PALK The Partition lock.
  • DB = database name
  • PS = pageset name
  • PT = partition
PBPC The Group BP level castout (PBPC) P-lock.

A physical lock acquired when a castout of a group buffer pool occurs. Castout is the process of writing pages in the group buffer pool out to DASD.

This lock is only used in a data sharing environment.

PCDB The DDF CDB P-lock.

A Distributed Data Facility communication database physical lock.

This lock is only used in a data sharing environment.

PDBD The DBD P-lock is a database descriptor physical lock.

This lock is only used in a data sharing environment.

PDSO The Pageset or partitioned pageset open lock.

If the data set supporting the tablespace that is referenced by the application is not opened, the program will acquire a lock to open the data set. The data set will stay open if CLOSE=NO is defined in the SQL statement creating the tablespace.

  • DB = database name
  • PS = pageset name
PITR The Index manager tree (PITR) is a physical lock (P-lock).

This lock is only used in a data sharing environment.

  • DB = database name
  • PS = pageset name
  • PT = partition
PPAG The Page P-lock is a physical lock on a page.

This lock is only used in a data sharing environment.

PPSC The Pageset/partition level castout physical lock (P-lock).

This lock is only used in a data sharing environment.

PPSP The Pageset/partition physical lock (P-lock).

This lock is only used in a data sharing environment.

PRLF The Resource Limit Facility (RLF) physical lock (P-lock).

This lock is only used in a data sharing environment.

PSET The Pageset (PSET) lock can be a tablespace or indexspace.

A pageset containing Db2 tables is a tablespace. A pageset containing Db2 index structure is an indexspace.

A pageset can be simple or partitioned. This lock type is for the simple pageset only.

  • DB = database name
  • PS = pageset name
PSPI The Pageset piece (PSPI) lock.

A pageset is a collection of pageset pieces. Each pageset piece is a separate VSAM data set.

A simple pageset contains from 1 to 32 pieces. Each piece of a simple pageset is limited to 2 GB. Whenever a simple pageset piece reaches this size, another piece is allocated and the pageset grows.

This is a lock on the expanded pageset piece.

RDBD The Repair DBD (RDBD) lock is acquired when REPAIR DBD REBUILD is running (test/ diagnose).
RDRN The Repeatable Read drain (RDRN) lock is acquired to drain all RR access to an object.
  • DB = database name
  • PS = pageset name
  • PT = partition
RGDA The Retry Getpage During Abort (RGDA) lock.
ROW The Row lock indicates a lock on a row.
RSTR The Shared Communications Area (SCA) restart (RSTR) lock indicates a lock on SCA access for restart/redo information.

(BMC-RSTP)

SDBA The Start/stop lock on DBA (SDBA) table indicates a lock on the table, tablespace, or database when a CREATE/DROP is processed against these objects.
  • DB = database name
  • PS = pageset name
SENV The System environment (SYSENV) serialization lock.
SKCT The Skeleton cursor table (SKCT) lock indicates a lock on the application plan.

PLAN=plan name

SKPT The Skeleton package table (SKPT) lock indicates a lock on the application package.

TOKEN= the consistency token (CONTOKEN) column from SYSIBM.SYSPACKAGE.

SPRC The System level point in time (PIT) recovery lock.

SYS_PITR

SREC The Log range lock.

Db2 writes a record in the log range tablespace (SYSLGRNG) every time a tablespace is opened and updated, and updates SYSLGRNG whenever that tablespace is closed.

The record contains the opening and/or closing log RBA (relative byte address) for the tablespace. When Db2 writes to SYSLGRNG, the program acquires a lock on the tablespace with updates.

  • DB = database name
  • TS = tablespace name
TABL The Table (TABL) lock on the table which resides in a segmented tablespace.
  • DBID = DBid
  • TABL = Tableid
UIDA The Util I/O Damage Assessment lock.
UNDT The Undetermined (UNDT) lock indicates that this lock cannot be determined because it is not part of the other listed lock types.

Resource ID (in hexadecimal).

UNKN The Unknown (UNKN) lock indicates the resource does not exist.
UTEX The Utility exclusive execution (UTEX) lock.

UTEXEC

UTID The Utility identifier (UTID) lock.

UID=utility id

UTOB The Utility object (UTOB) lock.
  • DB = database name
  • PS = pageset name
  • PT = partition
UTSE The Utility serialization (UTSE) lock is required when running utility jobs.

UTSERIAL

WDRN The Write drain (WDRN) lock is acquired to drain all write access to an object.
  • DB = database name
  • PS = pageset name
  • PT = partition
XMLK The XML lock.

LUWID The logical unit of work ID (LUWID) for a thread.

MVS System An ID for the MVS System Management Facility (SMF). It is an alphanumeric text string with a maximum of 4 characters; for example SP11.

Originating System ID The managed system name of the agent. It is an alphanumeric text string, with a maximum of 32 characters; for example, DB91:SYS1:DB2.

Plan Name The name of an application plan that Db2 produces during the bind process and uses for processing SQL statements during execution. It is an alphanumeric text string with a maximum of 8 characters. For example PLANLCK2 is the name of an application plan. *SYSTEM* indicates Db2 subsystem-generated threads that own or are waiting on a lock.

Resource Name 1 (Unicode) The resource name of type defined by type code.

Resource Name 2 (Unicode) The resource name of type defined by type code.

Resource Name 3 The resource name of type defined by type code.

Resource Name 4 The resource name of type defined by type code.

Resource Type 1 This value indicates what is contained in Resource Name 1.

Valid values are:
Value Description
BP Buffer Pool
CO Collection name
DB Hash Database
HC Class
PL Plan name
UT Utility ID

Resource Type 2 This value indicates what is contained in Resource Name 2.

Valid values are:
Value Description
PK Package
PS Page Set (Tablespace)

Resource Type 3 This value indicates what is contained in Resource Name 3.

Resource Type 4 This value indicates what is contained in Resource Name 4.

Valid values are:
Value Description
PT Partition

SUBSYS The IRLM subsystem name.

Thread Status The current status of a thread.

Valid values are:

Status
Description
NOT-AVAIL
The thread is not available.
IN-ABORT
The thread is in abort processing.
IN-ACCEL
The thread is executing on IBM Db2 Analytics Accelerator for z/OS.
IN-AUTO-PROC
The thread is processing an autonomous procedure.
IN-BIND-DYNM
The thread is in dynamic bind processing.
IN-BIND-STAT
The thread is in static bind processing.
IN-COMMAND
Command threads display this status when they are active in Db2 and executing within the Db2 command processor. (This type of thread always has a blank plan name.)
IN-COMMIT
The thread is in Commit processing (applies only to threads that originate from an attachment that does not use two-phase-commit protocol).
IN-COMT-PHS1
The thread is in Commit phase 1 processing.
IN-COMT-PHS2
The thread is in Commit phase 2 processing.
IN-CRTE-THRD
The thread is in Create Thread processing.
IN-DB2
The thread is executing in Db2. A more descriptive status could not be determined.
INDOUBT
The thread is in doubt.
IN-SIGNON
The thread is in signon processing. This status applies only to threads originating from CICS or IMS attachments.
IN-SQL-CALL
The thread is processing an SQL call.
IN-SQL-SORT
The thread is executing an SQL call and is doing the sort processing required to satisfy the call's request.
IN-STOR-PROC
The thread is currently running in a stored procedure.
IN-TERM-THRD
The thread is in termination as a result of allied task termination. This status corresponds to DB2 DISPLAY THREAD=D status.
IN-TRIGGER
The thread is currently running in a trigger.
IN-USER-FUNC
The thread is currently running a user-defined function.
NOT-IN-DB2
The thread is not currently executing in Db2.
SP/UDF-INACT
The thread is trying but not able to run in a stored procedure or user-defined function.
SWAPPED-OUT
The thread is not currently executing in Db2. The thread originating address space is swapped out.
WAIT-ARCHIVE
The thread is waiting for an archive log tape mount necessary during thread abort processing.
WAIT-ARCREAD
The thread is currently waiting for read of archive log from tape.
WAIT-ASYNCRD
The thread is currently waiting for completion of a read I/O that is being done under a thread other than this one (for example, sequential or List Prefetch).
WAIT-ASYNCWR
The thread is currently waiting for completion of Write I/O that is being done under a thread other than this one (for example, Deferred Writes).
WAIT-CONVLIM
The distributed thread is in a synchronous wait because the conversation limit has been reached for its designated logmode.
WAIT-CTHREAD
The thread is queued in Create Thread processing because Db2 reached the CTHREAD value. This status corresponds to DB2 DISPLAY THREAD=QD status.
WAIT-GLBLOCK
The thread is currently waiting for either:
  • Inter-system communication within the data sharing group to determine if there is lock contention.
  • A lock held by another subsystem in the data sharing group.
WAIT-LOCK
The thread is waiting for a lock.
WAIT-MSGSEND
The thread is waiting because of inter-system message sending contention.
WAIT-REMREQ
The database access thread is in a synchronous wait (waiting for a response or a request from the originating Db2 subsystem).
WAIT-REMSQL
The distributed allied thread is in a synchronous wait (waiting for a response from the remote Db2 subsystem being accessed).
WAIT-REUSE
The thread is not currently in use and is waiting to be reused. This status applies only to CICS and IMS threads.
WAIT-SERVICE
The thread is currently waiting for completion of a Db2 service. Types of Db2 services include: open/close of a dataset, DFHSM recall of a dataset, SYSLGRNG update or define/extend/delete of a dataset, rollback, and Commit phase 2 for read only threads.
WAIT-SP-STOP
The thread is queued waiting for a stopped stored procedure.
WAIT-TERM-TH
The thread is queued and waiting for thread termination as a result of allied task termination. This status corresponds to DB2 DISPLAY THREAD=QD status.
UTIL-STOP
The Db2 utility was started but not completed because of abnormal termination.
The following status are only available when class 2/3 traces are on:
WAIT-ACCEL
The thread is currently waiting for claimers to be released after acquiring drain lock.
WAIT-CLAIMER
The thread is currently waiting for claimers to be released after acquiring drain lock.
WAIT-CL3LOCK
The thread is currently waiting for the completion of an identify call to the IRLM.
WAIT-COMMIT
The thread is waiting for the FORCE-AT-COMMIT event to complete.
WAIT-DRNLOCK
The thread is currently waiting to acquire drain lock.
WAIT LOB
The thread is currently waiting for TCP/IP to materialize a LOB.
WAIT-LOCKLAT
The thread is waiting for a LOCK-I/O-LATCH.
WAIT-LOCKPIP
The thread is currently waiting for a PIPE suspend.
WAIT-LOCKPQS
The thread is currently suspended for parallel task synchronization.
WAIT-LOGQSCE
The thread is currently suspended because of an ARCHIVE LOG MODE(QUIESCE) command.
WAIT-PGLATCH
The thread is currently waiting for page latch.
WAIT-SP-SCHD
The thread is waiting for a TCB to become available in the stored procedures address space to schedule a stored procedure.
WAIT-SWITCH
The thread is currently waiting for the completion of a synchronous execution switch.
WAIT-SYNC-IO
The thread is currently waiting for completion of a synchronous Read I/O or Write I/O.
Note: This status can indicate that a Db2 resource is not large enough. You can use the OMEGAMON XE for Db2 PE resource manager or object analysis displays to further isolate the problem.

Thread Token The thread token uniquely identifies a specific thread.

Local Db2 Lock Conflict Attributes The type of thread.

It can be:
Value Description
BATCH Batch job
CICS CICS attach
DLIBATCH DL/I batch
Distributed Distributed Database Access thread
IMS_BMP IMS attach BMP
IMS_CTL IMS control region
IMS_MPP IMS attach MPP
IMS_TBMP IMS transaction BMP
DistAllied Distributed Allied thread
RRSAF Db2 Resource Recovery Services attachment facility
SYSTEM System directed access
TSO TSO foreground and background
UNKNOWN Unrecognizable connection type
UTILITY Db2 Utility

Time Stamp Represents the end of data collection or the end of an interval, and is the local time where the system is running.

Uniqueness Value The instance number: When concatenated with the fully qualified network name, it uniquely identifies a distributed thread (Field name: QWHSLUUV).

Uniqueness Value2 The instance number: When concatenated with the fully qualified network name, it uniquely identifies a distributed thread (Field name: the last 2 bytes of QWHSLUUV).