SQL Optimizer for DB2 LUW 4.4.1 - User Guide

Use the SQL Optimizer

Quest SQL Optimizer for IBM® DB2® LUW maximizes 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

Enter the Original SQL Statement

You can optimize a single SELECT, DELETE, UPDATE, and INSERT SQL statement in the SQL Optimizer window. This optimization process includes running two major functions—SQL Rewrite and Generate Indexes—on this original statement to obtain alternatives on which you can test performance.

The following procedures describe how to obtain or edit this original SQL statement.

To enter the original SQL statement

1. Click .

2. Enter the SQL statement in the SQL Text pane on the SQL tab:

   • Type or paste the statement from a another location.

   • Open an existing file.

   • Send an SQL statement directly from another window using the Send SQL to the SQL Rewrite Function or Send SQL to the Generate Indexes Function function.

     Note: When you use either of these functions, the SQL Rewrite or Generate Indexes function automatically runs on the SQL once the SQL Optimizer window opens.

   • From Toad, open a SQL Optimizer session on the SQL in the Toad Editor or the SQL used in view, trigger, or MQ table DDL.

3. Perform any of these functions:

   Retrieve Access Plan

   Retrieve Run Time

   Retrieve Run Result

   Automatically Rewrite the Original SQL Statement

   Generate Index-Set Alternatives

   Retrieve the Run Time for a Group of Alternatives

To edit the original SQL statement

1. Select <Edit SQL> in the Batch Run pane.
2. Edit the SQL text in the SQL Text pane.

Notes:

• SQL Optimizer processes only a single SELECT, DELETE, UPDATE, or INSERT SQL statement.
• You can have only one SQL Optimizer window open at any one time. SQL Optimizer prompts you to save the original SQL statement if you attempt to open another file or create a new one.
• If the SQL statement uses temp tables, the temp tables must already exist before you can perform any function on the SQL statement. Create the temp tables by using the User-Defined Temp Table window.
• You can insert variables and comments within the original SQL statement.

Tip: To help construct a SQL statement, use the Editor functions: member lookup, argument lookup, auto correction, indent, outdent, comment, and uncomment functions.

Automatically Rewrite the Original SQL Statement

Use the SQL Rewrite function to generate alternatives to an SQL statement that you enter in the SQL Optimizer window or that you sent to SQL Optimizer from another tool, such the Scanned SQL Viewer, Toad Database Explorer, or SQL Formatter.

To rewrite an SQL statement

1. Provide the SQL statement.

   Note: In Toad, if you open a SQL Optimizer session on the SQL used in a view, trigger, or MQ table, the statement automatically displays in the SQL Optimizer window.

2. Click to rewrite the original SQL. The rewrite time is dependent on the complexity of the original SQL statement, the SQL Options applied, and the quota values set in the Options window. When the rewrite completes, each rewritten SQL version displays as a SQL alternative in the Run Time pane.

Warning: When you define the optimization quota values that, note that the higher the quota, the longer it may take to rewrite a complicated SQL statement.

Note: The SQL Rewrite process executes multiple transformation rules to produce a list of semantically equivalent SQL alternatives, each with a unique access plan. SQL options defined in the Options window are also applied to produce the list of SQL alternatives.

To abort the rewrite process

Click .

Allow a few seconds to terminate all processes.

Automatically Start the Batch Run

If you have selected the Automatically start Batch Run after rewriting option, the Batch Run Criteria window automatically opens before the rewrite process begins so you can select the settings for the Batch Run.

Tips:

• To generate virtual index sets that you can add to the list of SQL alternatives generated during SQL Rewrite, click . You can then include these index-set alternatives in the Batch Run process. See Generate Index-Set Alternatives for more information.
• To create your own virtual index sets that you can add to the list of SQL alternatives, click . See Add Your Own Virtual Index Sets for more information.

Rewrite Details

The Rewrite Details dialog is optional and can be displayed after the SQL Rewrite process completes. This dialog displays the number of semantically equivalent SQL statements investigated and the number of valid SQL alternatives produced. The dialog displays the following:

• Time calculations for the rewrite process.

• Number of semantically equivalent SQL statements investigated.

• Number of alternative access plans produced.

• How many rewrites were eliminated because they have identical access plans or DB2 LUW costs.

• Warning message if the Syntax Transformation Quota, Total SQL Options Quota, or Table Join Permutation Quota is reached.

Note: Use the Optimization (2) page on the SQL Rewrite tab in Options to define whether to eliminate alternative SQL statements when they have identical access plans or identical DB2 LUW costs. Eliminating the SQL based on identical access plan is more accurate but it can take longer to determine.

The Rewrite Details dialog is shown every time the rewrite process completes. To disable this window, clear the Show details on next optimization option in the dialog.

To view the Rewrite Details dialog at anytime

Select View | Show Optimization Details in the SQL Optimizer window.

If no alternatives are available, the Generate Indexes button is provided to start the Index Expert process of determining virtual index sets that might improve performance. See Generate Index-Set Alternatives for more information.