Lockouts
Views for lock contention analysis that are based on timeout and deadlock events are organized into the following groups:
- Lockout event views
- Resource conflict views
- Involved thread views (victims, blockers, and waiters)
Lockout records are created for each resource involved in a lockout (usually just one per timeout, but two or more per deadlock). You can view these records by resource, blocker, and waiter, not just by the lockout events and the victim identifiers. Each record displays the victim as the waiter or blocker, or displays only participants in a deadlock. Lockout event views select only those records in which the victim is the waiter, whereas resource, waiter, or blocker views select all records.
MainView for DB2 automatically captures the DB2 IFCIDs 172 (deadlocks) and DB2 IFCIDs 196 (timeouts) to collect this data. The records are written to history as events occur. You can retrieve the records by Time period. If no Time is specified, the latest events still in the active LKOUT buffer (default of 100) are displayed. You can change the default with the LOCKOUTS parameter in the DMRBEXxx BBPARM member.
A tuning wizard (WZLOCK) simplifies lock contention analysis by guiding you through the views that are described in this section. For more information about WZLOCK, see DB2-tuning-wizards-usage.
You can also access the views for lock analysis from the DB2 Locking easy menu (EZDLOCK) (displayed in the following figure) (PTF BPD4875 applied). The lockout views are in the lower half of the menu. When you are using a data sharing group context for global lock analysis, use the options on the lower right side of the menu. For a single DB2 subsystem or DB2 member, use the options on the lower left side. The options in the middle work well for both situations.
DB2 Target ---> 1210
Currently Held Locks +----------------------+ Lock Statistics
. Suspended Threads | Place cursor on | . Lock Counts-One DB2
. Resources with Waiters | menu item and | . Lock Counts-Data Shar.
. Locks Held / Threads | press ENTER |
. Locks Held / Resources +----------------------+
Lockouts - One DB2 < Lockout analysis > Lockouts-Data Sharing
. Lockout Events by Time . Resource Cont. Summary . Lockout Events by Time
. Blocker/Waiter Summary . Conntype Cont. Summary . Blocker/Waiter Summary
. Blk/Wt Summ by AuthID . Blk/Wt Summ by AuthID
Summary by PlanName
Contention Analysis . Blocker Summary
. Lock Wizard Dialog . Waiter Summary . Return... .
Summary by AUTHID
. Blocker Summary
Total Locks overview . Waiter Summary Total Locks summary
The following table lists the views available for lockouts:
View name | Group | Type | Description |
|---|---|---|---|
LKBLOCK | Thread | Detail | Lockout blocker plan detail Lists all conflicts by blocker plan and time stamp Use this view to identify all specific conflicts caused by a plan over time. |
LKBLOCKZ | Thread | Summary | Lockout blocker plan summary Summarizes conflicts by blocker IDs Use this view to analyze plans and users that are causing lockouts. |
LKBLK2Z (PTF BPD4875 applied) | Thread | Summary | Lockout blocker plan summary view, sorted by authorization ID Summarizes conflicts sorted by authorization ID Use this view to analyze plans and users that are causing lockouts. |
LKBWZ | Thread | Summary | Lockout blocker/waiter summary Lists a summary of blocker and waiter plans Use this view to identify conflicting plans. |
LKBW2Z (PTF BPD4875 applied) | Thread | Summary | Lockout blocker/waiter summary for a single DB2 context, sorted by authorization ID Lists a summary of blocker and waiter plans, sorted by authorization ID Use this view to identify conflicting plans. |
LKBWZSSI | Thread | Summary | Lockout global blocker/waiter summary Lists a summary of blocker and waiter plans and systems Use this view to identify conflicting plans across multiple data sharing DB2 members. |
LKB2ZSSI (PTF BPD4875 applied) | Thread | Summary | Lockout global blocker/waiter summary view, sorted by authorization ID Lists a summary of global blocker and waiter plans, sorted by authorization ID Use this view to identify conflicting plans. |
LKCONZ | Thread | Summary | Lockout connection summary Lists a summary of lockout victims by connection type Use this view to identify scheduling problems with incompatible workloads (for example, IMS/CICS transactions blocked by batch). |
LKEVD | Event | Detail | Lockout event resource detail Provides complete data about the selected resource conflict |
LKEVENT | Event | Tabular | Lockout events Lists the latest timeout (IFCID 196) or deadlock (IFCID 172) events that have occurred in this DB2 |
LKEVRES | Event | Tabular | Lockout event resources Lists each resource involved in the selected lockout |
LKEVSSI | Event | Tabular | Global lockout events Lists lockout events within a data sharing group |
LKPRINT | Event | Tabular | Lockout event report LKPRINT is designed to show all lockout contentions in a format suitable for export to a data set for printing or downloading in CSV format to a spreadsheet (EXPORT command), or for printing in a batch job (MVBATCH utility). It is over 133 characters in length, so you may want to customize it to hide unneeded fields, or use the alternative view, LKPR133. Lockout events that involve two or more resource contentions, such as deadlocks (and some timeouts), are represented by multiple rows. The victim threads, whose SQL requests were terminated, are identified by the rows with a non-blank Victim Authid and a W/B Flag (Waiter/Blocker) value of W. |
LKPR133 | Event | Tabular | Lockout event report (Len=133) LKPR133 is designed to show all lockout contentions in a format suitable for export to a data set for printing or downloading in CSV format to a spreadsheet (EXPORT command), or for printing in a batch job (MVBATCH utility). Several fields have been shortened or hidden to fit a print length of 133 characters. If you want to customize a view yourself, it is preferable to start from alternative view, LKPRINT. Lockout events that involve two or more resource contentions, such as deadlocks (and some timeouts), are represented by multiple rows. The victim threads, whose SQL requests were terminated, are identified by the rows with a non-blank Victim Authid and a W/B Flag (Waiter/Blocker) value of W. |
LKPR133L | Event | Tabular | Lockout event long name report (Len=133) This view is similar to LKPR133, but it displays the long name for the resource next to the resource name. |
LKRESD | Event | Tabular | Lockout resource conflict detail Lists each occurrence by time of any lockout on which the selected resource (database, table space, or specific page) was involved Use this view to identify all plans involved in conflicts on this resource and help identify potential application scheduling problems. |
LKRESNRZ | Resource | Summary | Lockout resource number summary Lists each page or row involved in any conflict, usually for a selected resource (database or table space) from LKRESZ Use this view to see the hot spots in a table by identifying the pages that were hit. |
LKRESZ | Resource | Summary | Lockout resource summary Lists each resource (database or table space) involved in any conflict Use this view to see which table spaces cause the most conflicts. |
LKWAIT | Thread | Detail | Lockout waiter plan detail Lists all conflicts by waiter plan and time stamp Use this view to identify all specific conflicts over time where a plan was a waiter. |
LKWAITZ | Thread | Summary | Lockout waiter plan summary Lists conflicts by waiter IDs Use this view to analyze plans and users suffering most from lockouts. |
LKWAIT2Z (PTF BPD4875 applied) | Thread | Summary | Lockout waiter plan summary view, sorted by authorization ID Lists conflicts sorted by authorization ID Use this view to analyze plans and users suffering most from lockouts. |
Related topic