Analyzing lock usage without tracing LOCK events
The first place to look is the Lockout History display that shows you a list of the last 100 timeouts and deadlocks that have occurred.
The complete details from these events are available to identify holder and waiter threads and the resources that are involved.
To analyze locking problems for an application, first look at the accounting record (STRAC) for the counts on maximum page locks, suspensions, lock requests, and timeouts/deadlocks. This data is already available with a summary trace and should help you identify which applications are having–or causing–problems. For more information, a detail trace with SQL events is often sufficient. Usually you will want to include SQL so you can more easily determine which statement is causing the problem, but a great deal of information is available with just the basic detail trace.
If you are still searching for the culprit application, you might want to run a basic detail trace with no further events (not even SQL), but covering all the suspected applications. This type of trace causes much less overhead. You could also use exception filters in the trace request to identify threads with lock suspensions, escalations, lock table requests, or those with a high maximum number of locks held.
The events that are described in the following table are available in DTRAC for any detail trace. Analyze this information before deciding to trace LOCK events.
Long-running thread events
Event | Description |
|---|---|
LOCK-SUMMARY (display LEVEL=2) | This event appears near the end of the thread processing. It shows the maximum number of page locks held and the highest lock state for each page set (table/index space) accessed. |
LOCK-TMO (display LEVEL=1) | This event is shown for any thread terminated by a timeout or a deadlock. It shows the page set that is involved and identifies the first holder of the lock that was in contention, as well as the state of the lock (for example, held exclusive). It is followed by a DEADLOCK event or a TIMEOUT event (display LEVEL=2) that gives complete details. |
LOCK-SUSP (display LEVEL=3) | This event is shown for all lock suspensions. It shows the elapsed time suspended, the page set, the type of entity locked (for example, a data page, index tree, and so on), page number (if applicable), and lock state. |
Another source of information is the DB2 timeout and deadlock messages in the Journal Log or MVS console. If the DB2 messages are being written to the Journal, go to Option L and issue a FIND DSNT37 to find any deadlock (DSNT375) or timeout (DSNT376) messages. They are followed by DSNT501I to identify the resource and lock owner that caused the contention. If MainView AutoOperator Solutions are installed, you can use the DB2DLOK solution to summarize and analyze the deadlock/timeout messages and LOCKD contention snapshots. For more information, see the MainView AutoOPERATOR Solutions Guide.