Lock Suspension Report

The Lock Suspension report summarizes all Lock Suspension activities across a specified time period.

The suspensions are reported by any combination of up to three OMEGAMON for Db2® Performance Expert identifiers. The report summarizes the Lock Suspension activities of:

An IRLM request (except when the resource type is a drain lock).
An IRLM request where the resource type is a drain lock.
A drain request where the claim count is not zero.
This suspension occurs when the agent making the drain request has to wait for the claim count on the particular resource to become zero.
A suspension of a page latch request.
This suspension occurs when the agent making the page latch request has to wait for a page which is currently being held by another agent.

The Lock Suspension report is produced if level SUSPENSION is specified in the REPORT subcommand. The ORDER subcommand specifies by which OMEGAMON for Db2 Performance Expert identifiers the report is to be sorted.

Optionally, the SPREADSHEETDD subcommand option can be used to create a data set with Lock Suspension data that can be imported in spreadsheet programs for individual analyses. Refer to Using Lock Suspension Data with Spreadsheets for more details.

Every suspension results in a normal resume or a lockout (deadlock or timeout), or is canceled (in the case of page latch suspensions). In any case, and if the suspension delay is unacceptable, review the plans and associated tables and indexes.

Layout of a Suspension Report shows the layout of a suspension report. The letter x is a placeholder marking the maximum size of a field. See Lock Suspension Report for an example of a suspension report.

The report presents data summarized by OMEGAMON for Db2 Performance Expert identifiers. The report can be sorted by up to three identifiers. For group-scope reports, the member name is added implicitly as an additional identifier and sort criterion.

Layout of a Suspension Report

This is the layout of a suspension report.

   LOCATION: xxxxxxxxxxxxxxxx         OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0)                   PAGE: l-n
      GROUP: xxxxxxxx                             LOCKING REPORT - SUSPENSION                 REQUESTED FROM: mm/dd/yy hh:mm:ss.nn
     MEMBER: xxxxxxxx                                                                                     TO: mm/dd/yy hh:mm:ss.nn
  SUBSYSTEM: xxxx                                         ORDER: xxxxxx                        INTERVAL FROM: mm/dd/yy hh:mm:ss.nn
DB2 VERSION: Vn Rn                                        SCOPE: xxxxxx                                   TO: mm/dd/yy hh:mm:ss.nn

IDENT1xx
 IDENT2xx                                                     --SUSPEND REASONS-- ---------  R E S U M E    R E A S O N S  ---------
  IDENT3xx         --- L O C K   R E S O U R C E --- TOTAL    LOCAL  GLOB.  S.NFY ---- NORMAL ----  TIMEOUT/CANCEL  --- DEADLOCK ---
   MEMBER          TYPE      NAME                    SUSPENDS LATCH  IRLMQ  OTHER NMBR         AET NMBR         AET NMBR         AET
------------------ --------- ----------------------- -------- -----  -----  ----- ---- ----------- ---- ----------- ---- -----------
xxxxxxxxxxxxxxxx   xxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxx nnnnnnnn nnnnn  nnnnn  nnnnn nnnn ssss.nnnnnn nnnn ssss.nnnnnn nnnn ssss.nnnnnn
 xxxxxxxxxxxxxxxx            xxxxxxxxxxxxxxxxxxxxxxx          nnnnn  nnnnn  nnnnn
  xxxxxxxxxxxxxxxx           xxxxxxxxxxxxxxxxxxxxxxx
   xxxxxxxx                  xxxxxxxxxxxxxxxxxxxxxxx

                   xxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxx nnnnnnnn nnnnn  nnnnn  nnnnn nnnn ssss.nnnnnn nnnn ssss.nnnnnn nnnn ssss.nnnnnn
                             xxxxxxxxxxxxxxxxxxxxxxx          nnnnn  nnnnn  nnnnn
                             xxxxxxxxxxxxxxxxxxxxxxx
                             xxxxxxxxxxxxxxxxxxxxxxx

                   .
                   .
                   .

                   *SUM OF xxxxxxxxxxxxxxxx*         nnnnnnnn nnnnn  nnnnn  nnnnn nnnn ssss.nnnnnn nnnn ssss.nnnnnn nnnn ssss.nnnnnn
                                                              nnnnn  nnnnn  nnnnn
 .
 .
 .

   *GROUP TOTAL*                                     nnnnnnnn nnnnn  nnnnn  nnnnn nnnn ssss.nnnnnn nnnn ssss.nnnnnn nnnn ssss.nnnnnn
                                                              nnnnn  nnnnn  nnnnn
  *SUBTOTAL*                                         nnnnnnnn nnnnn  nnnnn  nnnnn nnnn ssss.nnnnnn nnnn ssss.nnnnnn nnnn ssss.nnnnnn
                                                              nnnnn  nnnnn  nnnnn
 *TOTAL*                                             nnnnnnnn nnnnn  nnnnn  nnnnn nnnn ssss.nnnnnn nnnn ssss.nnnnnn nnnn ssss.nnnnnn
                                                              nnnnn  nnnnn  nnnnn
