SQL Optimizer for DB2 LUW 4.4.2 - User Guide

Retrieve Access Plan

The access plan is a combination of steps the optimizer chooses in order to execute a SQL statement. It is the ordered set of steps required to carry out the query with the operators chosen for each table.

To view only the access plan of the original SQL statement

From the SQL Information pane, click .

Note: The Rewrite SQL, Generate Indexes, Run for All Records, Run for First Record, and Run Result functions also retrieve the corresponding access plan.

Retrieve Run Result

Note: This feature is available for only SQL alternatives, not index-set alternatives.

To retrieve the SQL statement results from the database

In the SQL Optimizer window, select the SQL alternative, and click .

If the SQL statement contains a result set, the SQL Result window displays, showing all data retrieved. Otherwise, an information dialog box showing the number of rows affected displays.

In the SQL Result window, the first rows of the result set are displayed as soon as they are returned from the database. Therefore, the time it takes for you to see the result does not equal the time measured by the run- time functions to retrieve either the first record or all records run time. Run Result only retrieves enough records to display in the window; the complete result set is not retrieved until you view the last records.

To terminate the Run Result process

Click .

Commit or Rollback

Two types of action, commit or rollback, can be made after the Run Result has been executed for a UPDATE, INSERT, or DELETE SQL statement depending on which window you are in when you execute the Run Result function.

For the original SQL statement

When you retrieve run results for the original SQL statement (displayed as <Edit SQL>), you are prompted to commit or rollback for UPDATE, INSERT, and DELETE SQL statements.

For the SQL alternatives

For the SQL alternatives, all rows affected by UPDATE, INSERT, and DELETE SQL statements are rolled back automatically.

New Database Session

When you log on to DB2 LUW from SQL Optimizer, it connects to the database and maintains this database connection throughout the application execution. Another database session is required for the Run Result processing. This connection is established when the Run Result function is executed and dropped after all the records have been retrieved.

Retrieve Run Time

There are two functions used to retrieve the run time for an SQL statement: Run for All Records and Run for First Record.

On the SQL tab of SQL Optimizer window, select the SQL or index-set alternative for which you would like the run time.

First Record

To retrieve the time for the first record

Click .

To terminate the run time process for the first record

lick .

All Records

To retrieve the time for the all the records

Click .

To terminate the execution to retrieve the run time for all the records

Click .

All run time information is displayed in the SQL Run Time pane of the SQL Optimizer window.

New Database Session

When you log on to SQL Optimizer, it connects to the database and maintains this database connection throughout the application execution. Another database session is required for run time processing. This connection is established when the Run for First Record, Run for All Records, or Batch Run function is initiated and then dropped after the performance figures are acquired.

Test for Scalability

You can test a single SQL or index-set alternative to find out how it will perform under different amounts of data in your database in Quest's Benchmark Factory program (version 4.6 or later).

To send SQL statement to Benchmark Factory

1. Click to optimize the original SQL statement. The rewritten SQL versions display as alternatives in the Run Time pane..

2. Click to generate virtual index sets and add them as alternatives.

3. Click Test for Scalability .

4. Select the alternative you want to test.