Quest SQL Optimizer for IBM® DB2® LUW maximizes SQL performance by automating the manual, time-intensive and uncertain process of ensuring that SQL statements are performing as fast as possible. SQL Optimizer analyzes, rewrites, and evaluates SQL statements within multiple database objects, files. With SQL Optimizer, you can analyze and optimize all your problem SQL from multiple sources. SQL Optimizer also provides you a complete index optimization and plan change analysis solution, from index recommendations to simulated index impact analysis, through comparison of multiple SQL access plans.
SQL Optimizer provides you with the following main modules.
SQL Optimizer (including SQL Rewrite and Generate Indexes functions)
The access plan is a combination of steps the DB2 LUW database optimizer chooses to execute a SQL statement. Each node represents how the database optimizer will physically retrieve rows of data from the database or how the data is prepared. By examining the access plan, you can see exactly how the database executes your SQL statement.
The Access Plan pane contains a right-click menu that allows you to perform the following functions:
|
Function |
Description |
|---|---|
|
|
Sends the access plan in its current view to the printer, to display on the screen (print preview), or to a file. |
|
Copy |
Copies the access plan to the clipboard. |
|
View Plan |
Changes how the access plan is displayed. |
|
Animated Plan Steps |
Highlights one-by-one the access plan steps. |
|
Plan Options |
Opens the Access Plan Options window so you can select the specific detailed information that is displayed in the access plan. You can also choose to display specific information in individual columns. |
|
Get Help on plan_step |
Displays the help text for the currently selected step in the access plan. |
|
Help on Access Plan |
Opens online help for the access plan. |
The access plan can be displayed in different ways to help you get a clear picture of the steps or detailed information about the steps that DB2 LUW is taking to execute a SQL statement.
For each location where the access plan is displayed, it will remember the display view option (As Tree Plan, As Plain Language Plan, As Graphic Plan, or As MS Graphic Plan) that you have selected from the right-click View Plan option. It will use that option the next time you view the access plan in the same location in the program.
To change how the access plan is displayed
Right-click the Access Plan pane and select View Plan.
Select one of the following display options:
As Tree Plan
As Plain Language Plan
As Graphic Plan
As MS Graphic Plan
Figure: Access Plan "As Tree Plan"
Figure: Access Plan "As Plain Language Plan"
Figure: Access Plan "As Graphic Plan"
Figure: Access Plan "As MS Graphic Plan"
The display of the Access Plan options can be customized for each window in which the access plan is displayed. You can select which elements of the access plan you would like displayed. You can also have the elements displayed in separate columns.
To customize the Access Plan window
Right-click the access plan and select Plan Options.
In the Visible column, select the elements you want displayed in the access plan text.
In the As Column column , select the elements for which you want a column added to the display in the Access Plan.
When you are viewing the access plan in plain language, you can also display any of the elements in a column.
To include elements when viewing the access plan in plain language
Note: The color and font settings for the access plan are set on the Plan tab in the Options window.
