SQL Optimizer for DB2 LUW 5.0 - User Guide

Compare Page

View the Compare Tab

The Compare tab in the SQL Optimizer window enables you easily to compare SQL text, access plans, and statistics between two alternatives or between an alternative and the original SQL.

The Compare tab consists of two major panes—Source of Comparison and Compare With. Each pane shows information for one alternative. Differences in syntax and access plans are highlighted in either pane.

Display or Hide Panes

Use the pane-control buttons to reorganize the panes:

To display panes vertically

Click .

To display panes horizontally

Click .

To display or remove SQL Text panes

Click .

To display or remove Access Plan panes

Click .

To display or remove Statistics panes

Click .

Execute Result Page

View the Execution Result tab

The Execution Result tab in the SQL Optimizer window compares the data returned by the original SQL with the data returned by any one of the SQL alternatives.

To compare all rows of data between the original SQL and an SQL alternative

1. Select Unlimited for Max rows to compare.
2. From the SQL list, select the SQL alternative that you want to compare with the original SQL.
3. Click .

To compare a limited number of rows between the original SQL and an alternative

1. Enter the number of rows in Max rows to compare.
2. From the SQL list, select the SQL alternative that you want to compare with the original SQL.
3. Click .

Comparison Grid

The grid in the top pane provides quick overview of returned-data differences. The numbers of returned columns and rows show whether the SQL alternative produces the same result as the original SQL statement. The displayed time shows how long it took to execute Batch Run or the Run Time, not the time it took to retrieve the data for the Execution Result tab.

Note: If the Data statistic shows that the returned data differs when you retrieve limited rows, do not assume that the alternative is returning wrong data. The difference can suggest that the alternative has returned data in an order different from the order returned by the original SQL. Two SQL statement are considered to be semantically equivalent if they retrieve the same data in any order (as long as the ORDER BY clause is not used in the SQL statement).

Comparison Functions

To refresh the data for the currently selected alternative

Click .

To clear all the data for original and all alternatives

Click .

To abort Compare SQL or Refresh SQL process

Click .

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