サポートと今すぐチャット
サポートとのチャット

SQL Optimizer for DB2 LUW 4.3.1 - User Guide

SQL Optimizer for IBM® DB2® LUW
About SQL Optimizer for IBM DB2 LUW 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 Information and Functions

Quest SQL Optimizer for IBM® DB2® LUWmaximizes 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 Information Pane

ClosedView 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.

 

Send SQL to SQL Optimizer

Performance improvement can be obtained by running the SQL Rewrite function on an SQL statement. This function transforms the SQL syntax into equivalent statements with different access plans.

Use the following procedure to send the selected SQL statement to the SQL Optimizer window, where the SQL Rewrite function is automatically run on the statement.

To send a SQL statement to the SQL Rewrite function from another module

  1. Select the single SQL statement you want to optimize.

  2. Click image\B_SendToSQLOptimizer.gif. The selected SQL statement is copied to the SQL Optimizer window, and the rewrite process is automatically started.

When you copy a SQL statement from the Scanned SQL Viewer, the following can occur:

  • If the SQL statement uses temporary tables and the DDL for creating the table was found when scanning, you are prompted to create the temporary tables through the User-Defined Temp Table window.

  • If the DB2 LUW Special Registers are different from current settings, you are prompted to set the special registers through the Special Register Settings window.

  • If your current schema does not match the one previously used to retrieve the access plan of the SQL statement, a message alerts you to change the schema from the box at the left-corner of the main window.

 

Send SQL to Index Expert

Use this procedure to send the SQL statement selected in the current tool to a SQL Optimizer session, where the Generate Indexes function automatically runs on the statement. The Generate Indexes function creates virtual index sets that you can test on the SQL statement to determine whether the SQL's performance improves.

See Generate Index-Set Alternatives for more information about the Generate Indexes function.

To execute Generate Indexes on an SQL statement in another module

  1. Select the SQL statement you want to optimize.

  2. Click image\B_CopyToIndexExpert.gif.

    The selected SQL statement is copied to the SQL Optimizer window, where the Generate Indexes function automatically starts.

When copying a SQL statement from the Scanned SQL Viewer,

  • If the SQL statement uses temporary tables and the DDL for creating the table was found when scanning, you are prompted to create the temporary tables through the User-Defined Temp Table window.

  • If the DB2 LUW Special Registers are different from current settings, then you are prompted to set the special registers through the Special Register Settings window.

  • If your current schema does not match the one previously used to retrieve the access plan of the SQL statement, a message alerts you to change the schema from the box at the left-corner of the main window.

 

関連ドキュメント

The document was helpful.

評価を選択

I easily found the information I needed.

評価を選択