SQL Optimizer for DB2 LUW 4.4.1

Enter or edit the original SQL statement. (See Enter or Edit the Original SQL Statement.)
View the SQL alternatives that the Optimizer generates. (See Automatically Rewrite the Original SQL Statement.)
View the index-set alternatives that the Index Expert function generates. (See Generate Index-Set Alternatives.)
Create your own the SQL alternatives. (See Add User-Defined SQL Alternatives.)
View the index-set alternatives that you create. (See Add Your Own Virtual Index Sets.)
Review the access plan for the original SQL and for each SQL or index-set alternative.
Review the run-time statistics after all alternatives are batch-executed.

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 .

SQL Text Pane

Closed View the SQL Text pane

Use the SQL Text pane to do any of the following:

Enter or edit the original SQL statement syntax (when <Edit SQL> is selected in the Run Time pane). See Enter or Edit the Original SQL Statement for more information.
Display the SQL statement associated with the SQL alternative selected in the Run Time pane.
Display the DDL for the index-set alternative selected in the Run Time pane.
Enter SQL statement text for an SQL alternative you are creating. See Add User-Defined SQL Alternatives for more information.

When a SQL statement is sent to this pane from other modules, the optimization process begins automatically (if you have selected .

Notes:

All variables in the SQL text are displayed in red (default color), which indicates that a value and data type need to be defined before the SQL statement is executed. Other color coding in the SQL text is determined by the settings for syntax highlighting under the Editor tab of the Options window.
You can enter comments in the original SQL statement text, but not in the SQL for the alternatives you manually create.

SQL Information Pane

View the SQL Information pane

The SQL Information pane shows types of information for the original SQL or the SQL associated with the currently selected alternative. Use the buttons across the top of the pane to toggle between the views.

Note: Different SQL Optimizer modules use the SQL Information pane. The buttons available in the pane depend on the module you are using.

Button	Function	Description
$image\B_IP_VirtualAccessPlanTree.gif$	Virtual Access Plan Tree	Show the corresponding virtual access plan. Notes: No information displays if the SQL statement in invalid. For index-set alternatives, this is the assumed plan should the indexes actually exist.
$image\B_IP_VirtualOptimizedText.gif$	Virtual DB2 Optimized Text	If the SQL statement is valid, show the corresponding virtual DB2 optimized text. Note: No information displays if the SQL statement in invalid.
$image\B_IP_AccessPlanTree.gif$	Access Plan and Bound Access Plan	Display the access plan. Notes: No information displays if the SQL statement in invalid. For index-set alternatives, this is the actual plan used during Batch Execution (or anther execution function) when temporary indexes were physically created . In the Scanner SQL Viewer, both the bound plan and the current plan display when the SQL statement is from a package. Tip: Click (if available)to display detailed information for each row of the access plan.
$image\B_IP_OptimizedText.gif$	DB2 Optimized Text and Bound DB2 Optimized Text	Display the SQL statement reconstructed by the DB2 LUW optimizer after it retrieves the access plan. Note: In the Scanner SQL Viewer, the optimized text for both the bound plan and the current plan display.
$image\B_IP_Information.gif$	Information	Show any of the following, depending on the specific function using this pane: SQL statement type classification: Problematic, Complex, Simple, or Invalid SQL. This classification is dependent on the parameters set in the Options window. (SQL Scanner) For a SQL statement in a package, a comparison of the bound access plan and the current access plan. A database error message is displayed if SQL is classified as Invalid. (SQL Optimizer) Warning or alert information about the SQL statement if the transformation is based on table constraints or indexes. Note: Changes to table constraints and indexes might have a direct effect on the optimized SQL statement. (SQL Optimizer)Origin of the SQL statement. (SQL Scanner) SQL conversion applied. (SQL Scanner)Start position of the SQL statement in DDL and in TXT and SQL files. (SQL Scanner) For bound access plans, package information. (SQL Scanner) For bound access plans, new access plan information. (SQL Scanner) Connection information. Special register settings.
$image\B_IP_TempTable.gif$	Scanner Temp Table (SQL Scanner only)	Display the temporary table SQL statement assumed to create or modify the temporary table used on the scanned SQL statement if the SQL Scanner finds it in the source code.
$image\B_IP_CheckedSQL.gif$	Checked SQL (SQL Scanner only)	Display the date and time when the SQL statement was checked, its status and description, and the name of person who checked the SQL.

Run Time Pane

Closed View the Run Time pane

The Run Time pane in the SQL and Statistics pages in the SQL Optimizer window lists the current SQL and index-set alternatives generated for the original SQL. The following shows for each alternative.

Notes:

Execute Batch Run to capture a complete set of statistics.
Initially the alternatives are listed by DB2 LUW Cost in ascending order.

Item	Description
Cost Order	The order of alternatives is initially based on the ascending order of DB2 LUW Cost. The original SQL statement will always be the first item on the grid.
DB2 LUW Cost	The cost value provided by DB2 LUW as an estimate of performance. Note: The lower the DB2 LUW cost, the better the estimated performance of the SQL statement. However, the cost value should not be used as the actual indication of performance. It is best to execute the alternatives to find which performs the best in your database environment.
Elapsed Time (All Records)	The elapsed time for all records shows the actual elapsed time required to retrieve all records from the database. The calculation of the run time is based on the CPU time of the database server. Thus network traffic is excluded from the time. If the SQL statement is used to retrieve all the records from the database, such as reports or batch processes, the SQL statement with the best elapsed time for all records should be used as a criteria for selecting the alternative.
Times of Improvement (All Records)	The times of improvement shows how many times faster the alternative is for retrieving all records than the original SQL statement.
Records Returned (All Records)	The number of records indicates the total number of records influenced by the alternative. This figure should remain constant throughout the original and optimized SQL statements.
Elapsed Time (First n Records)	The elapsed time for the first record indicates the time it takes for the first record to be returned from the SQL statement. For some on-line retrieval screens, interactive applications, or processes that do not retrieve all records from the SQL statement at once, the best elapsed time for retrieving the first record should be used as a criteria for selecting the best alternative.
Times of Improvement (First n Records)	The times of improvement shows how many times faster the alternative is for retrieving the first record than the original SQL statement.
Records Returned (First n Records)	The first record retrieved, this figure should be 0 or 1.
Remarks	Information from the Batch Run is included in this column. It includes: If the alternative was terminated by the termination criteria. If the alternative was run more than once. If a database error occurred. If the number of records retrieved by the alternative does not match the number of records retrieved by the original SQL statement.

Please select your product:

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

Recommended Solutions for Your Problem

SQL Optimizer for DB2 LUW 4.4.1 - User Guide

SQL Page

SQL Text Pane

SQL Information Pane

Run Time Pane

Display or Hide Panes

SQL Text Pane

SQL Information Pane

Run Time Pane