Chapter 4. Features and functions - online and short-term monitoring 179
Figure 4-19 Viewing chart for each partition
Importing charts for BI and Content Manager environments
In PE V2.2.0.1, there are sets of predefined data views containing multiple charts
for those who are monitoring Business Intelligence (BI) or Content Manager (CM)
The charts were customized by BI and CM experts to identify common
performance counters for those environments. If you do not have one of these
environments, you may still find the charts useful as you learn PE.
The charts are stored as XML files in the SAMPLES directory under PE Client
install location. To use them, you must import them and answer some simple
prompts (database name, for example).
Select the Data Groups folder, then right-click and select Import. This will bring
up the dialog showing the predefined data groups. Select one of them and press
OK. For each chart that needs a specific object (for example, database name or
buffer pool), you will be prompted to select the object from a list.
4.1.5 Locks monitoring
Integrity of data is one of the most important aspect of RDBMS. DB2 guarantees
this integrity of data by using the locking technique. Locking is a technique that
enables you to access data from different applications without interfering in the
other’s functionality and data changes. It does so by updating particular data one
transaction at time. The efficiency of these locking mechanisms can have an
important effect on the performance of DB2 applications.
180 DB2 Performance Expert for Multiplatforms V2.2
While analyzing the locks, you may be concerned about many factors associated
with them. A few of the important ones are:
Lock Suspension
This is a typical locking performance issue that occurs when a transaction has
acquired a lock and another transaction requests a lock on the same
resource. In this case, the requesting transaction must wait. The application is
suspended and will stop running until the lock can be acquired. Since the
transaction and application is in the wait state, lock suspensions can have a
significant effect on application duration and performance. In this section, we
discuss how we can use DB2 Performance Expert to look for such locking
conflicts and also about applications holding the locks.
Deadlock is a condition in which cyclic dependency for locks occurs between
multiple transactions. The deadlock occurs when two or more transactions
connected to the same database wait indefinitely for a resource and this
waiting is never resolved because each transaction is holding a resource that
the other needs to continue. The deadlock problem has a great performance
impact for any concurrent scenarios, as the only recovery from this problem is
to roll back one of the transactions. We can use the Locks view under
Application Details (see 4.1.1, “Application details” on page 148) and the
Locks view under the Statistic Details, Database view (see 4.1.3, “Statistics
Details” on page 162) to get information about the number of deadlocks that
occurred for the selected application or database.
When an application has been suspended beyond a predetermined period of
time, DB2 terminates and rolls back the transaction. The application will
receive an error code. This unexpected termination is termed a
timeout. The
wait time is defined using the DB2 Lock timeout configuration parameter
(locktimeout) at the database level. Its value can be anything in seconds or 0
(no waiting) or -1 (wait forever). The value for locktimeout should be set to
less enough to quickly detect waits that are occurring because of an abnormal
situation, such as a transaction that is stalled. On the other hand, it should be
high enough so valid lock requests do not timeout because of peak
workloads, during which time there is more waiting for locks.
From the Locks view under Application Details and the Locks view under
Statistic Details, Database view, we can get information about the number of
deadlocks that occurred for the selected application or database.
Lock escalations
A lock escalation occurs when the number of locks held on rows and tables in
the database equals the percentage of the locklist specified by the maxlocks
database configuration parameter. In this situation, the database manager

Get DB2 Performance Expert for Multiplatforms V2.2, 2nd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.