DECLARE GLOBAL TEMPORARY TABLE

Defines a temporary table for the current connection uniquely identified within the local member.

Syntax

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

Description

The DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for the current connection uniquely identified in the member.

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.

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 within the same JVM cannot define declared global temporary tables with the same name, because each connection attempts to create a local table of the same name, which is not allowed. Multiple active connections across members can create the table with the same name as temporary tables are process local.

Future versions of GemFire XD may support temporary tables with same name over multiple connections in a single member.

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 in CREATE TABLE for more information. DECLARE GLOBAL TEMPORARY TABLE does not allow generated-column-spec in the column-definition.

Data types

Supported data types are:

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

ON COMMIT

Unsupported in this release of GemFire XD.

DELETE ROWS

Unsupported in this release of GemFire XD.

PRESERVE ROWS

Unsupported in this release of GemFire XD.

NOT LOGGED

Note: This clause is mandatory in this release of GemFire XD.

Specifies the action taken on the global temporary table when a rollback operation is performed. On a ROLLBACK of the transaction, if the table was created within it, the table will be dropped. If the table was dropped in the transaction, the table will not be restored without the data.

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 operation is performed. If the table data has been changed, all the rows will be deleted.

Restrictions on declared Global Temporary Tables

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

GemFire XD does not support certain actions on temporary tables. Some of these features are specific to temporary tables and some are specific to GemFire XD.

Temporary tables cannot be specified in the following statements:

ALTER TABLE
CREATE INDEX
CREATE SYNONYM
CREATE TRIGGER
CREATE VIEW
GRANT
LOCK TABLE
RENAME
REVOKE

The following features cannot be used with temporary tables:

Synonyms, triggers and views on SESSION schema tables (including physical tables and temporary tables)
Caching statements that reference SESSION schema tables and views
Temporary tables cannot be specified in referential constraints and primary keys
Temporary tables cannot be referenced in a triggered-SQL-statement
Check constraints on columns
Generated-column-spec
Importing into temporary tables

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

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

BLOB
CHAR FOR BIT DATA
CLOB
LONG VARCHAR
LONG VARCHAR FOR BIT DATA
VARCHAR FOR BIT DATA
XML

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

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

Example

set schema  testSchema;

create table test (col1 int, col2 varchar(100));

-- The SESSION qualification is redundant here because temporary 
-- tables can only exist in the SESSION schema. 
declare global temporary table SESSION.test_temp(col1 int) not logged;

-- The temporary table is not qualified here with SESSION because temporary 
-- tables can only exist in the SESSION schema.
declare global temporary table check2(col2 int) not logged; 

-- SESSION qualification is mandatory here if you want to use 
-- the temporary table, because the current schema is "testSchema"
insert into SESSION.test_temp values (1); 

-- This select statement is referencing the "testSchema.test_temp" physical 
-- table since the table was not qualified by SESSION.
select * from test_temp;