Chat now with support
Chat with Support

SQL Optimizer for DB2 LUW 4.4.1 - User Guide

SQL Optimizer for IBM® DB2® LUW
UsageStatistics Getting Started Options SQL Scanner SQL Optimizer SQL Formatter SQL Inspector Database Explorer SQL Repository Index Impact Analyzer Index Usage Analyzer User-Defined Temp Table Editor Functions SQL Functions SQL Information and Functions Activity Log
Tutorials About us Copyright

Right Pane for Scenarios Analyzed Folder

In the Index Impact Analyzer pane, the following information is displayed in the right pane.

Summary button

Provides a summary of the DB2 LUW Cost and SQL classification for the SQL statements before and after the proposed indexes were created.

Chart button

Displays a bar chart which compares the accumulative total of the DB2 LUW Cost for the Baseline to each of the Scenarios. The Baseline shows the accumulative total of the DB2 LUW Cost of the SQL statements without the proposed indexes. The other bars show the accumulative total of the DB2 LUW Cost with the each proposed index set.

 

Right Pane for Scenario

In the Index Impact Analyzer window, when the Scenario folder is selected in the left pane, the following information is displayed in the right pane.

Properties button

Displays general information about the analysis and the index name.

Prognosis button

Displays 3 panes in the right section of the window.

Charts and Summary tabs (Top right pane)

The Chart tab displays charts of the comparison information for the access plans from before the analysis and during the analysis. These charts compare:

  • Total DB2 LUW cost for all SQL statements

  • Number of SQL statements with changes in the DB2 LUW cost

  • Number of SQL statements that use the indexes in the index set

  • Number of SQL statements whose access plan is different from the Baseline access plan

  • Number of SQL statements whose SQL classification changed

  • Number of SQL statements in each of the SQL classification categories

The Summary tab displays summarized statistics for the Scenario.

SQL Classification and Plan Change Comparisons (Middle right pane)

A grid displays the information about each access plan from before the analysis and during the analysis in a comparison format.

SQL Text and SQL Information tabs (Bottom right pane)

The SQL statement that is highlight in the middle right pane is the one that is displayed in this pane.

The SQL Text tab displays the text of the SQL statement.

The SQL Information tab displays two SQL Information panes, one for the SQL information without the index set and one for the SQL Information with the index set. The SQL Information panes contain buttons that show the access plans, DB2 LUW Optimized Text, SQL Classification, and any temporary tables used for the statement with and without the Index Set. As you review the access plans, the differences between the plan with and without the index set will be highlight in green to make it easier to find the differences.

Create Indexes button

Displays the script for creating the index or indexes.

To create the indexes

  1. Review the index name and, if desired, modify it.

  2. Click image\B_Execute.gif.

 

Right Pane for SQL Analyzed Folder

In the Index Impact Analyzer window, when the SQL Analyzed folder is selected in the left pane, the following information is displayed in the right pane.

Cost Summary button

Displays all the SQL statements and shows the DB2 LUW cost for the SQL statement in each Scenario so that you can compare the cost of each SQL statement to see if the cost increases or decreases with the index set. For each SQL statement, it displays the highest DB2 LUW cost in red and the lowest cost in green.

SQL Classification button

Displays all the SQL statements and shows the SQL Classification (Simple, Complex, and Problematic). The Baseline column shows the SQL Classification without the proposed indexes. The other columns show the SQL Classification with each proposed index set.

 

Right Pane for SQL Statements

In the Index Impact Analyzer window, when a SQL statement is selected in the left pane, the following information is displayed in the right pane.

Properties button

Displays general information about the analysis, information on the connection used to retrieve the access plans at the time of the analysis.

SQL Information button

Displays the SQL statement selected as well as two SQL Information panes that show the access plans, DB2 LUW Optimized Text, SQL Classification, and any temporary tables used for the statement with and without the Index Set. As you review the access plans, the differences between the plan with and without the index set will be highlight in green to make it easier to find the differences.

 

Related Documents