*GRAND TOTAL*                                        nnnnnnnn nnnnn  nnnnn  nnnnn nnnn ssss.nnnnnn nnnn ssss.nnnnnn nnnn ssss.nnnnnn
                                                              nnnnn  nnnnn  nnnnn

Field description

The header information to this report is described in The Locking Header of Reports and Traces.

IDENT1XX, IDENT2XX, IDENT3XX

These mark the positions where the order criteria are listed and reported.

In group-scope reports, MEMBER is automatically added as the second, third, or fourth identifier.

If you specify DATABASE, PAGESET, or both, in the ORDER option, the database name, page set name, or both names, are removed from the resource name. In this case, 'BLANK' is printed.

MEMBER

Group member name of the Db2 subsystem. This is only printed for member-scope reports.

LOCK RESOURCE TYPE

The type of resource on which the suspended request is made. Valid values are shown in Table 1.

Table 1. Lock Resource Type
Type	Description
ALTERBUF	Alter buffer pool lock
BINDLOCK	Autobind lock and remote bind lock for the serialization of local autobinds or packages, remote binds, and remote rebinds of packages
CATM CAT	CATMAINT convert catalog lock
CATM DIR	CATMAINT convert directory lock
CATM MIG	CATMAINT migration lock
CDB PLCK	DDF communications database P-lock
COLLECT	Collection ID
DATABASE	Locking of the DBD
DATAPAGE	Data page locking
DBALLOC	Start and stop lock on the database allocation table
DBCMD SER	Database command serialization
DBD	DBD load lock
DBD PLCK	DBD P-lock
DRAIN	All types of drain locking
DRAIN CS	Cursor stability drain lock
DRAIN RR	Repeatable read drain lock
DRAIN W	Write drain lock
EXCP UPD	Database group exception update lock
GBP CAST	Group buffer pool level castout P-lock
GBP S/S	Group buffer pool start and stop lock
HASH-ANC	Hash anchor lock
HDRPHASHB	BACKUP SYSTEM or RESTORE SYSTEM utility lock
INDEX KEY	Index Key lock
INDEXEOF	Index end-of-file lock
INDEXPAGE	Index page locking
LOB	Large object
LPL/GREC	Database group exception LPL/GRECP lock
LPLRECVRY	Logical page list recovery
MASSDEL	Mass delete lock
OPENLOCK	Page set or data set open lock
OTHER	All unlisted resource types
P/P CAST	Page set and partition level castout P-lock
P/P PLCK	Page set and partition P-lock
PAGE	Resource involved in page latch suspensions
PAGEPLCK	Page P-lock
PAGESET	Nonpartitioned table spaces and indexes. Drained at the page set level.
PART NSPL	Partitions of partitioned table spaces and indexes using the non-SPL (selective partition locking) scheme.
PART SPL	Partitions of partitioned table spaces and indexes using the SPL (selective partition locking) scheme.
RLF PLCK	RLF P-lock
ROW	Data row locking
RPR_DBD	Repair DBD test and diagnose lock
SCA ACCS	SCA access for restart or redo information
SKCT	Skeleton cursor table locking
SKPT	Skeleton package table Note, if the lock resource type has a value of SKPT the Lock Detail report shows compressed parts of the SKPT resource name as hexadecimal strings in reports and traces. It consists of the following parts: Collection ID, which is compressed (18 bytes) Program name, which is compressed (8 bytes) Consistency token (8 bytes) With the hexadecimal value you can compare values of different locks. See Layout of a Locking Detail report for the lock resource type SKPT for an example of a Locking Detail report for the lock resource type SKPT. SKPT resource names are shown for the Locking Activity report at the following LEVEL: LOCKOUT SUSPENSION SKPT resource names are shown for the Locking Activity trace at the following LEVEL: LOCKOUT TIMEOUT SUSPENSION DETAIL
SYSLGRNG	Buffer manager SYSLGRNG recording lock
TABLE	Table locking
TREEPLCK	Index tree P-lock
UTIL EXC	Utility exclusive execution lock
UTIL UID	Utility UID lock
UTILSER	Utility serialization lock
XML LOCK	XML lock

Note: For a suspended request where the resource type is not supplied, N/P is printed.

LOCK RESOURCE NAME

The name on which the suspended request is made. Each part of the lock resource name is printed on a separate line. The abbreviations shown in the report are explained, in alphabetical order, in Table 2.

Table 2. Lock Resource Name Abbreviations
Abbreviation	Description
ANCH	Anchor point ID
BPID	Buffer pool ID
COLL	Collection name
CKTN	Consistency token
DB	Database name
HASH	Database group exception hash class
OB	Object name
PAGE	Physical page
PART	Partition
PKID	Package name
PLAN	Plan name
RMID	Resource manager ID
ROW	Data row
ROWI	Row ID for LOB
SUBP	Subpage
UID	Utility ID
VER#	Version number of LOB

Note:

