Chat now with support
Chat with Support

SQL Optimizer for DB2 LUW 4.4 - Installation Guide

Index Impact Analyzer Tutorial

The Index Impact Analyzer allows you to analyze the impact of new indexes on the SQL statements in your database.

  1. Click image\B_IndexImpactAnalyzer.gif.

  2. Click image\B_NewAnalysis_IIA.gif. If this is your first time in the Index Impact Analyzer, the New Analysis window automatically opens.

Analyzer Tab

  1. Under the Analyzer tab in the New Analysis window, select to check the effects of index creation by either Creating a new Analyzer or Continuing an existing Analyzer.

  2. Enter a name and description.

  3. If you would like to create a folder, click image\B_AddFolder.gif.

Select SQL Tab

  1. Select the source of the SQL statements for Analysis: SQL Repository or SQL Scanner.

  2. Select the SQL statement(s) to add to the Analysis from your predefined SQL statements, or you may add a statement to this Analysis by clicking image\B_AddSQL.gif.

  3. Under the SQL Access Plans will be analyzed section, select the options for retrieving the access plans.

  • Using existing access plan saved with the SQL: This option uses the access plan that was saved with the SQL statement at the time that statement was saved to the SQL Repository, or scanned in the SQL Scanner.

  • Obtaining a new access plan under the current connection: This option retrieves the access plan with the current database logon. This current access plan is compared to the access plan that is retrieved after creating the new index(es).

Index Tab

  1. Name the Index Scenario and enter an optional description for easy reference.

  2. If the Index Impact Analyzer was called from the Index Expert, the index scenarios are automatically display and you can choose the index scenarios to include for analysis.

  3. To create the indexes, click image\B_AddIndex.gif.

  • Give the index a name for easy reference.

  • In the ‘Index based on’ section of this window, specify in which schema to create this index and specify the table that contains the column(s) to include in the index.

  • The columns of the table appear in the ‘Table columns’ window and can be selected to use in the index scenario, under the ‘Index columns’ window, by double clicking them or using the right arrow button.

  • Specify the Index type by selecting the option to have the index Unique and whether to Allow Reverse Scans.

  1. Click OK to perform the Index Impact Analysis.

Reviewing Index Impact Analysis Results

  1. After the analysis, you can see the overall results by clicking the Analyzer, and its related information, in the tree structure in the left pane and viewing the corresponding information in the panes to the right.

  2. In the right pane for the scenarios, click the Prognosis button to see overall performance changes and SQL details.

 

Related Topics

Index Impact Analyzer Overview

Index Impact Analyzer Window

Index Impact Analyzer Functions

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating