SQL Optimizer for DB2 ZOS 5.6.2

Once SQL Optimizer generates execution plan alternatives, you can execute them to test each alternative or use Batch Run to test all alternatives simultaneously. Executing the alternatives retrieves run time statistics and identifies the best alternative 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.

Execute Function

To execute a single statement

Select the SQL Details tab in the main window.
Select a Plan Control session.
Select a single SQL statement in the Plans pane.
Click and select Execute (All Records) or Execute (First Record).

Batch Run Function

To execute selected statements

Select the SQL Details tab in the main window.
Select a Plan Control session.
In the Plans pane, select multiple SQL statements in one of the following ways:

To select a consecutive group of SQL statements, click the first SQL, press and hold SHIFT, and then click the last SQL.
To select non-consecutive SQL statements, press and hold CTRL, and then click each SQL that you want to select.

Click and select All Records or First Record, Batch Run Selected.

To execute all statements

Select the SQL Details tab in the main window.
Select a Plan Control session.
Click and select All Records or First Record, Batch Run Selected.

Session Batch Run Criteria

Every Plan Control session can have its own batch run settings. See the following topics for more information:

Tips:

You can change Batch Run settings globally on the Batch Run Options page. Test Run Settings Options
Click to stop the batch run process.

Batch Run Multiple Function

You can use the Batch Run Multiple Function to obtain statistics for statements with run times in the millisecond range. Statements with run times in the millisecond range can be skewed by other active processes because run time is based on a CPU's clock time. Multiple active processes can slow down CPU performance and result in a longer than normal run time for a statement. Use this function to execute selected or all statements multiple times. The statistics reported using this function are an average of the results from these multiple executions.

To execute selected statements multiple times

Select the SQL Details tab in the main window.
Select a Plan Control session.
Select multiple alternatives in the Plans pane.
Click and select All Records) or First Record, Batch Run Multiple Selected.
Enter the number of times you want each SQL statement executed.

To execute all statements multiple times

Select the SQL Details tab in the main window.
Select a Plan Control session.
Click and select All Records or First Record, Batch Run Multiple All.
Enter the number of times you want each SQL statement executed.

Retrieve Run Results

Optimize SQL > Execution Plan > Retrieve Run Results

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

To retrieve run results

Right-click the Alternative Details or SQL Text pane and select Run Result.
Click .

Tip: Click to copy the SQL statement to execute in another Questproduct.

Deploy Plan Guides

Optimize SQL > Execution Plan > Deploy Plan Guides

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

To deploy a plan guide

Select the SQL Details tab in the main window.
Select a Plan Control Session.
Click and select the alternative from the Select a Plan to Deploy list.

Note: You can also right-click the alternative you want to deploy as a plan guide in the Alternatives pane and select Deploy Plan Guide.

Review the following for additional information:

Deploy	Description
Select a plan to deploy	Click and select an execution plan alternative to deploy.
Performance Comparison	Description
Mark the plan as	Review the following for additional information: Enabled—Select whether to enable or disable this plan. Fixed—Select whether to deploy this plan as fixed or non-fixed. Not Auto-Purged—Select whether to auto-purge when it is not used.
Plan name	Enter a name for the plan.
Description	Enter a description for this plan.

Click .

Animate Execution Plans

Optimize SQL > Execution Plan > Animate Execution Plans

You can animate each step in the execution plan in the order SQL Optimizer executes them.

To animate the execution plan or cancel the animation

Right-click the execution plan and select Animate Plan Steps.

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem

SQL Optimizer for DB2 ZOS 5.6.2 - User Guide

Execute Execution Plan Alternatives