Tables tab
The Tables tab in the Tablespaces drilldown shows a list of all tables in the selected tablespace. The list provides the following information about each table in a grid format. 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 shown here.
|
Column |
Description |
| Schema | Schema name of a table. Only catalog and user tables use schema names. |
| Name | Name of the table. Only catalog and user tables have table names. |
| Rows Read | The number of rows read from this table since Spotlight on IBM® DB2® LUWconnected to the DB2 for LUW database or since statistics were reset. If this value is high, consider creating additional indexes on the table. |
| Rows Written | The total number of rows that were changed (inserted, deleted, or update) in the table since Spotlight connected to the database or since statistics were reset. If this value is high, consider executing the DB2 RUNSTATS utility on this table to keep its statistics up to date for efficient SQL access to the table data. |
| Index Pages | Name of the tablespace that holds all indexes created on the table. |
| Overflow Accesses |
The number of times that accesses to overflowed rows on this table were required since Spotlight connected to the database or since statistics were reset. An overflowed row is an updated row that no longer fits on the data page where the row was originally written. The overflow is usually the result of one of these events:
Overflowed rows indicate that data fragmentation has occurred. If the number overflowed row accesses for the table is high, consider reorganizing the table using the DB2 REORG utility to clean up the fragmentation. |
| Page Reorgs | Total number of page reorganizations for a table. |
| Data Pages |
Total number of pages used by data in this table. |
| Lob Pages | Total number of pages used by LOBS related to this table. |
| Long Pages |
Total number of pages used by long data in this table. |
| XML Pages |
Total number of XML pages from tablespace containers for regular and large spaces. |
| Data Partition ID |
Identifies the data partition for which information is returned. |
| Type | Identities the type of table for which information is returned. |
Top SQL drilldown
Welcome to Spotlight on IBM DB2 LUW (Linux, Unix, and Windows). The help topics in this book cover features available in the interface when Spotlight is connected to a version 9 database.
Spotlight is a powerful database monitoring and diagnostic tool. Its unique user interface provides you with an intuitive, visual representation of the activity on the DB2 databases and database partitions that you choose to monitor. Graphical flows and line and fill graphs illustrate the activity on and between database components. Icons display the value of key statistics. Using the Spotlight browser, you can switch your monitoring focus between the various databases and partitions. Additionally, you can view activity at the DB2 instance level for any of these systems.
The power of Spotlight lies in its ability to provide visual and audible warnings if the performance metrics exceed acceptable thresholds. The components and dataflows on the home page change color to show you the source of the problem.
A range of reports and graphs provide you with detailed information about a DB2 database, database partition, or the instance to which the database belongs. This information can be viewed on the screen or be printed.
You can set Spotlight options to warn you when a threshold is reached. You can define a number of thresholds so that warning messages are displayed well before the traffic levels into or out of databases become critical.
Spotlight uses a number of different techniques to warn you when your DB2 instance or a database is exceeding a threshold. For example, to issue a warning, you can configure Spotlight to change a color on the home page or drilldown, provide an audible signal, or perform an action, such as send an email message.
Top SQL drilldown
The Top SQL drilldown shows performance metrics for the SQL statements executed in an active database or partition. It is designed to reveal statements that are the "top SQL" in terms of being the most resource intensive and high cost. For example, you can use it to identify statements that consume the most CPU, statements with the lowest hit ratio, or statements with the most sort overflows.
Metrics are shown in tabs for current, dynamic, and static SQL statements. From each top-level tab, you can drill down to detailed information on problematic statements. This is provided in sub drilldowns, which are displayed in the lower half of the window when a statement is selected. The additional information can help you diagnose why a statement is performing poorly.
Using the Top SQL drilldown
The steps below outline the basic workflow involved in using the Top SQL drilldown.
To use the top SQL drilldown
-
View top-level tabs to detect poorly performing SQL statements problems in client applications. The tabs and the SQL they show are as follows:
- Current SQL tab—This tab shows metrics for statements that are currently executing in a database or partition. The statements are those issued by applications that currently connected to the database or partition.
- Dynamic SQL tab—This tab shows metrics for dynamic SQL statements that are currently executing or have executed in the past for a database or partition. Past statements are those that have been executed since been executed since a database was started. The statements are those issued by applications that are currently connected or that have been connected at some point since the database was started.
- Static SQL tab—This tab shows metrics for static SQL statements that are currently executing or have been executed in the past for a database or partition. Past statements are those that have been executed since the current session was started. The statements are those issued by applications that currently connected to the database or partition.
Tip: You can use various display features to focus on the most problematic statements. You can organize columns from left to right, hide or show columns, and sort the tab by values in a certain column. To sort by a certain column, click the column header. Being able to sequence and sort columns in different ways allows you to quickly identify different performance problems.
- When you detect a problematic statement in a top-level tab, view its sub drilldown tab to see additional information and view the statement. Click the row for the statement to display the sub drilldown. The additional information can help you diagnose why a statement is performing poorly. (To close the sub drilldown, click the X button in the top right corner of the drilldown.)
- After you detect and diagnose problematic statements, you can tune them using SQL Optimizer for DB2. (You must have either product licensed and installed on the on the same computer where the Spotlight for DB2 client resides.) SQL tuning sessions can be opened from the sub drilldown for each tab.
Current SQL tab
The Current SQL tab on the Top SQL drilldown shows metrics for SQL statements that are currently executing against a monitored database or partition. The statements can be either dynamic or static.
Note: Spotlight captures information for statements that are in flight when it is refreshing. If a statement is not in flight during a refresh, information is not captured. This means a statement might not be shown or that values might not reflect all executions of a statement.
From this tab, you can drill down for more details on problematic statements and tune them.
Drill down on a statement
When the Current SQL tab shows a problematic statement, you can drill down to view additional performance information specific to that statement.
To drill down on a specific statement
Click the statement in the list of currently executing statements.
The Unit of Work sub drilldown is displayed at the bottom of the Current SQL tab. This sub drilldown shows the selected statement and provides additional metrics for this statement. Use these metrics to diagnose why the statement is performing poorly. Then, from within this sub drilldown, you can open a tuning session on the statement.
Tune a statement
From the Unit of Work sub drilldown, you can open a tuning session on the currently displayed statement if you have either of the following products licensed and installed on the same computer where your Spotlight on IBM® DB2® LUW client is installed:
-
Spotlight on IBM DB2 LUW
To tune the SQL statement currently displayed in the Unit of Work sub-drilldown
-
Click
on the left side of the Unit of Work sub drilldown.
SQL Optimizer for DB2 opens, showing the SQL statement that you were examining in Spotlight in the product's main window.
-
If you need instructions to proceed with the tuning session, click F1 for context-sensitive help.
Columns in the Current SQL tab
All columns available for the Current SQL 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 |
|
Agent ID |
The application handle of the agent for the application that issued an SQL statement. |
|
Database |
The name of the database on which an SQL statement is executing. |
|
Data Hit Ratio |
A statement's ratio of physical reads to logical reads for data pages. |
|
Elapsed Time (microsec) |
The amount of execution time elapsed so far for a statement in microseconds. This changes when the interface is refreshed. |
|
Index Hit Ratio |
A statement's ratio of physical reads to logical reads for index pages. |
|
Operation |
The operation currently being processed for an SQL statement. One of the following can be shown:
|
|
Temp Data Hit Ratio |
A statement's ratio of physical reads to logical reads for data pages read from temporary tablespaces. |
|
Temp Index Hit Ratio |
A statement's ratio of physical reads to logical reads for index pages read from temporary tablespaces. |
|
Temp XML Hit Ratio |
A statement's ratio of physical reads to logical reads for XML pages read from temporary tablespaces. |
|
Text |
The text of an SQL statement. For dynamic SQL statements, this is the SQL text associated with a package. |
|
Type |
The type for an SQL statement. Either DYNAMIC or STATIC is shown. |
|
XML Hit Ratio |
A statement's ratio of physical reads to logical reads for XML pages. |