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

162 DB2 Performance Expert for Multiplatforms V2.2

– Rows Written

– SQL code

– SQL state

– System CPU Time (sec.ms)

– User CPU Time (sec.ms)

– Sort Overflows

– Total Sort Time (sec.ms)

– Total Sorts

– Buffer Pool Data Physical Reads

– Buffer Pool Data Logical Reads

– Buffer Pool Index Physical Reads

– Buffer Pool Index Logical Reads

– Physical read requests into the temporary tablespace

– Logical read requests into the temporary tablespace

– Physical read requests into the temporary tablespace

– Logical read requests into the temporary tablespace

4.1.3 Statistics Details

Information pertaining to the entire DB2 instance is recorded and viewed in the

Statistics Details view. This information is particularly useful for diagnosing the

DB2 database activities. Information on the utilization and status of the buffer

pools, DB2 locking, DB2 logging, and DB2 storage is shown by this view.

The Statistics Details view provides a comprehensive view of DB2 performance

over time. It can be used to monitor various monitor elements or counters at

different levels. This helps you watch server loading for performance, availability,

and capacity planning.

Gathering and showing of Statistics Details data can be done in three different

modes:

 Regular processing

This is the default whenever you open a statistics window. The values

displayed are accumulated from the start of DB2. This mode is selected by

default, and is active if View → Delta is not selected.

Chapter 4. Features and functions - online and short-term monitoring 163

 Interval processing

The interval mode allows you to isolate DB2 statistics activity beginning at a

specific time. In this mode, the delta values accumulated since the start of

interval processing are shown for some columns. When you stop interval

processing, all the subsequent snapshots are shown in regular mode again,

that is, as accumulated since the start of DB2. This mode can be selected by

selecting View → Interval.

 Delta processing

The delta mode allows you to isolate DB2 statistics activity between two

snapshot refreshes. It can help you discover a problem when running

exception processing. In this mode, all statistics windows show the delta

between the values of two consecutive snapshots. This means accumulation

starts at zero after each snapshot. When you stop delta processing, all the

subsequent snapshots are shown in regular mode again, that is, as

accumulated since the start of DB2.This mode can be selected by selecting

View → Delta.

In all modes, statistics values are updated each time a refresh occurs.

As shown in Figure 4-11, Statistics Details is divided into different categories by

using panes; each pane shows data related to object associated with it.

Figure 4-11 Statistics Details

164 DB2 Performance Expert for Multiplatforms V2.2

 Instance Information

This pane can be used to view information about the sort work performed by

the DB2 instance being monitored and about its connections and agents. It

shows the following information regarding the instance.

– Connections

• Current connections

• Attempted connections for DB2 Connect

• Current connections for DB2 Connect

• Connection waiting for host reply

• Connection waiting for client to send request

• Remote connections to DBM

• Remote connections executing in the DBM

• Local connections

• Local connections executing in the DBM

• Local databases with current connects

–Sorts

• Total heap allocated

• Hash join threshold

• Post threshold sorts

• Piped sorts requested

• Piped sorts accepted

• Sort private heap high water mark

• Piped sorts (%)

– Agents

• Agents registered

• Agents waiting for token

• Maximum agents registered

• Maximum agents waiting

• Committed private memory

• Agents assigned from pool

• Agents created due to empty pool

• Stolen agents

Chapter 4. Features and functions - online and short-term monitoring 165

• Agents created (%)

• Maximum coordinating agents

• Connection switches

• Total inactive DRDA® agents

• Idle agents

• Maximum agent overflows

Under Instance Information, there are two sub categories:

– Fast Communication Manager

The Fast Communication Manager pane of the Statistics Details can be

used to view statistics on Fast Communication Manager (FCM). You can

get FCM information about each partition of a database manager instance

by double-clicking the instance. It shows the following data about FCM in

tabular form:

•Free buffers

• Free buffers low water mark

• Free connection entries

• Free connection entries low water mark

• Free message anchors

• Free message anchors low water mark

• Free request blocks

• Free request blocks low water mark

• Instance

•Partition

– Identification

The Identification section of the Statistics Details shows identification and

status information about the DB2 server. It shows the following monitor

elements:

• General, which includes:

Database manager type at monitored (server) partition

Server version

Time zone displacement

Snapshot time

Partition number