Using the Activity Monitor to Actively Monitor Application Locks - Page 3

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: paulz_ibm@msn.com.