Paul Kenney's Posts (6)
Sort by
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.
Introduction
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.
Requirements
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.
Filter Workspaces
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.
Here, an asterisk (*) can be used at the end of the field for wildcarding.
Clicking OK will navigate to the Events Summary.
Event 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 Event the ‘S’ selection is entered on. The detail screens are the same from both Summaries.
Deadlock Detail
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.
*From Db2 manual Managing Performance. Deadlock detection scenarios https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/perf/src/tpc/db2z_managingdb2perf.html
Timeout Details
If you use ‘S’ to select from one of the summaries on a timeout type of events, you will navigate to the Timeout Detail screen.
This shows the identity of the thread that timed out, the resource causing the contention, and information about the blocker that was holding the resource.
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.
- Check the LOCKRULE on the tablespace. If it has LOCKSIZE TABLE or TABLESPACE and the application does not need to lock the entire table or tablespace, the LOCKSIZE could be changed to PAGE or ANY. Applications should not lock more resources than are nessassary.
- Check the number of LOCKS PER TABLESPACE) NUMLKTS system parameter, which was set in DSNZPARM. If many page or row locks are acquired and held, a small value for LOCKS PER TABLE(SPACE) might cause lock escalation. You can resolve the timeout by changing the value on the NUMLKTS DSNTIPK update panel or by specifying the RELEASE(COMMIT) bind options and committing changes more frequently.
- Check EXPLAIN output for the failing SQL statement and examine the value of the PLAN_TABLE.TSLOCKMODE column for every table or table space. If the competing applications are attempting to obtain incompatible locks, you might be able to resolve the timeout by running the applications sequentially rather than concurrently. You might also resolve the problem by changing lock size and the following bind options: RELEASE and ISOLATION.
- Check the RELEASE bind option for the application. Binding with RELEASE(DEALLOCATE) causes partition, table, table space, and DBD locks to be held longer than binding with RELEASE(COMMIT), possibly causing a timeout.
- Issue the DISPLAY DATABASE command and specify the LOCKS keyword during program execution. By doing so, you can verify that only expected locks are held when the timeout occurs. If unexpected locks are held, you might also resolve the problem by changing lock size and the following bind options: RELEASE and ISOLATION.
- Check the ISOLATION bind option of the application. The isolation level affects whether locks are acquired and how long they are held.
- You may want to increase the wait time if you know that your applications must keep resources longer than the specified time. You can increase the limit by specifying a new IRLM locked resource wait time limit. You can take one of the following actions to make the change:
- Use the parameter in the DSN6SPRM assembler macro in the DSNTIJUZ job stream Update the 'wait-time' field of the Db2 installation IMS Resource Lock Manager panel, IRLMRWT. You must also specify all other parameters contained in this version of the CSECT. Next, take one of the following actions:
- Resubmit installation job DSNTIJUZ with the link-edit SYSIN file 'NAME' parameter that matches the -START DB2 'PARM=' parameter.
- Reassemble DSN6SPRM and relink-edit DSNZPARM by resubmitting installation job DSNTIJUZ. The link-edit SYSIN file 'NAME' parameter must match the -START DB2 'PARM=' parameter.
**From Db2 manual Managing Performance. Investigating and resolving timeout situations https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/perf/src/tpc/db2z_managingdb2perf.html
Conclusion
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.
New features are being added to the Enhanced 3270 User Interface in each release of OMEGAMON XE for Db2 Performance Expert on z/OS. A continued investment in the 3270 UI provides more synergy and integration with the entire OMEGAMON family of products. One of the most important features introduced for version 5.3.0 is the ability to display Thread History.
Introduction
In OMEGAMON for Db2, there are two types of history. The Snapshot type of history takes a sample of what is happening in Db2 at a specific point in time. This is the type of history in the Enhanced 3270 UI using the Persistent Data Store (PDS). The other type of history is DB2 Event History. To a DBA, while snapshot history might capture a thread in suspension if the snapshot was recorded at that very moment, thread history/event history is the more important tool for evaluating DB2 performance history.. In event history, data is recorded when an event takes place in DB2. The most important event is thread termination. At thread termination, Db2 produces an IFCID 3 Accounting record with metrics about the resource utilization of that thread. The Enhanced 3270 UI Thread History feature allows metrics from the Accounting records to be displayed on Enhanced 3270 UI.
Requirements
The PTF for APAR PI82090 needs to be installed on the 5.3.0 release of OMEGAMON for Db2. Parmgen will need to be run to configure collection of Thread History.
Accessing Thread History
The remaining sections of this blog provide a detailed walk-thru of the panels that are especially useful to newer users of the OMEGAMON family of products. The enhanced 3270 UI Thread History is accessed by a new selection code ‘R’ from the Enterprise Summary. Selecting ‘R’ will take you to the filter workspaces where filter values can be entered to focus and limit the threads you are looking for to find performance problems.
Filter Workspaces
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 poorly performing threads.
Clicking the Counts/Time tab will bring you to the Counts/Time filter workspace.
This filter workspace allows you to set filters by time or count of events. For example you might want to set Deadlock/Timeout greater than 1, or DB2 CPU time greater than 30 seconds.
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 threads you are looking for.
For example, if I was looking for a specific CICS transaction I could enter in the CICS TX field QMFE.
The ‘*’ or ‘?’ can be used for wild cards. ‘?’ for a single character. ‘*’ for multiple characters at the end of the field. The connection type cannot be a wild card it must be one of the specific types like DRDA.
From any of the filter workspaces, you can click the ‘OK’ button when you have completed entering your filters. The filters you entered will remain set for your entire session. Clicking the ‘Clear’ button on any of the filter workspaces will set all filters back to their default values.
Thread History Summaries
There are five different Thread History Summary workspaces. The first general Thread Summary always is displayed when the ‘OK’ button is clicked. The other summaries are accessed by clicking one of the tabs on all of the thread summary workspaces.
This is the general Thread Summary.
By clicking the ‘BP Summ’ tab summary information related to Buffer Pools is displayed.
Clicking the ‘DB2 Time Summ’ tab will display a summary workspace with DB2 time related data.
Clicking on the ‘Lock Summ’ tab will display Lock related summary information.
Clicking on the ‘Alt Summ’ tab will display a collection of some of the fields shown on each of the other summary workspace.
From any of the summary workspaces there are two different select codes to display more detail about a thread in history. The ‘S’ select is to display Thread Accounting Detail and the ‘W’ select is to display Thread Wait Time detail. ‘S’ is the default.
The Thread Accounting Detail displays the Thread Identification, the DB2 Class 1 and Class 2 times.
Elapsed times and CPU times spend running the thread and used within Db2 can be review
Clicking the Class 3 tab or using the ‘W’ select from one of the summary workspaces will display the Thread Detail Class 3 wait times for a thread.
Conclusion
Thread History on Enhanced 3270 UI feature will enable users to quickly locate threads that were performing badly in the past and diagnose and resolve their performance problems.
