Performance improvement could be obtained through the SQL Optimizer window by transforming the SQL syntax into equivalent statements with different query plans.
If you are in a module that has a SQL Text pane, you can send a SQL statement to the SQL Optimizer.
To send a SQL statement to the SQL Optimizer and automatically start the optimization process
The SQL Optimizer only supports a single SELECT, SELECT INTO, INSERT, UPDATE or DELETE SQL statement.
When you optimize a SQL statement, create a temp table, or execute a SQL statement, you must have the right database selected and your logon must have access to the tables used in the SQL statement.
In the bottom left corner of the main window are find two drop-down lists: one for databases and the other for users. Select the appropriate database and user. This is equivalent to perform a USE and SETUSER commands.
You receive the following warning message if your current database and user do not match the one previously used to retrieve the query plan of the SQL statement. Click Yes to have the database and user automatically changed for you and continue with the optimization process.
"Your current connection is not the same as the scanned SQL statement. Do you want to change the database to 'database_name' and user to 'username' now to continue with the optimization?
If the SQL statement is used within a cursor declaration the SQL for cursor checkbox is automatically selected.
If the SQL statement uses a temporary table and the SQL Scanner found the CREATE TABLE or SELECT INTO statement for creating the table, then the User-Defined Temp Table window is automatically opened and the DDL for creating and populating is entered ready for you to execute it.
SQL statements can be executed in the SQL Worksheet. If you are in a module that has a SQL Text pane, you can copy a SQL statement to the SQL Worksheet.
To copy a SQL statement to the SQL Worksheet
Select the SQL statement you want to execute.
Click Copy to SQL Worksheet .
The selected SQL statement is copied to Editor pane of the SQL Worksheet window.
Performance improvement for a SQL statement could be obtained by creating indexes to reduce I/O consumption. By sending a SQL statement to the Index Advisor module, you can obtain recommendations for new indexes that could change SQL performance.
If you are in a module that has a SQL Text pane, you can send a SQL statement to the Index Advisor.
To send a SQL statement to the Index Advisor and automatically start the index recommendation process
Select the SQL statement you want to generate indexes for.
Click Send to Index Advisor .
Note: The Index Advisor only supports a single SELECT, INSERT, UPDATE and DELETE SQL statement.