Chat now with support
Chat with Support

SQL Optimizer for DB2 ZOS 5.6.1 - User Guide

Welcome Optimize SQL Options Tutorial About us Third-party contributions Copyright

Execute Execution Plan Alternatives

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

  1. Select the SQL Details tab in the main window.
  2. Select a Plan Control session.
  3. Select a single SQL statement in the Plans pane.

  4. Click and select Execute (All Records) or Execute (First Record).

Batch Run Function

To execute selected statements

  1. Select the SQL Details tab in the main window.
  2. Select a Plan Control session.
  3. 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.
  1. Click and select All Records or First Record, Batch Run Selected.

To execute all statements

  1. Select the SQL Details tab in the main window.
  2. Select a Plan Control session.
  3. 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

  1. Select the SQL Details tab in the main window.
  2. Select a Plan Control session.
  3. Select multiple alternatives in the Plans pane.

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

  5. Enter the number of times you want each SQL statement executed.

To execute all statements multiple times

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

  4. Enter the number of times you want each SQL statement executed.

  

Related Topics  

Generate Execution Plan Alternatives

Deploy Plan Guides

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

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

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

  

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

  1. Select the SQL Details tab in the main window.
  2. Select a Plan Control Session.
  3. 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.

  4. 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.
  5. Click .

  

Related Topics  

Generate Execution Plan Alternatives

Execute Execution Plan Alternatives

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.

 

Related Topics

Copy Execution Plans

Fill Missing Execution Plans

Get Execution Plans

Related Documents