SQL Optimizer for DB2 LUW 4.3.1 - User Guide

Add Virtual Index Alternatives

Quest SQL Optimizer for IBM® DB2® LUWmaximizes SQL performance by automating the manual, time-intensive and uncertain process of ensuring that SQL statements are performing as fast as possible. SQL Optimizer analyzes, rewrites, and evaluates SQL statements within multiple database objects, files, or SQL statements captured by the DB2 Event Monitor. With SQL Optimizer, you can analyze and optimize all your problem SQL from multiple sources. SQL Optimizer also provides you a complete index optimization and plan change analysis solution, from index recommendations to simulated index impact analysis, through comparison of multiple SQL access plans.

SQL Optimizer provides you with the following main modules.

SQL Optimizer (including SQL Rewrite and Generate Indexes functions)

SQL Formatter

Database Explorer

SQL Scanner

SQL Inspector

SQL Repository

Index Impact Analyzer

Index Usage Analyzer

About the Generate Indexes Function

The Generate Indexes function in your SQL Optimizer session generates virtual index sets that can potentially improve the performance of your SQL. This function is part of Index Expert, an internal component that facilitates the management and testing of generated and user-defined virtual indexes in your SQL Optimizer session.

When you run the Generate Indexes function, Index Expert analyzes the tables referenced in the original SQL and generates index candidates. It groups these candidates into virtual index sets, each of which generates a unique plan that accesses the virtual indexes in the set.

When Generate Indexes completes, the generated virtual index sets display as alternatives, along with SQL alternatives generated during SQL Rewrite, on the SQL Optimizer window. From the window, you can view the virtual DB2 access plan and cost for any index-set alternative. The Batch Run process executes the index-set alternatives along with SQL alternatives, allowing you to compare run-time results among all alternatives to determine the best-performing version of your SQL. (During Batch Run, Index Expert physically creates the indexes to retrieve run times and then drops them.)

Additionally, you can use the Index Impact Analyzer to perform an impact analysis of a virtual index set on other SQL statements stored in the database system. This feature determines which SQL statements are impacted by the index set and identifies the index-set alternative that yields the highest performance gain with the least impact on the database system.

Index Expert Database Requirements

The Index Expert function requires DB2 LUW 7 or later to retrieve the indexes recommended by DB2. It requires DB2 LUW 8 or later to generate its own index-set recommendations (using an Artificial Intelligence engine), along with the DB2-recommended indexes. Additionally, Index Expert requires that the statistics be run in order to be able to estimate the size of the index.

Generate Index-Set Alternatives

The Generate Indexes function creates virtual indexes that can potentially improve performance of your original SQL statement.

This function is part of Index Expert, an internal component that facilitates the management and testing of generated and user-defined virtual indexes in your SQL Optimizer session. When you run Generate Indexes, Index Expert retrieves DB2-recommended virtual indexes, as well as generates its own virtual indexes. To generate index candidates, Index Expert analyzes the SQL syntax, relationships between tables, and data selectivity processes. It then combines the index candidates into one or more index sets, each set with a unique virtual access plan.

When the Generate Indexes process is complete, the resulting virtual index sets display as alternatives in the SQL Optimizer window. These alternatives are listed along with any SQL alternatives that were generated using the SQL Rewrite function. You can then test run and compare all alternatives to determine the best-performing version of your SQL.

To generate index-set alternatives for an SQL statement

1. Click to open a SQL Optimizer session.

2. On the SQL tab, enter the SQL statement for which you want to analyze for index alternatives.

   Note: To move an SQL statement from anther tool—such as Scanned SQL Viewer, SQL Formatter, Database Explorer, or SQL Comparer—into SQL Optimizer, select the statement within the tool, and click .

3. Click to generate new index-set alternatives for the original SQL statement.

To terminate the index generation process

Click .

Allow a few seconds to terminate all processes.

Include Index Recommendations from DB2

Index Expert uses DB2's SET CURRENT EXPLAIN MODE RECOMMENDED INDEXES command to retrieve DB2-recommended indexes for the SQL statement. These indexes are combined into a single index-set alternative labeled as DB2 LUW in the Run Time pane. To include DB2 index recommendations in the Generate Indexes process, select Generate Index Sets with DB2 LUW recommendations in the Index Expert Options settings.

Note: Consider setting the DB2 LUW MEM_UDF heap size to 1024 or higher if the index generation process does not produce DB2-recommended indexes.

Automatically Start the Batch Run

If you have selected the Automatically start Batch Run after generating index sets option, the Batch Run Criteria window automatically opens before the Generate Indexes process begins, allowing you to customize the Batch Run process.

View Index Generation Details

Once index generation is complete, the Index Generation Details window shows counts for generated, eliminated, and accepted index-set candidates. The final virtual index sets display as alternatives in the Run Time pane. These alternatives have the label Setx.

Tips:

• To create your own virtual index-sets, click . See Add Your Own Virtual Index Sets for more information.
• To analyze the impact of virtual index sets on other SQL in the database, click . See Analyze the Impact of New Indexes for more information.