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.

Example: The following statement defines a created temporary table, TEMPPROD:

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.

Begin general-use programming interface information.

Example: The following statement defines a declared temporary table, TEMP_EMP. (This example assumes that you have already created the WORKFILE database and corresponding table space for the temporary table.)

DECLARE GLOBAL TEMPORARY TABLE  SESSION.TEMP_EMP
   (EMPNO  CHAR(6)        NOT NULL,
    SALARY DECIMAL(9, 2)          ,
    COMM   DECIMAL(9, 2));

End general-use programming interface information.

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.