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
Select the SQL statement you want to optimize.
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.
SQL statements saved in the SQL Repository can be copied to the editable pane in other modules.
To copy SQL from the SQL Repository
From an editable pane in a module other than the SQL Repository, select File | Open SQL from SQL Repository.
Navigate through the tree structure and select the SQL statement.
To save SQL statements to the SQL Repository module
Select the specific SQL to save and click the Save button.
Select the location in which to save the SQL statements.
Only valid SQL statements can be added to the SQL Repository.
The SQL Repository only supports a single SELECT, INSERT, UPDATE, or DELETE SQL statement.
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
Right-click and select Create Benchmark Factory Import File.
Select the specific SQL statements that you want to save. Click OK.
Enter the filename and select the file location. Click Save.