Chat now with support
Chat with Support

Spotlight on DB2 6.10 - 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

Sorting tab

The Sorting tab on the Client Application Analysis drilldown uses graphs to show sort activity performed by the selected application and contrasts this activity to overall sorting activity in the database.

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—overflows , non-piped sorts, less-than-optimal sort heap allocation size¾might indicate a need to adjust the SORTHEAP and SHEAPTHRES parameters. However, these problems might signal a need to tune the SQL within the application. Use the Appl Sort & Hash Join and the Appl Sort & Join Overflow Pct graphs on this tab to identify those applications that are generating sort and join overflows and hash loops. Then use the Top SQL drilldown to investigate problems with specific SQL in the application.

Graphs

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

App Sort and Hash Join

App Sort and Join Overflow Pct

Database Sort & Hash Join

Database Sort & Join Overflow Pct

 

Related topics

I/O Activity tab

The I/O Activity tab on the Client Application Analysis drilldown provides a summary of I/O activity generated by the selected application. 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.

Graphs

The graphs on the I/O Activity tab keep track of the rates for I/O generated by the application 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

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. Use the graphs on this I/O Activity tab to identify the applications that are contributing to these rates.

 

Related topics

 

Locks tab

The Locking tab on the Client Application Analysis drilldown provides details about each lock that the application currently holds and each lock on which the application is waiting. This information can help you pinpoint deadlocks that this application is causing or experiencing. You can also determine whether this application is reaching the maximum number of locks available to it.

Application identification

These details identify the application:

Auth ID

Appl Name

Agent ID

If you want Spotlight to collect and display information on the Locking tab for all applications, select Collect lock information for all applications. Otherwise, the Locking tab shows lock information for the currently selected application only.

Locks Held information

The Locks Held list grid shows the total of number of locks the application is holding and provides the following information about each lock. Since you can choose to hide, display, or rearrange any column in the grid, some columns below might not appear in your current list view, or be in the order in which they are shown here.

Lock Mode

Object ID

Object Type

Lock Status

Table Schema

Escalated

Table Name

Freeze First Column

Tablespace

 

Use this information to determine the locations and types of locks being held and whether the application is reaching the maximum number of locks available to it.

Locks Waiting information

The Locks Waiting list grid shows the total number of locks on which the application is waiting and provides the following information about each of these locks. Since you can choose to hide or display any column in the grid, some columns below might not appear in your current list view.

Lock Mode

Tablespace

Object Type

Holder's Agent ID

Table Schema

Holder's App ID

Table Name

Freeze First Column

Use this information to determine both the sources of contention in the database and the applications in contention for these sources.

 

Related topics

 

Lock Chains tab

The Lock Chains tab on the Client Application Analysis drilldown provides details about an application's lock relationships with other applications. It shows:

  • Which applications are holding locks that the selected application is waiting for.

  • Which applications are waiting for locks held by the selected application.

  • Whether locks are held by indoubt transactions.

You can use this information to determine which applications are in contention for resources.

Note: Before information can be shown in the Lock Chains tab, the lock monitor switch must be turned on.

Columns in the Lock Chains tab

All columns available for the Lock Chains tab are listed below in alphabetic order. Initially, the tab contains Spotlight's default column selection. You can choose which columns to display and organize them in any order from left to right using the Organize Columns function. This can be selected from the right-click menu for column headers.

Column

Description

Holding Agent ID

The application handle of the agent that currently holds a lock an application is waiting for. If the column is blank or 0 (zero) is shown, this indicates that the lock held by the agent is the top of the lock chain.

Waiting Agent ID

The application handle of the agent waiting for the lock.

Waiting Appl Name

The name of the application waiting for the lock.

Waiting Applid

The ID of the application waiting for the lock.

Waiting Authid

The authorization ID of the user who is running the application that is waiting for a lock. This is the ID that was used to connect to the database.

 

Related Topics

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating