SQL Optimizer for DB2 LUW 4.4.1 - User Guide

SQL Optimizer for IBM® DB2® LUW

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

UsageStatistics

To prioritize enhancements in future releases, SQL Optimizer for IBM® DB2® LUW collects data about the use of its different features, and periodically, this data is communicated back to us. Initially, this usage data includes an IP address. Upon its receipt at a temporary server in the U.S.A., the IP address is removed, and then the anonymous data is aggregated before it is sent to our servers in California. Our product team analyses the aggregated data to understand our user community’s preferences and common practices. This analysis influences our future releases. Click here for more information on the data we collect and on our privacy policy.

• No personal information is collected
• You can stop participating at any time

To initiate participation in Product Improvement Program

Select Help | Product Improvement Program and select Yes, I want to participate

To cancel participation in Product Improvement Program

Select Help | Product Improvement Program and select No, thank you

Getting Started

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

User Logon Screen

When you start SQL Optimizer, a User Logon dialog box is displayed to allow connection to the DB2 database.

To logon to DB2 LUW

1. In the Login Name box, enter the user name required to connect to the database.

2. In the Password box, enter the password associated with the user name.

3. In the Database Alias box, enter the name of the database to which you want to connect. Database aliases can be defined in DB2 Client Configuration Assistant.

4. Click Connect to connect to the database server. After the database is successfully connected, information from the data dictionary is loaded into memory.

In DB2 LUW version 7 or earlier, if the current logon user does not satisfy all of the logon privileges, an icon will appear on the bottom status bar and the Connection Information dialog box will be displayed. Full details of what database privileges are required and their workaround are displayed on the Connection Information dialog box.

To view the Connection Information dialog box

Select Database | View Connection Information. 

Access Plan Table

When SQL Optimizer connects to the database, it determines whether the access plan tables exist. DB2 requires access plan tables to provide access plan detail. If these tables do not exist in the current schema, SQL Optimizer attempts to create the following tables according to the table definitions in the SQL script, C:\Program Files\SQLLIB\misc\EXPLAIN.DDL, provided by DB2 LUW.  

ADVICE_WORKLOAD

ADVICE_INDEX

EXPLAIN_OPERATOR

EXPLAIN_OBJECT

EXPLAIN_ARGUMENT

EXPLAIN_PREDICATE

EXPLAIN_STREAM

EXPLAIN_INSTANCE

EXPLAIN_STATEMENT

Related Topic

Connect to the Database