The database names and object names are translations obtained from the IFCID 105 and 107 records. If these records are not available, the decimal representation of the database and object names are printed.
If you specify DATABASE, PAGESET, or both, in the ORDER option, the database name, page set name, or both names, are removed from the resource name and printed in the OMEGAMON for Db2 Performance Expert identifier column. If the name only consists of the database and page set, N/P is printed in the resource name column. If the resource name does not contain the database and page set, 'BLANK' is printed in the OMEGAMON for Db2 Performance Expert identifier column and all resource name parts are printed in the lock resource block.

TOTAL SUSPENDS

The number of suspensions for the particular combination of OMEGAMON for Db2 Performance Expert identifiers.

SUSPEND REASONS

The reason why a particular request was suspended. The requests composing the particular combination of OMEGAMON for Db2 Performance Expert identifiers and lock resource can be suspended for several reasons. The SUSPEND REASONS column shows all reasons identified by the IRLM resume records. Therefore, the sum of the counts in this column can differ from the TOTAL SUSPENDS count.

The categorized reasons for suspension are:

LOCAL: Local resource contention. This occurs when you request access to a local resource that is locked.
LATCH: IRLM latch contention. This occurs when the IRLM needs to serialize a resource. For example, the IRLM serializes the adding and removing of locks to the lock table. The lock table is latched for a short period of time, and the resulting suspensions, if any, are brief.
GLOB.: Global contention. This occurs when you request access to a global resource that is locked.
IRLMQ: IRLM queued request.
S.NFY: Intersystem message sending.
OTHER: Suspensions other than those listed here. Suspensions reported as OTHER are either serviceability values, drain suspensions, contentions with retained locks, or page latch suspensions.

RESUME REASONS

The reasons for resumption of the suspended tasks. The reason can be normal, timeout, deadlock, and canceled (canceled only applies to page latch suspensions).

NORMAL NMBR: The number of suspensions that ended when the task resumed normal processing after completion of the lock request. In page latch suspensions, this is the number of suspensions where the latch requester was not canceled.
NORMAL AET: The average elapsed time of a suspension that ended in the task resuming normally. In page latch suspensions, this is the average elapsed time of a suspension where the latch requester was not canceled.
The format for this field is ssss.nnnnnn.
TIMEOUT NMBR: The number of waits to access locked resources that resulted in exceeding a preset time interval.
TIMEOUT AET: The average elapsed time of a resumption due to a timeout.
The format for this field is ssss.nnnnnn.
CANCEL NMBR: The number of page latch suspensions that ended with the latch requester being canceled.
CANCEL AET: The average elapsed time of a page latch suspension that ended with the latch requester being canceled.
The format for this field is ssss.nnnnnn.
DEADLOCK NMBR: The number of deadlocks.
DEADLOCK AET: The average duration of a deadlock.
The format for this field is ssss.nnnnnn.

SUM OF

The sum printed for the lowest-level identifier when there is more than one combination of request type, resource type, and lock resource reported under it.

GROUP TOTAL

The sum of report entries that belong to a data sharing group if more than one member of the group is reported for a particular combination of OMEGAMON for Db2 Performance Expert identifiers. A GROUP TOTAL only appears in group-scope reports.

SUBTOTAL

When a report is ordered by three identifiers and there is more than one third-level identifier reported under it, a subtotal is printed each time the second-level identifier changes.

TOTAL

When a report is ordered by two or three identifiers and there is more than one second-level identifier reported under it, a total is printed each time the first-level identifier changes.

GRAND TOTAL

If there is more than one first-level identifier reported, a grand total is printed at the end of each group in a group-scope report or at the end of each member in a member-scope report.

Lock Suspension Report

The following sample suspension report is produced with this command:

 
LOCKING
     REPORT

This is a sample Lock Suspension report.

   LOCATION: OMPDA21                    OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0)                   PAGE: 1-1
      GROUP: N/P                                LOCKING REPORT - SUSPENSION                     REQUESTED FROM: NOT SPECIFIED
     MEMBER: N/P                                                                                            TO: NOT SPECIFIED
  SUBSYSTEM: DA21                                ORDER: PRIMAUTH-PLANNAME                        INTERVAL FROM: 02/14/15 19:46:09.24
DB2 VERSION: V10                                       SCOPE: MEMBER                                        TO: 02/14/15 19:48:51.36

                                                            --SUSPEND REASONS-- ---------- R E S U M E   R E A S O N S -----------
PRIMAUTH           --- L O C K   R E S O U R C E --- TOTAL    LOCAL  GLOB.  S.NFY ---- NORMAL ----  TIMEOUT/CANCEL  --- DEADLOCK ---
 PLANNAME          TYPE      NAME                    SUSPENDS LATCH  IRLMQ  OTHER NMBR         AET NMBR         AET NMBR         AET
------------------ --------- ----------------------- -------- -----  -----  ----- ---- ----------- ---- ----------- ---- -----------
...
                   SKPT                                    15     0      0      0   15    0.000042    0         N/C    0         N/C
                                                                 15      0      0
                             CTKN=5359534C564C3031 
                             COLL(HEX)=              X'112233445566778899001122334455667788'  
                             PKID(HEX)=              X'1122334455667788'         
                  
...
LOCKING REPORT COMPLETE