DB2 Performance Expert for Multiplatforms V2.2, 2nd Edition

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)

environments.

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.

 Deadlocks

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.

 Timeouts

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