Optimize SQL Statements

SQL Optimizer for IBM DB2 z/OS

previous page next page

Optimize SQL Statements

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.