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

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.

 

Related Topics

Copy Execution Plans

Fill Missing Execution Plans

Get Execution Plans

About Execution Plans

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.

 

Related Topics

Copy Execution Plans

Fill Missing Execution Plans

Get Execution Plans

Review Execution Plans

The execution plan can be displayed in different ways to help you get more detailed information.

To change how the execution plan is displayed

  1. Right-click the plan and select View Plan.

  2. Select one of the following display options:

  • As Tree Plan

  • As Plain Language Plan

  • As Graphic Plan

  • As MS Graphic Plan

Tips: 

  • You can change the colors used to highlight different items for the current plan only by right-clicking the plan and selecting Plan Options. You can change colors for all execution plans in Options | General | Execution Plan. Execution Plan Options

  • You can use the toolbar to the right of the Execution Plan pane to change the details displayed.

 

Related Topics

Copy Execution Plans

Get Execution Plans

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

SQL Optimizer automatically retrieves the execution plan when you optimize or test run a SQL statement.

To get the execution plan

  1. Select the Optimize SQL tab.

  2. Enter a SQL statement in the Alternative Details pane.

  3. Click .

 

Related Topics

Copy Execution Plans

Related Documents