DECLARE GLOBAL TEMPORARY TABLE statement

The DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for the current connection. These tables do not reside in the system catalogs and are not persistent. Temporary tables exist only during the connection that declared them and cannot be referenced outside of that connection. When the connection closes, the rows of the table are deleted, and the in-memory description of the temporary table is dropped.

Temporary tables are useful when:

• the table structure is not known before using an application.
• other users do not need the same table structure.
• data in the temporary table is needed while using the application.
• the table can be declared and dropped without holding the locks on the system catalog.

Syntax

DECLARE GLOBAL TEMPORARY TABLE table-Name
    { column-definition [ , column-definition ] * }
[ ON COMMIT {DELETE | PRESERVE} ROWS ] 
NOT LOGGED [ON ROLLBACK DELETE ROWS]
 

table-Name

Names the temporary table. If a schema-Name other than SESSION is specified, an error will occur (SQLSTATE 428EK). If the schema-Name is not specified, SESSION is assigned. Multiple connections can define declared global temporary tables with the same name because each connection has its own unique table descriptor for it.

Using SESSION as the schema name of a physical table will not cause an error, but is discouraged. The SESSION schema name should be reserved for the temporary table schema.

column-definition

See column-definition for CREATE TABLE for more information on column-definition. DECLARE GLOBAL TEMPORARY TABLE does not allow generated-column-spec in the column-definition.

Supported data-types are:

• BIGINT
• CHAR
• DATE
• DECIMAL
• DOUBLE PRECISION
• FLOAT
• INTEGER
• NUMERIC
• REAL
• SMALLINT
• TIME
• TIMESTAMP
• VARCHAR

Specifies the action taken on the global temporary table when a COMMIT operation is performed.

DELETE ROWS

All rows of the table will be deleted if no hold-able cursor is open on the table. This is the default value for ON COMMIT. If you specify ON ROLLBACK DELETE ROWS, this will delete all the rows in the table only if the temporary table was used. ON COMMIT DELETE ROWS will delete the rows in the table even if the table was not used (if the table does not have hold-able cursors open on it).

PRESERVE ROWS

The rows of the table will be preserved.

Specifies the action taken on the global temporary table when a rollback operation is performed. When a ROLLBACK (or ROLLBACK TO SAVEPOINT) operation is performed, if the table was created in the unit of work (or savepoint), the table will be dropped. If the table was dropped in the unit of work (or savepoint), the table will be restored with no rows.

ON ROLLBACK DELETE ROWS

This is the default value for NOT LOGGED. NOT LOGGED [ON ROLLBACK DELETE ROWS ]] specifies the action that is to be taken on the global temporary table when a ROLLBACK or (ROLLBACK TO SAVEPOINT) operation is performed. If the table data has been changed, all the rows will be deleted.

Examples

set schema myapp;
 
create table t1(c11 int, c12 date);
 
declare global temporary table SESSION.t1(c11 int) not logged;
-- The SESSION qualification is redundant here because temporary 
-- tables can only exist in the SESSION schema. 
 
declare global temporary table t2(c21 int) not logged; 
-- The temporary table is not qualified here with SESSION because temporary 
-- tables can only exist in the SESSION schema.
 
insert into SESSION.t1 values (1); 
-- SESSION qualification is mandatory here if you want to use 
-- the temporary table, because the current schema is "myapp."
 
select * from t1; 
-- This select statement is referencing the "myapp.t1" physical 
-- table since the table was not qualified by SESSION.
 
 

Note that temporary tables can only be declared in the SESSION schema. You should never declare a physical schema with the SESSION name.

The following is a list of DB2 UDB DECLARE GLOBAL TEMPORARY TABLE functions that are not supported by Derby:

• IDENTITY column-options
• IDENTITY attribute in copy-options
• AS (fullselect) DEFINITION ONLY
• NOT LOGGED ON ROLLBACK PRESERVE ROWS
• IN tablespace-name
• PARTITIONING KEY
• WITH REPLACE

Restrictions on Declared Global Temporary Tables

Temporary tables cannot be specified in the following statements:

• ALTER TABLE
• CREATE TRIGGER
• CREATE VIEW
• LOCK
• RENAME

Temporary tables cannot be specified in referential constraints.

There is no check constraints support for columns.

The following data types cannot be used with Declared Global Temporary Tables:

• BLOB
• CLOB
• LONG VARCHAR

Temporary tables cannot be referenced in a triggered-SQL-statement.

If a statement performing an insert, update, or delete to the temporary table encounters an error, all the rows of the table are deleted.

Restrictions Specific to Derby

Derby does not support the following on temporary tables:

• index support
• triggers and views on SESSION schema tables (including physical tables and temporary tables)
• LOCK TABLE
• constraints and primary keys
• generated-column-spec
• importing into temporary tables

Any statements referencing SESSION schema tables and views will not be cached.