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

Caching tab

The Caching tab on the Database Analysis drilldown graphically depicts the utilization of the various resource caches (memory areas) maintained for each database. These caches include package cache, catalog cache, the SQL workarea , and buffer pools—all of which enhance the performance of the database when their respective hit rates are high. Usually the hit rate is the ratio of retrieval requests that were satisfied by data already in the cache to the number of all requests—those satisfied by the cache plus those that required a disk read. (This ratio is expressed as a percentage in the various graphs on this tab.)

If you identify a cache utilization problem for a specific database on this tab, you can use the Caching tab on the Client Application Analysis drilldown to identify those applications that are contributing to the problem.


Use the following graphs to follow cache performance over consecutive monitoring intervals:


Related topics


Sorting tab

The Sorting tab on the Database Analysis drilldown uses graphs to show sort activity within the selected database and to contrast this activity to overall sorting activity in the instance.

About sorts

An application query often requires that retrieved rows be put in a specific order—that is, be sorted—to satisfy a SQL sort request (as with the ORDER BY clause), to construct an index, or to perform a hash join. Sort heap is a block of memory that the database manager allocates to an application as workspace whenever the application requires a sort. An important factor in the performance of any sort is the size of its sort heap, which is determined by one of these parameters:

  • SORTHEAP database configuration parameter

  • SHEAPTHRES database manager configuration parameter

A sort consists of these steps:

  • The actual sort process—If the information being sorted cannot fit into the sort heap, some information is written to temporary database tables. This incident is called a sort overflow. Sorts that do not overflow perform better than those that do.

  • Return of the sorted rows—If sorted information can be returned directly without requiring a temporary table to store the final, sorted list of data, the sort is referred to as a piped sort. If the sorted information requires a table to temporarily store the data before it is returned, the sort is referred to as a non-piped sort. A piped sort always performs better than a non-piped sort.

Sorting problems, such as overflows, non-piped sorts, less than optimal sort heap allocation size, might indicate a need to adjust the SORTHEAP and SHEAPTHRES parameters.


Use the following graphs to determine sort activity and problems over consecutive monitoring intervals:

Database Sort and Hash Join

Database Sort and Join Overflow Pct

Instance Sort and Join Exceptions

Instance Sort and Join Exception Pct


Related topics

I/O Activity tab

The I/O Activity tab on the Database Analysis drilldown provides a summary of I/O activity on the database or partition you are monitoring. The graphs track of rates for the following:

  • Reads and writes that involve the buffer pool

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


The I/O Activity tab contains the following graphs, which keep track of database I/O rates over consecutive monitoring intervals:

Data Read Rate

Index Read Rate

Index and Data Write Rates

Direct Read and Write Rates

Troubleshooting high I/O activity on the database

High rates depicted on the graphs for the following activities might be indications of performance degradation since these activities involve disk I/O and slow-downs in query processing:

  • Physical read rates (compared to logical read rates)

  • Synchronous read or write rates

  • Direct I/O rates for a database

If you need to investigate high rates for any of these activities, refer to the graphs on the I/O Activity tab in the following drilldowns:

  • Client Application Analysis drilldown—To identify the applications that are contributing to high I/O activity rates

  • Tablespaces drilldown—To pinpoint the tablespaces on which the high I/O activity is occurring

  • Buffer Pools drilldown—To identify those buffer pools that are not being used effectively to offset I/O on the database


Related topics

Locking tab

The Locking tab on the Database Analysis drilldown gives you an overview of lock activity on the database. Scanning the graphs and statistics on this drilldown, you can quickly detect the following locking problems on the database:

  • High rates of lock holding or waiting

  • High numbers of deadlocks, escalations, or timeouts

  • Near-or full-capacity utilization of the lock list space.

If lock problems are evident, use the Client Applications drilldown to investigate the lock activity of individual applications to pinpoint those applications that are contributing to the locking issues.


Monitor the following graphs to detect abnormal lock activity on the database:

Average Lock Wait Time ( msec )

Lock List Utilization (%)

Lock Status


Lock statistics

These lock activity counts provide more detail about any lock problems depicted in the graphs. For each activity type, you can view the count for the lifetime of the statistic (the number of occurrences since the database was activated or performance statistics were reset).

Lock Waits

Lock Escalations

Lock Timeouts

Lock Escalations, Exclusive

Lock Deadlocks Detected

Lock Wait Time, Total (ms)

Additionally, the Statistics tab shows rates based on these counts. The names for these statistics begin with Lock.


Related topics


Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating