Chat now with support
Chat with Support

SQL Optimizer for DB2 LUW 4.4.2 - User Guide

SQL Optimizer for IBM® DB2® LUW
UsageStatistics Getting Started Options SQL Scanner SQL Optimizer SQL Formatter SQL Inspector Database Explorer SQL Repository Index Impact Analyzer Index Usage Analyzer User-Defined Temp Table Editor Functions SQL Functions SQL Information and Functions Activity Log
Tutorials About us Copyright

SQL Optimizer Functions

Below is a list of available functions within the SQL Optimizer window.

These functions are available only for the original SQL statement <Edit SQL> when displayed in the editable SQL Text window.

Button or Menu


SQL Menu

image\B_Optimize.gif image\B_AbortOptimize.gif

Optimize/Abort Optimize

SQL Menu


Show Plan

SQL Menu


Convert Parameter Markers

File Menu

Open SQL from SQL Repository


These functions are available for original SQL and its alternatives.

Button or Menu


SQL Menu

image\B_RunResult.gif image\B_AbortRunResut.gif

Run Result/Abort Run Result

SQL Menu

image\B_RunForFirstRecord.gif image\B_AbortRunForFirstRecord.gif

Run for First Record/Abort Run for First Record

SQL Menu

image\B_RunForAllRecords.gif image\B_AbortRunForAllRecords.gif

Run for All Records/Abort Run for All Records

Right-click Menu

Plan Help

SQL Menu

Open Optimized SQL

File Menu

Save SQL to SQL Repository

SQL Menu


Add User-Defined SQL

SQL & Right-click Menu


Test for Scalability

Right-click Menu

Create Benchmark Factory Import File


These functions are available only after the original SQL statement has been rewritten or virtual indexes have been generated.

Button or Menu


SQL Menu

image\B_BatchRun.gif image\B_AbortBatchRun.gif

Batch Run/Abort Batch Run

SQL Menu

Save Optimized SQL

Search Menu

Find SQL. . . /Find Next SQL

image\B_FirstSQL.gif image\B_PreviousSQL.gif

image\B_NextSQL.gif image\B_LastSQL.gif

First SQL/ Previous SQL/ Next SQL/ Last SQL

Navigate Menu

Go to SQL

Report Menu

Optimized SQL

View Menu

Show Optimization Details

View Menu

Show Batch Run Details

View Menu

Show Open Optimized SQL Details

View Menu

Show Refresh Plan Details


SQL Optimizer Window

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, or SQL statements captured by the DB2 Event Monitor. 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)

SQL Formatter

Database Explorer

SQL Scanner

SQL Inspector

SQL Repository

Index Impact Analyzer

Index Usage Analyzer


SQL Optimizer Window

ClosedView the SQL Optimizer Window

SQL Optimizer helps you optimize an SQL statement. That is, it rewrites the SQL to produce tweaked versions, or alternatives, of the original SQL and suggests indexes, all of which you can test to determine whether performance improves.

The SQL Optimizer window is the point at which you start the optimization process. This window shows the original SQL statement that you have entered manually or extracted from another facility, such as Toad or SQL Scanner. From this window, run either or both of these functions to optimize the SQL:

  • SQL Rewrite function to create virtual SQL alternatives
  • Index Expert function to create virtual index sets that display as alternatives as well

To help you determine the best-performing version of your SQL, the SQL Optimizer window lets you do the following:

  • Review the access plan, plan statistics, and optimized SQL for the original SQL and each alternative
  • Execute the original SQL and the index-set and SQL alternatives to find the fastest alternative
  • Compare SQL text, access plans, run-time statistics, costs, and other overhead of the original SQL to the index-set and SQL alternatives, as well as compare alternatives with each other
  • Compare the execution results of the original SQL to the index-set and SQL alternatives

The SQL Optimizer window consists of the following:


Access Plan Tab

Plan Statistics Tab

Summary Tab

Compare Tab

Execution Result Tab


  • You can optimize only one SELECT, DELETE, UPDATE, or INSERT SQL statement in the SQL Optimizer window.
  • To optimize SQL statements within database objects, SQL PL , or other application source code, use the SQL Scanner module to identify potential problematic SQL statements within the code. Then optimize each SQL statement in the SQL Rewrite function. 
  • In Toad, to open a SQL Optimizer session on the SQL in a view, trigger, or MQ table, click in the Create or Alter window for the object.
  • In Toad, to open a SQL Optimizer session on an SQL statement in a package, click on the SQL tab in the Database Explore or Object Explorer for the specific package.


SQL Page

ClosedView the SQL tab

The SQL tab in the SQL Optimizer window consists of three panes: SQL Text, SQL Information and Run Time.

On this tab, perform any of the following:

SQL Text Pane

Use the SQL Text pane to view the original SQL statement syntax or the SQL associated with a specific SQL alternative. For an index-set alternative, view the index DDL. You can also use this pane to enter or edit the text of the original SQL statement or to enter the text of an SQL alternative you are manually creating.

SQL Information Pane

Use the SQL Information pane to toggle between the access plan or the DB2 optimized text for the original SQL or an alternative. You can also view information about the original SQL.

Run Time Pane

The Run Time pane displays the SQL or index-set alternatives automatically generated or created manually on the original SQL. Use the SQL Navigation buttons to move between alternatives. When you perform a Batch Run, this list shows the run time statistics for retrieving all records and for retrieving the first n records for the original SQL and each alternative.

Display or Hide Panes

Use the pane-control buttons to reorganize the panes:

To display the SQL Text, SQL Information, and Run Time panes

Click .

To display only the SQL Text pane

Click .

To display the SQL Text and SQL Information panes

Click .

To display the SQL Text and Run Time panes

Click .


Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating