The technique of creating temp tables to extract data from permanent tables is often used, because the existence of the temp table is session related and you will need to create them within SQL Optimizer. The User-Defined Temp Table window allows you to create temp tables to be use throughout SQL Optimizer.
To create or modify temporary tables in the User-Defined Temp Table module, the logon user needs the following privileges:
Connection to DB2 LUW 7 or above
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 appearing on the bottom right of the main window status. This icon will disappear when all User-Defined Temp Tables are dropped.
User-Defined Temp Table only supports SQL statements that create or modify temporary . These are:
DECLARE GLOBAL TEMPORARY TABLE
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
Select the Temp Table List tab.
Select the desire temporary table from the Temp Table list.