Creating declared temporary tables

Use declared temporary tables when you need to store data for only the life of an application process and do not need to share the table definition. The definition of this table exists only while the application process runs. DB2® performs limited logging and locking operations for declared temporary tables.

About this task

You create an instance of a declared temporary table by using the SQL DECLARE GLOBAL TEMPORARY TABLE statement. That instance is known only to the application process in which the table is declared, so you can declare temporary tables with the same name in different applications. The qualifier for a declared temporary table is SESSION.

Before you can define declared temporary tables, you must have a WORKFILE database that has at least one table space with a 32-KB page size.

To create a declared temporary table, specify the DECLARE GLOBAL TEMPORARY TABLE statement. In that statement, specify the columns that the table is to contain by performing one of the following actions:
  • Specify all the columns in the table.

    Unlike columns of created temporary tables, columns of declared temporary tables can include the WITH DEFAULT clause.

  • Use a LIKE clause to copy the definition of a base table, created temporary table, or view.

    If the base table, created temporary table, or view from which you select columns has identity columns, you can specify that the corresponding columns in the declared temporary table are also identity columns. To include these identity columns, specify the INCLUDING IDENTITY COLUMN ATTRIBUTES clause when you define the declared temporary table.

    If the source table has a row change timestamp column, you can specify that those column attributes are inherited in the declared temporary table by specifying INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES.

  • Use a fullselect to choose specific columns from a base table, created temporary table, or view.

    If you want the declared temporary table columns to inherit the defaults for columns of the table or view that is named in the fullselect, specify the INCLUDING COLUMN DEFAULTS clause. If you want the declared temporary table columns to have default values that correspond to their data types, specify the USING TYPE DEFAULTS clause.

Example: The following statement defines a declared temporary table called TEMPPROD by explicitly specifying the columns. 
DECLARE GLOBAL TEMPORARY TABLE TEMPPROD
  (SERIAL       CHAR(8)     NOT NULL WITH DEFAULT '99999999',
   DESCRIPTION  VARCHAR(60) NOT NULL,
   PRODCOUNT    INTEGER GENERATED ALWAYS AS IDENTITY,
   MFGCOST      DECIMAL(8,2),
   MFGDEPT      CHAR(3),
   MARKUP       SMALLINT,
   SALESDEPT    CHAR(3),
   CURDATE      DATE        NOT NULL);
Example: The following statement defines a declared temporary table called TEMPPROD by copying the definition of a base table. The base table has an identity column that the declared temporary table also uses as an identity column. 
DECLARE GLOBAL TEMPORARY TABLE TEMPPROD LIKE BASEPROD
  INCLUDING IDENTITY COLUMN ATTRIBUTES;
Example: The following statement defines a declared temporary table called TEMPPROD by selecting columns from a view. The view has an identity column that the declared temporary table also uses as an identity column. The declared temporary table inherits its default column values from the default column values of a base table on which the view is based. 
DECLARE GLOBAL TEMPORARY TABLE TEMPPROD
  AS (SELECT * FROM PRODVIEW)
  DEFINITION ONLY
  INCLUDING IDENTITY COLUMN ATTRIBUTES
  INCLUDING COLUMN DEFAULTS;

After you run a DECLARE GLOBAL TEMPORARY TABLE statement, the definition of the declared temporary table exists as long as the application process runs.

If you need to delete the definition before the application process completes, you can do that with the DROP TABLE statement. For example, to drop the definition of TEMPPROD, run the following statement: 
DROP TABLE SESSION.TEMPPROD;
DB2 creates an empty instance of a declared temporary table when it runs the DECLARE GLOBAL TEMPORARY TABLE statement. You can then perform the following actions:
  • Populate the declared temporary table by using INSERT statements
  • Modify the table using searched or positioned UPDATE or DELETE statements
  • Query the table using SELECT statements
  • Create indexes on the declared temporary table

The ON COMMIT clause that you specify in the DECLARE GLOBAL TEMPORARY TABLE statement determines whether DB2 keeps or deletes all the rows from the table when you run a COMMIT statement in an application with a declared temporary table. ON COMMIT DELETE ROWS, which is the default, causes all rows to be deleted from the table at a commit point, unless a held cursor is open on the table at the commit point. ON COMMIT PRESERVE ROWS causes the rows to remain past the commit point.

Example: Suppose that you run the following statement in an application program: 
EXEC SQL DECLARE GLOBAL TEMPORARY TABLE TEMPPROD
  AS (SELECT * FROM BASEPROD)
  DEFINITION ONLY
  INCLUDING IDENTITY COLUMN ATTRIBUTES
  INCLUDING COLUMN DEFAULTS
  ON COMMIT PRESERVE ROWS;
EXEC SQL INSERT INTO SESSION.TEMPPROD SELECT * FROM BASEPROD;
⋮
EXEC SQL COMMIT;
⋮

When DB2 runs the preceding DECLARE GLOBAL TEMPORARY TABLE statement, DB2 creates an empty instance of TEMPPROD. The INSERT statement populates that instance with rows from table BASEPROD. The qualifier, SESSION, must be specified in any statement that references TEMPPROD. When DB2 executes the COMMIT statement, DB2 keeps all rows in TEMPPROD because TEMPPROD is defined with ON COMMIT PRESERVE ROWS. When the program ends, DB2 drops TEMPPROD.

Parent topic: Creating tables
Related reference:
DECLARE GLOBAL TEMPORARY TABLE