Declare Global Temporary Table

Valid in: SQL, ESQL

The Declare Global Temporary Table statement creates a temporary table.

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]

• table_name

  Defines the name of the temporary table. The SESSION schema qualifier is optional, as described in SESSION Schema Qualifier.

• 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

  Defines the subselect, as described in Select (interactive).

• 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

    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

Description

The Declare Global Temporary Table statement creates a temporary table, also referred to as a session-scope table. Temporary tables are useful in applications that need to manipulate intermediate results and want to minimize the processing overhead associated with creating tables.

Temporary tables have the following characteristics:

• Temporary tables are visible only to the session that created them.
• Temporary tables are deleted when the session ends (unless deleted explicitly by the session). They do not persist beyond the duration of the session.
• Temporary tables can be created, deleted, and modified during an online checkpoint.

The SESSION schema qualifier on the table name is optional, as described in SESSION Schema Qualifier.

If the location parameter is omitted, the temporary table is located on the default database location (if the temporary table requires disk space). If the subselect is omitted, the temporary table is created as a heap.

When a transaction is rolled back, any temporary table that was in the process of being updated is dropped (because the normal logging and recovery processes are not used for temporary tables).

Note: If II_DECIMAL is set to comma, be sure that when SQL syntax requires a comma (such as list of table columns or SQL functions with several parameters), that the comma is followed by a space. For example:

select col1, ifnull(col2, 0), left(col4, 22) from ti:

SESSION Schema Qualifier

Two syntaxes can be used for declaring and referencing global temporary tables:

• With the SESSION Schema Qualifier

  If the DECLARE GLOBAL TEMPORARY TABLE statement defines the table with the SESSION schema qualifier, then subsequent SQL statements that reference the table must use the SESSION qualifier.

  When using this syntax, the creation of permanent and temporary tables with the same name is allowed.

• Without the SESSION Schema Qualifier

  If the DECLARE GLOBAL TEMPORARY TABLE statement defines the table without the SESSION schema qualifier, then subsequent SQL statements that reference the table can optionally omit the SESSION qualifier. This feature is useful when writing portable SQL.

  When using this syntax, the creation of permanent and temporary tables with the same name is not allowed.

Note: In both modes, a session table is local to the session, which means that two sessions can declare a global temporary table of the same name and they do not conflict with each other.

Note: Syntaxes cannot be mixed in a single session. For example, if the table is declared with SESSION the first time, all declarations must use SESSION.

Embedded Usage

In an embedded Declare Global Temporary Table statement:

• Host language variables can be used to specify constant expressions in the subselect of a create table...as statement.
• Locationname can be specified using a host language string variable.
• The preprocessor does not validate the syntax of the with_clause.
• Do not specify the declare global temporary table session statement within the declare section of an embedded program; place the statement in the body of the embedded program.

Permissions

All users are permitted to use this statement.

Restrictions

Temporary tables are subject to the following restrictions:

• Temporary tables cannot be used within database procedures.
• Temporary tables cannot be used in view definitions.
• Integrities, constraints, or user-defined defaults cannot be created for temporary tables. (The with|not null and with|not default can be specified.)
• A temporary table cannot be modified to use a different page size.
• Repeat queries referencing temporary tables cannot be shared between sessions.

All SQL statements can be used with temporary tables except the following:

• ALTER TABLE
• CREATE INTEGRITY
• CREATE PERMIT
• CREATE RULE
• CREATE SECURITY_ALARM
• CREATE SYNONYM
• CREATE VIEW
• GRANT
• HELP
• REVOKE
• SAVE
• SET JOURNALING
• SET LOCKMODE

The following commands cannot be issued with a temporary table name:

• auditdb
• copydb
• optimizedb
• statdump
• verifydb
• usermod
• genxml
• xmlimport

Related Statements

Create Table

Delete

Drop

Insert

Select (interactive)

Update

Examples: Declare Global Temporary Table

The following are Declare Global Temporary Table statement examples:

1. Create a temporary table.

   exec sql declare global temporary table
       emps
       (name char(20) , empno char(5))
       on commit preserve rows
       with norecovery,
       location = (personnel),
       [no]duplicates,
       allocation=100,
       extend=100;

2. Use a subselect to create a temporary table containing the names and employee numbers of the highest-rated employees.

   exec sql declare global temporary table
       emps_to_promote
       as select name, empno from employees
       where rating >= 9
       on commit preserve rows
       with norecovery