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

Create an Index Impact Analyzer

To create an Index Impact Analyzer

  1. Click image\B_IndexImpactAnalyzer.gif .

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

  1. 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

Item

Description

Name

If you are creating a new Analyzer, enter a name.

If you are using an existing Analyzer, select the from the Select Analyzer box.

Description

If you are creating a new Analyzer, enter the description for the analysis.

Last modified

Displays the last modified date and time.

Save location

Displays the folder in the tree where the Analyzer is saved. In the bottom pane, select the folder where you want to save the Analyzer. To create a new folder, click image\B_AddFolder.gif.

 

New Analysis Wizard: Select SQL Page

In the New Analysis wizard, the Select SQL page is used to select the SQL statements whose access plans you want to analyze in order to review the impact on their performance that the creation of new indexes would have.

Select SQL to check for performance changes

Select SQL from:

The SQL statements used in an analysis can be taken from the SQL Repository or the SQL Scanner. If you select SQL statements from the SQL Scanner, these statements are added to the SQL Repository.

Select the SQL statements for the analysis. If you select SQL statements from the SQL Scanner, the bottom portion of this section displays the SQL Repository folder so that you can select the folder where they will be stored.

SQL access plans to be analyzed

In order to analyze the impact the proposed indexes might have on the performance of the SQL statements, the Index Impact Analyzer compares the query before the indexes are created to the plans after the indexes are created. The access plans from before the analysis can be obtained two ways:

Using existing access plan saved with the SQL

This option uses the access plan that was saved with the SQL statement when it was saved in the SQL Repository or SQL Scanner.

Obtaining a new access plan under the current connection

This option retrieves the access plan with the current logon and the current database settings. This current access plan is compared to the access plan that is retrieved after the new indexes are created.

 

New Analysis Wizard: Index Page

In the New Analysis wizard, the Index page is used to specify the index(es) that are used for this analysis.

Access plans will be grouped under a Scenario

An individual Index Impact Analyzer consists of a group of stored SQL statements with the access plans called the " Baseline." The actual Impact Analysis is displayed in a "Scenario." A Scenario shows the comparison information for the stored SQL statements identifying the impact on the access plan that the creation of new index will have.

Item

Description

Name

Enter a name for the Scenario.

Description

Enter a description.

Add Index

Click image\B_AddIndex.gif.

Item

Description

Index Name

Enter a name for the index.

Schema

Select a name for the index.

Table

Select a table for the index.

Table columns

Double-click one or more columns.

Index type: Unique, Clustered, Allow Reverse Scans

Select the type of index.

 

Remove Index

Click image\B_RemoveIndex.gif.

After you have created the first Scenario, you can add more Scenarios.

 

Related Documents