Chat now with support
Chat with Support

Spotlight on DB2 6.9.3 - User Guide

Spotlight on IBM DB2 LUW (Linux, Unix, and Windows)
New in This Release Getting started with Spotlight on IBM DB2 LUW Desktop features specific to Spotlight on IBM DB2 LUW Spotlight on IBM DB2 LUW drilldowns
About Spotlight on IBM DB2 LUW drilldowns Buffer Pool Analysis drilldown Client Application Analysis drilldown Database Analysis drilldown Database Manager Summary drilldown Diagnostic Log drilldown FCM Analysis drilldown Tablespace Analysis drilldown Top SQL drilldown Operating System drilldown Workload Management Analysis drilldown
Spotlight on IBM DB2 LUW alarms Spotlight on IBM DB2 LUW Options Tuning SQL statements in Spotlight on IBM DB2 LUW
Spotlight Basics
Spotlight Connections Monitor Spotlight Connections Alarms Charts, Grids And Home Page Components View | Options Troubleshooting
Spotlight History Spotlight on Windows
Connect to Windows Systems Background Information Home Page Alarms Drilldowns View | Options Troubleshooting
Spotlight on Unix About us Third-party contributions Copyright

Details tab

The Details tab on the Buffer Pools drilldown provides the following information about the selected buffer pool:

  • Configuration information

  • Use of extended storage

  • The amount of time required to perform I/O that involved this buffer pool

Extended storage utilization statistics

Extended storage is a secondary level of storage for buffer pools, allowing the user to access memory beyond the maximum allowed for each process. When the database has buffer pools configured for extended storage, the extended storage counters are useful. They show the number of data and index pages belonging to the tablespace that are copied in and out of extended storage. If the number of pages copied out of extended storage back into the buffer pools is higher than the number of pages copied into extended storage from the buffer pools, your database at this tablespace level is probably benefiting from extended storage.

Buffer pool identification and configuration information

The Buffer pool Details list shows current identification, extended storage, and configuration settings for the selected buffer pool.

Buffer Pool Name

Page Size (bytes)

Extended Data Pages Out

Buffer Pool ID

Total Size

Extended Index Pages Out

Node Group

Uses Extended Storage

Extended Data Pages In


Files Closed

Extended Index Pages In





The Buffer pool Timings bar graph compares the amount of time (in seconds) required to perform each of these types of I/O that involve the selected buffer pool.

Direct Write

Direct Read

Pool Async Write

Pool Write

Pool Async Read

Pool Read

Use the graph to get an overall sense of the time required to perform I/O involving this buffer pool.


Related topics


I/O Activity tab

The I/O Activity tab on the Buffer Pools drilldown provides a summary of I/O activity involving the selected buffer pool. The graphs show rates for the following:

  • Reads and writes that use the buffer pool

  • Direct reads and writes, which do not go through the buffer pool, but access the database directly

The bottom of the tab lists average I/O times (in milliseconds) for both buffered and direct reads and writes. The average times are shown in milliseconds.


The graphs on the I/O Activity tab keep track of the rates for I/O activity that involves the buffer pool. These rates are plotted over consecutive monitoring intervals.

Data Read Rate

Index Read Rate

Index and Data Write Rate

Direct Read and Write Rate

Troubleshooting high I/O activity on the database

On the Databases drilldown, you might notice consistently high rates for the following activities on specific databases:

  • Physical read rates (compared to logical read rates)

  • Synchronous read or write rates

  • Direct I/O rates for a database

These high rates might be indications of performance degradation since the activities involve disk I/O and slow-downs in query processing. You can use the graphs on this I/O Activity tab to pinpoint those buffer pools that are not being used effectively to offset I/O on the database.


Related topics


I/O Times tab

The I/O Times tab on the Buffer Pool Analysis drilldown contains graphs that let you visually monitor average I/O times and buffer pool hit ratios for a buffer pool. The four graphs track the following:  

  • Average amount of time spent on direct I/O.

  • Average amount of time spent on asynchronous I/O.

  • Average amount of time spent on physical I/O.

  • Buffer pool hit ratios.

All four graphs use counter data to show statistics as they change over time. This allows you to see when average I/O times are high and buffer pool hit ratios are low for a buffer pool.      

Consistently high I/O times can be a symptom of I/O conflict. Consistently low hit ratios can be a sign that a buffer pool is not operating efficiently. Both problems degrade performance. To identify applications that are contributing to I/O conflict, work from the Client Application Analysis drilldown. To investigate buffer pool conditions, work from the Buffer Pool Analysis drilldown.

Graphs in the I/O Times tab

Click the links below to see descriptions of each graph in the I/O Times tab:

Average Direct I/O Times

Average Async I/O Times

Average Physical I/O Times

Read Hit Ratios

Functions for graphs

As you view graphs in the I/O Times tab, you can click icons in graph toolbars to:

Copy graph details to the clipboard

Change the graph view

Filter a graph

Maximize or restore a graph

Pan over a zoomed graph

Zoom a graph


Statistics tab

The Statistics tab on the Buffer Pools drilldown lists performance statistics for database events associated with the selected buffer pool. Each row in the list identifies a specific event and provides countsfor the event based on these time measurements:

  • Interval—Displays the number of occurrences of the event for the most recent monitoring interval.

  • Interval Rate/Sec—Displays the rate per second during the most recent monitoring interval.

  • Lifetime—Displays the number of occurrences during the life of the event (since the database was activated or performance statistics were reset).


Related topics


Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating