SQL Optimizer for DB2 ZOS 5.5.1

SQL Optimizer generates semantically equivalent alternatives with unique execution plans for your original SQL statement. You can then test run the alternatives to benchmark their performance. SQL Optimizer provides execution times and run-time statistics for each alternative to help you identify the best SQL statement for your database environment.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To optimize a SQL statement

Select the SQL Details tab in the main window.
Enter a SQL statement in the Alternative Details pane of the SQL Details tab.
Click to optimizer the SQL statement.

Select a connection to use. Review the following for additional information:

Select Connection	Description
Connection	Click to select a previously created connection. Tip: Click to open the Connection Manager to create a new connection. Connect to the Database
Select Schema and SQLID	Description
Schema	Click to select a schema for the connection.
SQLID	Click to select your SQLID.

When SQL Optimizer is finished generating alternatives, use one of the following methods to test run the SQL alternatives:

To test run all SQL alternatives, click .

To test run a single SQL alternative, select the alternative and click .

To test run two or more alternatives simultaneously, select the alternatives, click the arrow beside and select Test Run - Selected.

The execution statistics display in the Alternatives pane once the test run completes. Select an alternative to see more information in the Alternative Details and Execution Plan panes.

Test Run SQL Alternatives

Tips:

Click to stop the test run process.
You can select an executing SQL statement and click to abort only the selected statement.

To clear the SQL Rewrite window

To clear only the results and retain the original SQL statement, right-click within the Alternative Details pane and select Clear Optimization Results | Keep Original Scenario.

To clear the SQL Rewrite window, right-click within the Alternative Details pane and select Clear Optimization Results | Clear Original Scenario.

Related Topics

Automatically Optimize SQL Statements

Test Run SQL Alternatives

Specify Session Test Run Criteria

Use the Session Test Run Criteria dialog to specify options to use for the current test run of SQL statements in the Optimize SQL module. The options you specify are used for the current test run only. Global test run options for Optimize SQL are modified at Options | Optimize SQL | Test Run.

Execution Method and Run Time

Select from the following options:

Execution Method	Description
Maximum rows to be retrieved (records)	Select this checkbox and then enter the maximum number of rows to retrieve for a test run of all records.
Run Time Retrieval Method	Description
Run SQL options	Select one of the following options: Run all SQL twice if original SQL runs faster than (seconds)—Combines the Original SQL twice and all others once and the All SQL twice options into one option and allows you to determine (by the number of seconds a SQL statement runs) which option to use. The original SQL statement always runs twice. The SQL alternatives run twice if the original SQL statement runs in less time than the value specified. Otherwise, the SQL alternatives all run once. Original SQL twice and all others once—Caches data from a table into memory the first time you access it. The next time you access that data, it is already in memory so the following SQL statements run faster. To provide an accurate comparison, the first SQL statement runs twice but only the time from the second run is compared to the times for the other statements. All SQL twice—Executes all SQL statements twice to eliminate factors that can affect the accuracy of the results. If you recently executed a SQL statement, the information for that statement may be cached and the statement may execute faster. This option eliminates time variation caused by caching since it runs all SQL statements twice but only uses the second run time for comparison. All SQL once—Executes all SQL statements once. For long running SQL, you do not need to run any statement twice since the effect from caching diminishes over time.

Order and Termination

Select from the following options:

Execution Order	Description
Execution order for SQL	Select one of the following options: Intelligent order—Executes representative SQL statements with various plan cost according to SQL Optimizer's intelligence engine. Plan cost—Executes SQL statements in order of plan cost.
SQL Termination Criteria	Description
Terminate execution of SQL alternative if it runs longer than	Select one of the following options: Run time of fastest SQL—Cancels SQL statements that run longer than the current fastest run time. With this option, the first SQL statement runs and the time from that statement is used as the termination time for the next SQL statement. When a SQL statement runs faster than this time, the faster time is used as the new termination time. This percentage of the original SQL run time—Cancels SQL statements whose total elapsed time is the specified % of the total elapsed time for the original SQL statement. It terminates all SQL statements that run longer than the calculated termination time. User defined time (mins/secs)—Cancels SQL statements that run longer than a time you specify. Run without termination—Runs all SQL statements to completion regardless of run time.
Cancellation delay (seconds)	Adds a specified time to the termination time. It is important to factor a delay into the overall termination time to account for the time needed to send the SQL statement to the database server.

SQL to Execute

Execute Criteria

Description

Execute all alternatives with criteria

Select one of the following:

% of alternatives with lowest cost—Enter the percentage of SQL alternatives to execute with the lowest cost. You can also enter the minimum and maximum number of alternatives to execute.
Number of alternatives with lowest cost—Enter the number of SQL alternatives to execute with the lowest cost.
All alternatives with cost less than or equal to original SQL—Select to execute all SQL alternatives with a cost less than or equal to the cost of the original SQL statement.
All alternatives with cost less than the average of all alternatives—Select to execute all SQL alternatives with a cost less than the average cost of all alternatives.
All alternatives with cost less than the original SQL by percentage—Enter a percentage used to determine the SQL alternatives selected for execution. Optimize SQL executes alternatives with a cost that is the specified percentage lower than the cost of the original statement.
All alternatives with cost less than the original SQL by N times—Enter a value for N used to determine the SQL alternatives selected for execution. Optimize SQL executes alternatives with a cost N times lower than the original SQL statement.
All alternatives—Select to execute all SQL alternatives.

Tips:

Click to stop the test run process.
You can select an executing SQL statement and click to abort only the selected statement.

Open Optimizer SQL Sessions

Execution Method Options

Order and Termination Options

Automatically Optimize SQL Statements

Use the Auto Optimize function to perform the optimization and testing processes simultaneously. The function optimizes your original SQL statement by generating alternatives and starts testing once SQL Optimizer generates the first alternative. The Auto Optimize function reduces the time required to find the best alternative by not waiting until SQL Optimizer generates all alternatives before starting the testing process. You can stop the Auto Optimize function once you find a satisfactory SQL statement alternative.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To automatically optimize a SQL statement

Select the SQL Details tab in the main window.
Enter a SQL statement in the Alternative Details pane.
Click .

Tip: Click to stop the optimization and testing processes.

Optimize SQL Statements

Execution Plan

The execution plan displays the steps a database takes to execute a SQL statement. You can use the execution plan to determine if a statement is efficient.

Each step of the tree indicates how SQL Optimizer retrieves rows of data. The first line of the execution plan displays the SQL statement type, such as SELECT. The remaining lines represent an operation. The operations are numbered in the order of execution to make the plan easier to read.

The database executes each child operation before the parent operation. For some SQL statements, the database executes the parent operation once it retrieves a single row from the child operation. Other SQL statements require that the database retrieve all rows from the child operation before it executes the parent operation.

Execution Plan Window

The Execution Plan pane shows types of information for the original SQL or the SQL associated with the currently selected alternative.

Execution Plan Actions

Right-click in the Execution Plan window to select from the following actions:

Action	Description
Copy	Copies the execution plan to the clipboard.
Save	Saves the execution plan as a JPG file.
Print	Opens the print window so you can print the execution plan.
Style	Select option to display different plan details (operators, predicates, statistics).
View Plan	Allows you to change how the execution plan is displayed.
Step Description	Select this option to display a description of the step selected in the execution plan.
Animate Plan Steps	Highlights, one-by-one, the execution plan steps.
Plan Options	Opens the Execution Plan Options window so you can select which information is displayed in the execution plan and whether to display specific items in a column.
Help on RETURN	Displays the help text for the currently selected operation in the execution plan.
Help on Execution Plan	Opens online help for the Execution Plan window.