Page and Row Locking Activity

This block shows the page locking, row locking, and lock avoidance activity for each object, performed by the event.

The page and row locking activity block is only printed if a commit occurred or a thread terminated.

In summary by occurrence, page and row locking activity information generated for explicit commits is shown on the relevant commit events.

In summaries by cursor or program, any explicit commits occurring during the life of that cursor or program are counted. Page and row locking activity caused by those commits is shown on the relevant cursor or program.

In summaries by statement number or statement type, commits are not counted. Because page and row locking activity is not relevant for these summary levels, it is not printed.

Any page or row locking activity occurring when a thread terminated is shown in the summary by thread. This activity is added to any page or row locking which took place in the body of the thread. Therefore, page and row locking figures in summary by thread can be greater than the sum of page locking figures shown in the body of the thread. The difference is the page and row locking activity occurring at thread termination.

Page and Row Locking Workload Block Example

Here is an example of the page and row locking workload block. 
--- PAGE & ROW LOCKING ------------------------------------------------------------------------------
                                          LOCK   MAXIMUM PAGE   # LOCK   HIGHEST   TS      LOCK AVOID
MEMBER     DATABASE   PAGESET    COUNT    SIZE   OR ROW LOCKS    ESCAL      LOCK   TYPE    SUCCESSFUL
SE11       DBPARALL   TSPARALL       1   PAGE               1        0               SPL          YES
SUMMARY : MAX PAGE OR ROW LOCKS HELD         1  LOCK ESCALATIONS : SHARED      0  EXCLUSIVE         0

SE12       DBPARALL   TSPARALL       2   PAGE               5        0               SPL          YES
SUMMARY : MAX PAGE OR ROW LOCKS HELD         5  LOCK ESCALATIONS : SHARED      0  EXCLUSIVE         0

SE21       DBPARALL   TSPARALL       1   PAGE               2        0               SPL          YES
SUMMARY : MAX PAGE OR ROW LOCKS HELD         2  LOCK ESCALATIONS : SHARED      0  EXCLUSIVE         0

TOTAL                                4                               0
Note:
  1. The DBID and OBID are obtained from IFCID 020.
  2. The values in MAX PAGE OR ROW LOCKS HELD, LOCK ESCALATIONS SHARED, and LOCK ESCALATIONS EXCLUSIVE are accumulated within a subsystem. They are reset only at thread deallocation or when a new user signon occurs.
  3. The values in MAXIMUM PAGE OR ROW LOCKS, HIGHEST LOCK, and # LOCK ESCAL are reset at commit time for dynamic BINDs and for static BINDs for which release (commit) is specified. Otherwise, these values accumulate until thread deallocation or until a new user signon occurs.
  4. IFCID 218 is an additional lock summary record, written for lock avoidance. It indicates whether a successful lock avoidance test occurred during a given unit of work. The record is externalized for the agent at each commit or rollback.
  5. For each event, the relevant IFCID 020 and 218 records are processed. If there is a DBID/OBID combination present for IFCID 218 but not for IFCID 020, the IFCID 020 fields show N/P. If there is a DBID/OBID combination present for IFCID 020 but not for IFCID 218, the IFCID 218 field (LOCK AVOID SUCCESSFUL) shows N/P.

Field description

Here is a description of the field labels shown in the page and row locking workload block.
MEMBER
The name of the Db2® member within the Db2 data sharing group.
DATABASE
The database name, if available.

If the name is not available, the decimal DBID is printed instead.

PAGESET
The page set name, if available.

If the name is not available, the decimal OBID is printed instead.

COUNT
The number of page locking or row locking occurrences for each page set.
  • Specific database and page set:
    • At commit time: always 1
    • At thread termination: the number of times this database and page set occurred on a commit record
  • TOTAL
    • At commit time: the total number of page sets listed
    • At thread termination: the sum of the values for all page sets
LOCK SIZE
The lock size used:
PAGE
Page lock
ROW
Row lock
TABLE
Table space or table lock
LOB
LOB lock
UNKN
Unknown lock
*
Multiple lock sizes
MAXIMUM PAGE OR ROW LOCKS
The maximum number of either page locks or row locks held at one time against this object.
# LOCK ESCAL
The number of lock escalations:
  • 0 if no escalations occur
  • For simple table spaces and partitioned table spaces not using selective partition locking (SPL): 1 if any escalation occurred for this table space in this logical unit of work
  • For segmented table spaces: the number of tables within the table space that have experienced lock escalations
  • For partitioned table spaces using SPL: the number of partitions for which locks escalated within the table space

The TOTAL contains the sum of all values in this column.

HIGHEST LOCK
The highest table space lock state.
If the table space is simple or partitioned not using SPL, it is the highest lock state for this database or page set. At trace end, it is the largest value from any commit for this object. The following values are possible:
IS
Intent share
IX
Intent exclusive
S
Share
U
Update share
SIX
Share with intent exclusive
X
Exclusive

If the table space is segmented or partitioned using SPL, this field is blank.

TS TYPE
The table space type:
SIMPL
Simple table space
SEG
Segmented table space
PARTI
Partitioned table space
SPL
Partitioned table space using selective partition locking (SPL)
LOB
LOB table space
LOCK AVOID SUCCESSFUL
Indicates whether there was a successful lock avoidance test during the unit of work.

If the state of this field changed during the summarization period, an asterisk (*) is shown.

MAX PAGE OR ROW LOCKS HELD
The maximum number of page locks and row locks held at one time across all objects.
LOCK ESCALATIONS: SHARED
The total of shared lock escalations.
LOCK ESCALATIONS: EXCLUSIVE
The total of exclusive lock escalations.