SQL Optimizer for DB2 LUW 4.4.1 - User Guide

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 .

   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.

Open SQL from SQL Repository

SQL statements saved in the SQL Repository can be copied to the editable pane in other modules.

To copy SQL from the SQL Repository

1. From an editable pane in a module other than the SQL Repository, select File | Open SQL from SQL Repository.

2. Navigate through the tree structure and select the SQL statement.

3. Click Open.

Save SQL to SQL Repository

To save SQL statements to the SQL Repository module

1. Click .

2. Select the specific SQL to save and click the Save button.

3. Select the location in which to save the SQL statements.

Only valid SQL statements can be added to the SQL Repository.

Supported SQL Statements

The SQL Repository only supports a single SELECT, INSERT, UPDATE, or DELETE SQL statement. 

Create Benchmark Factory Import File

All the SQL statements can be saved in a text file from the Job Manager, the Scanned SQL Viewer, the SQL Repository, and the SQL Optimizer windows. These SQL statements can then be imported into Benchmark Factory program (version 4.6 or later).

To create the text file to import into Benchmark Factory

1. Right-click and select Create Benchmark Factory Import File.

2. Select the specific SQL statements that you want to save. Click OK.

3. Enter the filename and select the file location. Click Save.