Declaring a global temporary table

 

You can create a temporary table for use with your current session. To create a temporary table, use the DECLARE GLOBAL TEMPORARY TABLE statement.

This temporary table does not appear in the system catalog and cannot be shared by other sessions. When you end your session, the rows of the table are deleted and the table is dropped.

The syntax of this statement is similar to CREATE TABLE, including the LIKE and AS clause.

For example, create a temporary table ORDERS:

DECLARE GLOBAL TEMPORARY TABLE ORDERS                (PARTNO   SMALLINT NOT NULL,
                DESCR    VARCHAR(24),
                QONHAND  INT)
       ON COMMIT DELETE ROWS 

This table is created in QTEMP. To reference the table using a schema name, use either SESSION or QTEMP. You can issue SELECT, INSERT, UPDATE, and DELETE statements against this table, the same as any other table. You can drop this table by issuing the DROP TABLE statement:

DROP TABLE ORDERS

 

Parent topic:

Data definition language

 

Related reference

DECLARE GLOBAL TEMPORARY TABLE