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

Add Index Window

The Add Index window is used to create the DDL for creating of the indexes for the Index Impact Analysis.

Item

Description

Index name

Enter a name for the index. By default, the prefix of the index name is taken from the Index Expert settings in the Options window and then a random number is generated for the rest of the index name.

Note: It is best not to change the prefix for the index. The indexes are generated as virtual indexes. If the Impact Analysis process terminates abnormally and a virtual index is left on the database, it can be easily identified as a virtual index which was created by SQL Optimizer by the prefix.

Database

Select the database where the index is to be created from the database list.

Owner

Select the owner of the index from the drop-down list.

Table

Select the table where the index is to be created from the drop-down list.

Table columns

List of all the columns from the selected table.

Index columns

Move the columns to be indexed from the Table columns list by highlighting the column and clicking image\B_AddDatabaseObjects.gif.

Unique

Specify to create a unique index.

Clustered/Nonclustered

Select to create either a clustered or a nonclustered index.

 

View Index Impact Analysis Details

ClosedView the Index Impact Analysis Details Window

The Index Impact Analysis Details window displays information about the creation of the indexes and the retrieval of the access plans. The Index Impact Analysis Details window appears after the Index Impact Analysis process is completed unless the Show details on next generation checkbox in the Index Impact Analysis Details window is unchecked.

To view the Index Impact Analysis Details window

After an Index Impact Analysis is complete, select View | Last Index Impact Analysis Details when the Index Impact Analyzer window is active. 

Review this information to see if an error occurred during the retrieval of the access plan. Errors may occur if the selected SQL statements are from one schema and you have set another schema in the Schema list at the bottom left of the main window.

 

Add SQL to an Index Impact Analysis

After creating an Index Impact Analysis, you can add more SQL statements from the SQL Repository to it. This process will re-execute the Scenario.

To add SQL statements

  1. From the left pane, select the Analyzer in which you want to add the SQL.

  2. Right-click and select Add SQL.

  3. In the Add SQL window under the Select SQL to be added pane, check the SQL statements you want added.

  4. Under the Select Scenario to include SQL pane, check the Scenarios you want the SQL added to.

 

Add Scenario

After creating an Index Impact Analysis, you can add more Scenarios to it. A Scenario can be added in two ways:

To add a Scenario to an Analyzer

Method One

  1. In the left pane, right-click the Analyzer and select Add Scenario.

  2. Click the Index page and specify the information for the proposed new indexes.  

Method Two

  1. In the left pane, right-click an Analyzer and select New Analysis.

  2. Select the Continuing an existing Analyzer using the Analyzer’s selected SQL option.

  3. From the Select Analyzer box, click the Analyzer name.

  4. Click the Index page and specify the information for the proposed new indexes.

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating