Tuning SQL statements in Spotlight on IBM DB2 LUW
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.
Tuning an SQL statement...
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.
...listed in the Top SQL drilldown
You can tune SQL statements listed in the Top SQL drilldown 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:
- SQL Optimizer for DB2
To start a tuning session
- Open the Top SQL drilldown.
-
Select one of the tabs shown as follows:
- Select the Current SQL tab to tune currently executing statements.
- Select the Static SQL tab to tune static SQL statements.
- Select the Dynamic SQL tab to tune dynamic SQL statements.
-
From the list in the selected tab, click the row for the statement you want to tune. The detail pane for this statement appears in the lower half of the SQL tab
-
In the upper left of the detail pane, click the SQL Tuning icon
.
SQL Optimizer for DB2 opens, showing the SQL statement in the product's main window.
- If you need instructions to proceed with the tuning session, click F1 for context-sensitive help.
...listed in the Client Application Analysis drilldown
You can tune SQL statements listed in the Client Application Analysis drilldown 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:
-
SQL Optimizer for DB2
To start a tuning session
-
Open the Client Application Analysis drilldown.
-
From the list of applications, click in the application whose currently executing SQL statement you want to tune.
A set of tabs, containing detail information about the application, displays in the lower half of the drilldown window.
-
Click the Unit of Work - SQL tab to examine the SQL statement currently executing for the application.
-
In the upper left of the detail pane of this tab, click the SQL Tuning icon
-
If you need instructions to proceed with the tuning session, click F1 for context-sensitive help.