BTSUSPTR Lock Suspension Details Report

The BTSUSPTR report provides detailed information about lock suspensions including the reason the lock occurred, whether the lock is global or local, and the state of the lock.
caidb219
The BTSUSPTR report provides detailed information about lock suspensions including the reason the lock occurred, whether the lock is global or local, and the state of the lock.
Report characteristics:
  • Request type: Event Trace
  • Report format option: Column formatted
  • Records displayed: (045) LOCK-SUSPEND (performance trace class 6)
MM/DD/YY 14:29:15 PAGE 1 REQUEST: BTSUSPTR DB2 SYS: SSID FIRST RECORD YY/MM/DD 15:39:25 LOCK SUSPENSION DETAILS NORMAL resume at MM/DD/YY 15:39:25 after waiting 0.148 seconds Reason for lock suspension INTER-SYSTEM COMMUNICATION Scope GLOBAL Glbl cont type XES Lock type LOGICAL Task ID - Auth USERxx Corr USERxx Plan RADIO23 Conn BATCH Resource - Name USERxx .JBGTSPC2 Type PAGESET Mode IS Dur COMMIT NORMAL resume at MM/DD/YY 15:39:25 after waiting 0.088 seconds Reason for lock suspension INTER-SYSTEM COMMUNICATION Scope GLOBAL Glbl cont type XES Lock type LOGICAL Task ID - Auth USERxx Corr USERxx Plan RADIO23 Conn BATCH Resource - Name JBG.JBGTBL01 Type TABLE Mode IS Dur COMMIT TIMEOUT resume at MM/DD/YY 15:40:43 after waiting 75.911 seconds Reason for lock suspension INTER-SYSTEM COMMUNICATION Scope GLOBAL Glbl cont type FALSE Lock type LOGICAL Task ID - Auth USERxx Corr USERxx Plan RADIO23 Conn BATCH Resource - Name JBG.JBGTBL01 .PAGE X'000003' Type DATAPAGE Mode U Dur MANUAL
The BTSUSPTR report displays the following information:
  • Reason for Lock Suspension
    A categorization of suspensions. The most common type is Local Resource Contention which applies to DB2 databases. Valid values are as follows:
    • Global resource contention
    • IRML queued request
    • Inter-system communication
    • IRLM latch contention
    • Local resource contention
    • Notify message sent
    • RH to PTB communication request
  • Scope
    Indicates whether the lock is a global lock or a local lock.
  • Glbl cont type
    If the reason for lock suspension is global contention, this field indicates which of four possible global lock contentions apply. These include the following types:
    • IRLM
      IRLM global resource contention.
    • XES
      XES global resource contention.
    • FALSE
      False contention.
    • N/A
      Field does not apply to non-global resource contention.
  • Lock Type
    Indicates whether the lock is a physical lock (used in data sharing) or a logical lock (used by transactions).
  • Task ID
    The next four fields identify a task that was suspended.
  • Auth ID
    The DB2 Authorization ID.
  • Corr ID
    The thread's correlation ID (first 8 bytes).
  • Plan
    The plan name. If blank, a DB2 command thread.
  • Conn
    The name of the connection (TSO, BATCH, CICS procname, IMS MPR, DB2CALL).
  • Resource
    Information which shows lock attributes and also identifies the locked resource.
  • Name
    Identifies a resource involved in a lock suspension. Database and page set names are translated using the OBID table. If the table is out of sync with the data, DBIDs and OBIDs are displayed.
  • Type
    One of the following lock types:
    • DATAPAGE
      A page lock in a tablespace.
    • DATABASE
      A lock on a database descriptor.
    • PAGESET
      A lock on a non-partitioned tablespace or indexspace.
    • DATASET
      A lock on one of the partitions of a partitioned tablespace or indexspace.
    • SKCT
      A lock on an application plan's skeleton cursor table.
    • INDXPAGE
      A page lock in an indexspace.
    • DS OPEN
      A lock used to serialize data set opens and closes.
    • UTIL DMG
      A lock used for utility I/O damage assessment.
    • PSET PC
      A simple page set consists of 1 to 32 VSAM data sets called page set pieces. When a page set piece exceeds the page set piece size limit, another piece is allocated. A page set piece lock is issued on the pieces while the expansion occurs.
    • DBA TABL
      A lock on a table, tablespace or database which is being processed by a CREATE, DROP or ALTER statement.
    • RETRY
      A retry during an abort.
    • 32K DESP
      32K desperation pool lock. These occur as an overflow out of the other buffer pools and imply that the buffers are undersized.
    • SYSLGRNG
      A lock obtained while writing to the SYSLGRNG tablespace.
    • UTILITY
      A lock used by utility jobs for serialization.
    • MASS DEL
      A mass delete lock occurs when a delete is used without a WHERE clause. All rows in the table are deleted
    • TABLE
      A lock on a table in a segmented tablespace.
    • HSH ANCH
      A hash anchor lock is used when accessing DB2 catalog or directory tables.
    • SKPT
      A lock on an application package's skeleton package table.
    • COLLECT
      A lock on an application package collection.
    • AUTOBIND
      A lock used to serialize local autobinds of packages and remote binds and rebinds of packages.
    • CS DRAIN
      Cursor stability drain locks are used by DB2 commands and utilities to serialize CS drains.
    • RR DRAIN
      Repeatable read drain locks are used by DB2 commands and utilities to serialize RR drains.
    • WR DRAIN
      Write drain locks are used by DB2 commands and utilities to serialize write drains.
    • AL BPOOL
      Alter buffer pool locks are used to serialize ALTER BUFFERPOOL commands.
    • ROW
      Row lock.
    • INDEX EOF
      Index End-of-File lock.
    • SS GPOOL
      Group Buffer pool Start/Stop lock.
    • DB EX LG
      Database Exception LPL/GRECP lock.
    • UTILEXC
      Utility exclusive execution lock.
    • SCA REDO
      SCA access for restart/redo information lock.
    • GPDB EXU
      Database Exception Update lock.
    • UTIL ID
      Utility UID lock.
    • REPR DBD
      REPAIR DBD TEST/DIAGNOSE lock.
    • CATM MIG
      CATMAINT Concert Catalog lock.
    • CATM CVC
      CATMAINT Concert Catalog lock.
    • CATMCVD
      CATMAINT Directory lock.
    • P/P PLCK
      Page set or Partition P-lock.
    • P/P CAST
      Page set or Partition castout P-lock.
    • GBP PLCK
      Group Buffer pool level castout P-lock.
    • INDX PLK
      Index Manager Tree P-lock.
    • RLF PLCK
      Resource Limit Facility P-lock.
    • DBD PLCK
      Database Descriptor P-lock.
    • DDF PLCK
      Distributed Data Facility CDB P-lock.
    • PAGE PLK
      Page P-lock.
  • Mode
    Also known as the lock state. This field shows the type of access permitted to concurrent applications that want to process the same resource.
    • ANY
      A mode used for IRLM CHANGE or UNLOCK requests.
    • US
      Unprotected Share, an index tree is locked and lock escalation occurs.
    • IS
      Intent Share, a tablespace lock in which the lock owner might read data, but not update it. Other applications can both read and update data. IS tablespace locks are only used with page locks.
    • IX
      Intent Exclusive, a tablespace lock in which both the lock owner and concurrent applications processes can read and update the data. When the lock owner reads a specific page, a S or U page lock is obtained on that page. Other applications can read and update other pages, but not the locked page. When the owner updates data, an X lock is obtained on the updated page and other applications cannot even read the locked page. IX locks are only used with page locks.
    • S
      Share, used for both page and tablespace locks. The owner and concurrent applications can read the data, but might not update it.
    • U
      Update, used for both page and tablespace locks. The owner and concurrent applications can read data and intend to change it. Only one application can hold a U lock on the locked resource (be it a page, table or tablespace). When the application is ready to change the data, it must wait until there are no S locks on the resource, at which time the lock will be promoted to an X lock.
    • SIX
      Shared Intent Exclusive, a tablespace lock in which the lock owner can read and update the data. Other application processes can only read data. This lock is only used with page locks. When the lock owner updates data, an X lock is obtained on the updated page and other applications cannot read the locked page.
    • NSU
      Nonshare Update Lock, an update lock used when an index tree is updated without lock escalation.
    • X
      Exclusive, used for both page and tablespace locks. The lock owner can read or update data, no one else can do so.
  • Dur
    Indicates when a lock is released. Valid values are as follows:
    • ALLOCnn
      When the resource is deallocated.
    • COMMIT
      When the lock holder issues a COMMIT.
    • COMT+1
      Locks needed to maintain the position of a cursor opened “with hold,” which are held past commit.
    • MANUAL
      For plans that were bound with isolation level CS, DB2 does not wait until the next commit point to release locks on pages that were not updated.
    • MANL+1
      Temporary change of consistency level from CS to RR during bind and DDL (RDS changes back internally).
    • PLAN
      The lock is released when the plan is deallocated.
    • FREE
      Duration to free all.