SQL Optimizer for DB2 LUW 5.0

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

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.
Click $image\B_Optimize.gif$ 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 $image\B_AbortOptimize.gif$ .

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.

Add User-Defined SQL Alternatives

Once you have provided the original SQL statement in the SQL Optimizer window, you can create your own tweaked version of the statement and add it as a SQL alternative. You can do this either before or after you have rewritten the original SQL statement. You can then test run the user-defined SQL alternatives with the alternatives created by the SQL Rewrite and Generate Indexes functions. Or, you can simply test your alternative against the original SQL statement.

To insert your own SQL alternative

In the Run Time Pane on the SQL tab in the SQL Optimizer window, select a SQL alternative (or the original SQL statement) to use as a template.
Click $image\B_InsertUserDefinedSQL.gif$ .
In the SQL Text Pane, edit the SQL syntax to create your own version.
Click to retrieve the access plan for your SQL alternative. The plan is automatically parsed to determine whether its cost is identical to the cost of another alternative or the original SQL. If identical costs are found, SQL Optimizer displays a message.

Note: User-defined SQL alternatives are not checked to determine whether they are semantically equivalent to the original SQL. However, after you perform a Batch Run on the alternative, you can check the Records Returned and Remarks columns in Run Time pane to see whether the alternative's record count matches the record count for the original SQL.

To delete a user-defined SQL alternative

Select the alternative in the Run Time pane.
Select SQL | Delete User-Defined SQL.

Add Virtual Index Alternatives

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

Index Impact Analyzer

Index Usage Analyzer