SQL Optimizer for DB2 LUW 5.0 - User Guide

Retrieve Run Time

There are two functions used to retrieve the run time for an SQL statement: Run for All Records and Run for First Record.

On the SQL tab of SQL Optimizer window, select the SQL or index-set alternative for which you would like the run time.

First Record

To retrieve the time for the first record

Click .

To terminate the run time process for the first record

lick .

All Records

To retrieve the time for the all the records

Click .

To terminate the execution to retrieve the run time for all the records

Click .

All run time information is displayed in the SQL Run Time pane of the SQL Optimizer window.

New Database Session

When you log on to SQL Optimizer, it connects to the database and maintains this database connection throughout the application execution. Another database session is required for run time processing. This connection is established when the Run for First Record, Run for All Records, or Batch Run function is initiated and then dropped after the performance figures are acquired.

Test for Scalability

You can test a single SQL or index-set alternative to find out how it will perform under different amounts of data in your database in Quest's Benchmark Factory program (version 4.6 or later).

To send SQL statement to Benchmark Factory

1. Click to optimize the original SQL statement. The rewritten SQL versions display as alternatives in the Run Time pane..

2. Click to generate virtual index sets and add them as alternatives.

3. Click Test for Scalability .

4. Select the alternative you want to test.

Convert Parameter Markers

Your SQL statement may have several question marks "?" as parameter markers.

To enable unique referencing of parameter markers

Click from the SQL Editor pane.

This will assign a unique number to all parameter markers within the SQL statement, for example:

Original SQL

SELECT *

  FROM employee  

 WHERE emp_id = ?

    OR emp_salary < ?

SELECT *

  FROM employee  

 WHERE emp_id = ?1

    OR emp_salary < ?2

SQL Information and Functions

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)

SQL Formatter

Database Explorer

SQL Scanner

SQL Inspector

SQL Repository

Index Impact Analyzer

Index Usage Analyzer