SQL Optimizer for DB2 LUW 5.0 - User Guide

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.

Create an Index Impact Analyzer

To create an Index Impact Analyzer

1. Click .

2. If no analysis exists in the Index Impact Analyzer, then the New Analysis wizard appears automatically. Otherwise, click .

3. Use the following pages in the New Analysis wizard to select the options for creating an analysis.

   Analyzer

   Selected SQL

   Index

New Analysis Wizard: Analyzer Page

In the Analyzer page, you can select to create an new Analyzer or to use an existing Analyzer with the SQL statements that are already in the SQL Repository for that Analyzer.

Select a new or existing Analyzer

Create a new Analyzer and select SQL to analyze

Specify to create an new Analyzer and to then select the SQL statements from the SQL Repository that you want to use in this new Analyzer.

Continue an existing Analyzer using the Analyzer's selected SQL

Specify to use an Analyzer that you have previously created with the SQL statements that are already stored with that Analyzer.

Analyzer Information