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. This article introduces DB2 UDB Version 8.2's new
Activity Monitor, a graphical view of locks and lock chains.
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:
by Paul C Zikopoulos
Investigating a Locking Problem Using
the Activity Monitor
Once you open the
Activity Monitor, you need to select the database that you want to work with.
By default, the first database that you create in the default instance is
You can work
with any database in any instance on your system. If you want to work with a
different database than the default one selected by the wizard, click on the
ellipsis and select the instance and database that you want to work with, as
shown in the Select Database window. On my system, I just have a single
instance cataloged (called HEALTH) that has a single database (called SMART).
Once you decide
what database you want to work with, you can choose what type of problem you
want to investigate. You can see in the following figure that you have some provided
reports or investigative paths that you can follow. You can also create your
own, or new reports based on existing paths. For this example, since I am
interested in investigating a locking issue that the Health Center automatically
brought to my attention, I selected the Resolving an application locking
situation monitoring task.
systems could have hundreds of connections or multiple applications, you can
filter the information that the Activity Monitor returns. I set up this example
on my laptop, so I do not need to concern myself with filtering.
window lists all of the provided reports that DB2 UDB will return to you so
that you can investigate your locking problem:
report is then returned to you from the DB2 UDB engine. You can see in the
figure below that there are a number of locks that are waiting on other locks, but
have not timed out yet. Also, notice that you can filter the results in this
window as well.
You also have
access to the other reports that DB2 UDB generated for you. To see these
reports, click the Report drop-down box:
you select a different report, that report is shown in the Activity Monitor.
The following figure shows the largest number of rows read for the SQL executed
against the database (one of the provided reports you have the option of
Since all of the
applications are blocked right now (you will see this visually in a moment), there
are no SQL statements listed here. What you will find, however, is that the
column information returned changes for each report.
by Paul C Zikopoulos
Graphically Solving the Problem
Now it is time to solve the locking problem at hand. To solve this problem, the Applications holding the largest number of locks report would be a good place to start
In this report, you can see all the locks that are being held by the different application handles that are connected to the database. To see the lock chains that are causing the blocking problems, right-click an application and select Show Lock Chains
You can see the lock chains associated with the authorization ID. In the figure below, you can see that AMUSERB's application is causing blocking on AMUSERC's application, which in turn is blocking AMUSERD, which in turn blocks two other applications.
If you right-click any of the applications connected to the database, you can force the lock, find out more details about the lock, or show the SQL statement that is causing the lock.
In my example, I have forced off the AMUSERB and AMUSERD statements. When I refresh the lock chain on my system, I can see that some of the applications were able to continue working and are no longer causing problems but that one issue still remains:
Unlocking the Mystery
You have seen how much easier DB2 UDB V8.2 makes the investigation into lock chains and blocking, and in future articles you will see how the Activity Monitor can be used for lots of other problem-resolution tasks as well. When you combine that graphical lock investigation with the fact that DB2 UDB will monitor itself and let you know about the problem (or potential problem) before your phone starts ringing, then you know you've got a database that's like no other. Combine all these DBA lock-helping features with the concurrency enhancements that have been delivered since the V8.1 release, and your DB2 UDB system is ready to fly.
About the Author
Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technologies team. He has more than nine years of experience with DB2 products and has written numerous magazine articles and books about it. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). Currently he is writing a book on the Apache Derby/IBM Derby database. You can reach him at: email@example.com.