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 that of the CREATE TABLE statement and can include the LIKE or 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