Syntax
The DECLARE GLOBAL TEMPORARY TABLE statement has the following format:
[EXEC SQL] DECLARE GLOBAL TEMPORARY TABLE [SESSION.]table_name
(column_name format {, column_name format})
ON COMMIT PRESERVE ROWS
WITH NORECOVERY
[with_clause]
To create a temporary table by selecting data from another table:
[EXEC SQL] DECLARE GLOBAL TEMPORARY TABLE [SESSION.]table_name
(column_name {, column_name})
AS subselect
ON COMMIT PRESERVE ROWS
WITH NORECOVERY
[with_clause]
SESSION
Allows the creation of permanent and temporary tables with the same name. The SESSION schema qualifier is optional, as described in
SESSION Schema Qualifier (see
SESSION Schema Qualifier).
table_name
Defines the name of the temporary table.
ON COMMIT PRESERVE ROWS
(Required) Directs the DBMS Server to retain the contents of a temporary table when a COMMIT statement is issued.
WITH NORECOVERY
(Required) Suspends logging for the temporary table.
AS subselect
with_clause
Specifies parameters on the WITH clause. Multiple WITH clause parameters must be specified as a comma-separated list. For details about these parameters, see
Create Table (see
CREATE TABLE).
Valid parameters for the with_clause are:
• LOCATION = (locationname {, locationname})
• [NO]DUPLICATES
• ALLOCATION = initial_pages_to_allocate
• EXTEND = number_of_pages_to_extend
For temporary tables created using a subselect, the following additional parameters can be specified in the with_clause:
• STRUCTURE = HASH | HEAP | ISAM | BTREE
• KEY = (columnlist)
• FILLFACTOR = n
• MINPAGES = n
• MAXPAGES = n
• LEAFFILL = n
• NONLEAFFILL = n
• COMPRESSION[ = ([[NO]KEY] [,[NO]DATA])] | NOCOMPRESSION
• PAGE_SIZE = n
• PRIORITY = cache_priority