Temporary tables only exist in your current session. In order to optimize a SQL statement that uses temporary tables, these tables need to be created in this session before you optimize. The User-Defined Temp Table window allows the creation of temporary tables to be use throughout modules that use the original sessions that is created as you logon.
Temporary tables created in the User-Defined Temp Table module are used in the SQL Optimizer, Index Expert, and SQL Scanner. They also can be viewed in the Database Explorer.
Temporary tables are dropped when you:
Exit from the program
Reconnect with the same or a different user logon
Drop it using the User-Defined Temp Table module
To create temporary tables
.
After the temp table is created you will notice an icon 
User-Defined Temp Table only supports SQL statements that create or modify temporary . These are:
DECLARE GLOBAL TEMPORARY TABLE
DROP TABLE
INSERT
UPDATE
DELETE
Temp tables created within the User-Defined Temp Table window can be used throughout SQL Optimizer.
After the user-defined temporary tables are created, you can view the SQL scripts used to create and populate the tables.
To review the DDL for creating temporary tables
Click
Select the Temp Table List tab.
Select the desire temporary table from the Temp Table list.
After the User-Defined temporary tables are created, you can delete them.
To delete temporary tables
Click
Select the Temp Table List tab.
If you want to remove all temp tables, click Drop All Table.
Otherwise, select the desire temp table from the Temp Table list and then click Drop Table.
When you copy a SQL statement from the Scanned SQL Viewer to the SQL Optimizer module, the Send to SQL Optimizer function automates this process for you. It copies the SQL statement, opens the SQL Optimizer window and inserts the SQL statement in the SQL Editor pane. When the SQL statement you are copying also includes a temporary table, the Send to SQL Optimizer function also copies the SQL statements for creating and populating the temporary table to the User-Defined Temp Table module.
Two items need to be considered when the SQL statements are automatically copied in the User-Defined Temp Table module. Is there SQL text already in the module? And does the temporary table already exist with the same or different data?
If SQL text is already entered into the User-Defined Temp Table module, you are prompted to save the current SQL to an ASCII file before it is overlaid with the new text. Saving the text enables you to use it again.
If a table with the same name already exists and you would like to recreate it with either a new definition or different data, then you must drop the table before executing the new SQL text. You can drop temporary table from the Temp Table List tab in the User-Defined Temp Table module.
