8. SQL Statements : DECLARE GLOBAL TEMPORARY TABLE : Syntax
 
Share this page                  
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
Defines the subselect, as described in Select (interactive) (see page SELECT (interactive)). The subselect can be preceded by a common table expression (see page WITH (common_table_expression)).
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