Db2 for z/OS monitoring: Evaluating deadlock and timeout events on the Enhanced 3270 UI
pkenneyrocketsoftware 310002SVER Visits (4424)
New features are being continuously added to the OMEGAMON for Db2 Performance Expert Enhanced 3270 User Interface. A continued investment in the 3270 UI demonstrates a commitment to synergy and integration with the entire OMEGAMON family of products. This blog describes the latest feature introduced through the version 5.4.0 service stream.
Deadlocks and timeouts are a fairly routine occurrence in Db2 as applications vie for potentially the same resource. A deadlock occurs when two or more resources acquire an exclusive lock on the same object – typically to write to a table. A timeout is a built-in safety mechanism in Db2 (defined by ZPARM IRLMRWT) that cancels the threads in a deadlock situation. Any Db2 thread (OLTP, batch) can have contention for resources that cause deadlocks and timeouts to occur. It is important to find the cause of these contentions and reduce their occurrence to keep Db2 applications running efficiently. OMEGAMON Db2 Performance Expert on z/OS has had the ability to record and display Deadlocks and Timeouts on the Performance Expert Client for some time. Now that same data will be on the Enhanced 3270 UI.
The PTF UI57769 for APAR PI94904 must be installed on the 5.4.0 release of OMEGAMON for Db2. Parmgen may need to be run to configure collection of Events Deadlock and Timeout. See Parmgen parameters KD2_OMPE_DB2_EVENT, KD2_OMPE_DEADLOCK and KD2_OMPE_TIMEOUT.
Accessing Deadlock and Timeout Events
The remaining sections of this blog explain the panels that are especially useful to newer users of the OMEGAMON family of products. As deadlocks and timeouts are considered “events” in Db2, the enhanced 3270 UI Events are accessed by a new selection code ‘E’ from the Enterprise Summary for one single Db2 subsystem or selection code ‘E’ from the DB2 Main Screen for Data Sharing groups to display events from an entire DSG. Selecting ‘E’ will take you to the filter workspaces, where filter values can be entered to focus and limit the events you are looking for to find resource contention problems.
The first filter workspace is time ranges:
Using this filter workspace, you can narrow down a time range by minutes, hours or a specific date and time range, so you can find contentions.
From any of the filter workspaces, click the ‘OK’ button when you have completed entering your filters. The filters you entered will remain set for the entire logon session. Clicking the ‘Clear’ button on any of the filter workspaces will restore filters back to their default values.
Clicking on the Thread ID tab will bring you to the Thread Identification filter screen:
On this screen, you can enter any identification fields to limit the contentions you are looking for.
The asterisk (*) can be used for wild carding for multiple characters at the end of the field.
Clicking on the End User Tab will bring to the End User Filter screen.
Clicking OK will navigate to the Events Summary.
This is what the Events Summary looks like for a single Db2:
For a Data Sharing Group, you will see something like this:
Selection from the Summaries navigates to either Deadlock Detail or Timeout Detail depending on which type of Events the ‘S’ selection is entered on. The detail screens are the same from both Summaries.
The deadlock detail shows the resource type and a list of waiters and blockers.
Using the ‘S’ select code shows more detail about the deadlock:
The Waiter and Blocker SQL Statement ID are zoom columns. By Clicking on one of these field names you will navigate to the Dynamic or Static SQL cache to display statement information including statement text. This can only be displayed if the SQL statement is still present in the SQL cache.
Deadlock SQL Cache
This screen displays if the statement is in the SQL cache:You can then navigate to more details about the SQL statement in the cache including the SQL text.
What do I do about deadlocks?*
Deadlocks can often be prevented by changing applications to modify resources in the same sequence. Row level locking can sometimes help prevent deadlocks by reducing the amount of data being locked and preventing two applications which are updating the same page but not the same row from conflicting with each other.
A deadlock is detected by Db2 in milliseconds and Db2 will usually resolve the situation very quickly. But if a deadlock occurs at the end of long running application unit of work with a lot of updates without frequent commits, there will be an expensive ROLLBACK processing before normal processing can continue.
You can then select the blocker to get more detail.
Clicking on the Statement ID field name will navigate you to the SQL cache to display more information about the SQL statement that caused the contention. Same as with the Deadlock detail.
What do I do about timeouts?**
For timeouts, there are several things to look for.
Deadlocks and Timeouts are important events to monitor for and resolve to ensure optimal application throughput. This new Enhanced 3270 UI feature will enable users to quickly locate threads that were involved in contentions and diagnose and resolve their problems.
*From Db2 manual Managing Performance. Deadlock detection scenarios http
**From Db2 manual Managing Performance. Investigating and resolving timeout situations http