SQL Reference Guide > SQL Reference Guide > SQL Statements > DECLARE GLOBAL TEMPORARY TABLE
Was this helpful?
DECLARE GLOBAL TEMPORARY TABLE
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The DECLARE GLOBAL TEMPORARY TABLE statement creates a temporary table.
The DECLARE GLOBAL TEMPORARY TABLE statement has the following format:
[EXEC SQL] DECLARE GLOBAL TEMPORARY TABLE [SESSION.]tablename
              (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.]tablename
              (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.
tablename
Defines the name of the temporary table.
ON COMMIT PRESERVE ROWS
Directs the DBMS Server to retain the contents of a temporary table when a COMMIT statement is issued.
WITH NORECOVERY
Suspends logging for the temporary table.
AS [WITH common_table_expression] subselect
Defines the subselect, as described in SELECT (interactive). The subselect can be preceded by a common table expression (see 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.
Valid parameters for the with_clause are:
• LOCATION = (locationname {, locationname})
• [NO]DUPLICATES
• ALLOCATION = initial_pages_to_allocate
• EXTEND = number_of_pages_to_extend
• [NO]MINMAX_SAMPLES
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
Last modified date: 11/09/2022