SQL Optimizer for DB2 LUW 4.3.1 - User Guide

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.

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

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

2. Click .

3. In the SQL Text Pane, edit the SQL syntax to create your own version.

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

1. Select the alternative in the Run Time pane.

2. Select SQL | Delete User-Defined SQL.