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 [WITH common_table_expression] 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 page
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 [WITH common_table_expression] 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 page
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