Creating declared temporary tables

If you need to store data for the life of an application process, but you don't need a permanent, sharable description of the table, you can define and use a declared temporary table.

About this task

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.

Start of change When you create an index on a declared global temporary table, and the specified buffer pool does not match the default index buffer pool of the work file database, your privilege set must include the USE privilege for the buffer pool. Similarly, when you create an index on a declared global temporary table and the specified storage group does not match the default storage group of the work file database, your privilege set must include the USE privilege for the storage group. End of change

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.

Procedure

To create a declared temporary table:

Issue the DECLARE GLOBAL TEMPORARY TABLE statement.

Example

Begin general-use programming interface information. 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));

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. End general-use programming interface information.