Creating created temporary tables

Use created temporary tables when you need to store data for only the life of an application process, but you want to share the table definition. DB2® does not perform logging and locking operations for created temporary tables. Therefore, SQL statements that use these tables can execute queries efficiently.

About this task

Each application process has its own instance of the created temporary table.

You create the definition of a created temporary table using the SQL CREATE GLOBAL TEMPORARY TABLE statement.

Example: The following statement creates the definition of a table called TEMPPROD:

CREATE GLOBAL TEMPORARY TABLE TEMPPROD
  (SERIAL       CHAR(8)     NOT NULL,
   DESCRIPTION  VARCHAR(60) NOT NULL,
   MFGCOST      DECIMAL(8,2),
   MFGDEPT      CHAR(3),
   MARKUP       SMALLINT,
   SALESDEPT    CHAR(3),
   CURDATE      DATE        NOT NULL);

Example: You can also create this same definition by copying the definition of a base table (named PROD) by using the LIKE clause:

CREATE GLOBAL TEMPORARY TABLE TEMPPROD LIKE PROD;

The SQL statements in the previous examples create identical definitions for the TEMPPROD table, but these tables differ slightly from the PROD sample table PROD. The PROD sample table contains two columns, DESCRIPTION and CURDATE, that are defined as NOT NULL WITH DEFAULT. Because created temporary tables do not support non-null default values, the DESCRIPTION and CURDATE columns in the TEMPPROD table are defined as NOT NULL and do not have defaults.

After you run one of the two CREATE statements, the definition of TEMPPROD exists, but no instances of the table exist. To create an instance of TEMPPROD, you must use TEMPPROD in an application. DB2 creates an instance of the table when TEMPPROD is specified in one of the following SQL statements:

OPEN
SELECT
INSERT
DELETE

Restriction: You cannot use the MERGE statement with created temporary tables.

An instance of a created temporary table exists at the current server until one of the following actions occurs:

The application process ends.
The remote server connection through which the instance was created terminates.
The unit of work in which the instance was created completes.
When you run a ROLLBACK statement, DB2 deletes the instance of the created temporary table. When you run a COMMIT statement, DB2 deletes the instance of the created temporary table unless a cursor for accessing the created temporary table is defined with the WITH HOLD clause and is open.

Example: Suppose that you create a definition of TEMPPROD and then run an application that contains the following statements:

EXEC SQL DECLARE C1 CURSOR FOR SELECT * FROM TEMPPROD;
EXEC SQL INSERT INTO TEMPPROD SELECT * FROM PROD;
EXEC SQL OPEN C1;
⋮
EXEC SQL COMMIT;
⋮
EXEC SQL CLOSE C1;

When you run the INSERT statement, DB2 creates an instance of TEMPPROD and populates that instance with rows from table PROD. When the COMMIT statement runs, DB2 deletes all rows from TEMPPROD. However, assume that you change the declaration of cursor C1 to the following declaration:

EXEC SQL DECLARE C1 CURSOR WITH HOLD
  FOR SELECT * FROM TEMPPROD;

In this case, DB2 does not delete the contents of TEMPPROD until the application ends because C1, a cursor that is defined with the WITH HOLD clause, is open when the COMMIT statement runs. In either case, DB2 drops the instance of TEMPPROD when the application ends.

To drop the definition of TEMPPROD, you must run the following statement:

DROP TABLE TEMPPROD;

Parent topic: Creating tables