Creation of temporary tables
Temporary tables can help you identify a small subset of rows from an intermediate result table that you want to store permanently. The two types of temporary tables are created temporary tables and declared temporary tables.
You can use temporary tables to sort large volumes of data and to query that data. Then, when you have identified the smaller number of rows that you want to store permanently, you can store them in a base table. The two types of temporary tables in DB2® are the created temporary table and the declared temporary table. The following topics describe how to define each type.
Created temporary table
Sometimes you need a permanent, shareable description of a table but need to store data only for the life of an application process. In this case, you can define and use a created temporary table. DB2 does not log operations that it performs on created temporary tables; therefore, SQL statements that use them can execute more efficiently. Each application process has its own instance of the created temporary table.
CREATE GLOBAL TEMPORARY TABLE TEMPPROD (SERIALNO CHAR(8) NOT NULL, DESCRIPTION VARCHAR(60) NOT NULL, MFGCOSTAMT DECIMAL(8,2) , MFGDEPTNO CHAR(3) , MARKUPPCT SMALLINT , SALESDEPTNO CHAR(3) , CURDATE DATE NOT NULL);
Declared temporary table
Sometimes you need to store data for the life of an application process, but you do not need a permanent, shareable description of the table. In this case, you can define and use a declared temporary table.
Unlike other DB2 DECLARE statements, DECLARE GLOBAL TEMPORARY TABLE is an executable statement that you can embed in an application program or issue interactively. You can also dynamically prepare the statement.
When a program in an application process issues a DECLARE GLOBAL TEMPORARY TABLE statement, DB2 creates an empty instance of the table. You can populate the declared temporary table by using INSERT statements, modify the table by using searched or positioned UPDATE or DELETE statements, and query the table by using SELECT statements. You can also create indexes on the declared temporary table. The definition of the declared temporary table exists as long as the application process runs.
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_EMP (EMPNO CHAR(6) NOT NULL, SALARY DECIMAL(9, 2) , COMM DECIMAL(9, 2));
If specified explicitly, the qualifier for the name of a declared temporary table, must be SESSION. If the qualifier is not specified, it is implicitly defined to be SESSION.
At the end of an application process that uses a declared temporary table, DB2 deletes the rows of the table and implicitly drops the description of the table.