by Paul C Zikopoulos
One of the most frustrating tasks that database administrators (DBAs) have to perform is trying to figure out when one database application’s request is blocking another. Sure, most databases have built-in deadlock detectors and timeouts to help these sorts of issues work themselves out. However, being able to easily identify applications or requests that block others could significantly enhance the end-user experience of your system with minimal intervention. As any DBA who has ever had to deal with concurrency issues resulting from varying application locks can attest, investigating lock chains involves a significant annoyance factor and time investment.
The IBM DB2 Universal Database Version 8.2 for Linux, UNIX and Windows product (DB2 UDB) has a new wizard-driven feature called the Activity Monitor. In this article, I will introduce you to this tool’s blocking-resolution capabilities (it has others, which I will cover in future articles), and walk you through an example. Just think: a graphical view of locks and lock chains – I can see you smiling already!
A Little Blurb About the Activity Monitor
The Activity Monitor helps you improve the efficiency of database performance monitoring, problem determination, and resolution. By tracking a set of predefined monitor data, the Activity Monitor allows you to quickly locate the cause of the problem. You can then take direct action to resolve the problem or invoke another tool for further investigation.
The Activity Monitor can help you monitor application performance, application concurrency, resource consumption, and SQL statement usage. It can assist you in diagnosing database performance problems such as lock-waiting situations, and in tuning queries for optimal utilization of the database resources.
You can start the Activity Monitor by right-clicking your database in the Control Center and selecting Activity Monitor from the pop-up window, as shown in the figure below.
However, certain events triggered in the Health Center (for example, a locking event) will also give you the option to start this tool when you perform investigative work on the problem at hand. For many DBAs, the Health Center will likely be the launching point for this tool after they are notified about a locking issue, as shown below:
You can see in the preceding figure that there are too many applications that are waiting on locks. DB2 UDB has been configured to alert me if more than 75% of applications are waiting on locks. I could also configure a warning threshold that would alert me of a potential growing problem (an eventual alarm state) – the mixture of warnings and alarms is a great protective strategy.
From the Health Center, you use the Recommendation Advisor to have DB2 UDB help solve your blocking problem. Depending on how you respond to a number of questions, you could end up in the Activity Monitor as well. The Recommendation Advisor, which recommends using the Activity Monitor to investigate the locking problem at hand, is shown below:
