Input for Locking reports

If you do not use the Collect Report Data facility supplied with the Online Monitor, you need to know the input IFCIDs for Locking reports.

The IFCIDs used as input for the Locking reports, traces, and File data sets are shown in the following table.

Table 1. IFCIDs used for Locking reports
Locking report, trace, and File data set	IFCIDs
Lock suspension report or trace	44, 45, 105, 107, 213, 214, 215, 216, 226, 227
Lockout report or trace	105, 107, 172, 196
Deadlock trace	105, 107, 172
Timeout trace	105, 107, 196
Lock detail report	21
Lock detail trace	20, 21, 44, 45, 105, 107, 172, 196, 211, 212, 213, 214, 215, 216, 218, 223, 226, 227, 251, 257, 259, 337
File data set	21, 105,107, 211, 212, 223

Deadlock traces and Timeout traces are generated from Statistics class 3. When Statistics class 3 is active, deadlock and Timeout traces can be generated without starting any Performance trace classes.

DB2 IFC covers all important aspects of DB2 locking activity provided that the appropriate trace classes are active. Except for IDENT, SYNC, or QUIT, each occurrence of an IRLM request is traced by an IFCID 21, 211, or 212 record.

To maintain consistency in a data sharing environment, DB2 IFC generates an IFCID 251, 257, or 259, which records, or notifies on, a physical lock (P-lock) on a page set, partition, or page.

Whenever a suspension occurs, DB2 IFC generates an IFCID 44, 213, 215, or 226 record along with a matching IFCID record that specifies the reason for resume, regardless of how the suspension was generated. The matching IFCID record is an IFCID 45 for IFCID 44, IFCID 214 for IFCID 213, IFCID 216 for IFCID 215, and IFCID 227 for IFCID 226. Also, the Db2 trace produces IFCID 211 records for every claim request and IFCID 212 records for every drain request.

An IFCID 172 record is written when lock suspensions end in deadlock or when deadlock situations occur. The deadlock record details all units of work involved in the deadlock, the resources for which they were contending, and the attributes of their requests.

IFCID 196 records lock suspensions that end in a timeout or when timeout situations occur. It details all units of work involved in the timeout, the resource for which they were contending, and the attributes of their requests.

IFCIDs 218 and 223 provide summary information and details of successful lock avoidance, which can improve application performance by reducing lock suspension times.

IFCID 20 records the locking summary for page or row locks, the highest table space lock state, and lock escalation information for table spaces. The record is written for the thread at each commit or at a rollback.

IFCID 21 is the base for recording the detail lock requests.

IFCIDs 105 and 107 provide database and object identifier translation information used in all Locking reports and traces and in Locking File.

The following example shows the Db2 trace commands that are required to collect data for a detail Locking report or trace:

-START TRACE(P) DEST(OPX) CLASS(6,17,7,30) IFCID(226,227,251,257,259) BUFSIZE(512)
-START TRACE(S) DEST(OPX) CLASS(3) BUFSIZE(512)