DECLARE GLOBAL TEMPORARY TABLE

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

None required, unless the LIKE clause is specified when additional privileges might be required.

PUBLIC implicitly has the following privileges without GRANT authority for declared temporary tables:

• The CREATETAB privilege to define a declared temporary table in the database that is defined AS TEMP, which is the database for declared temporary tables.
• The USE privilege to use the table spaces in the database that is defined as TEMP.
• All table privileges on the table and authority to drop the table. (Table privileges for a declared temporary table cannot be granted or revoked.)

These implicit privileges are not recorded in the DB2 catalog and cannot be revoked.

Syntax

DECLARE GLOBAL TEMPORARY TABLE table-name
{
    (column-spec[,...]) |
    LIKE {table-name | view-name} [INCLUDING INDENTITY [COLUMN ATTRIBUTES]] |
    AS (subselect) DEFINITION ONLY [as-attribute]
}
    [CCSID {EBCDIC | ASCII}] 
    [ON COMMIT PRESERVE ROWS]

column-spec:

column-name data-type 
[NOT NULL]
[[WITH] DEFAULT [constant | USER | CURRENT SQLID | NULL]]
[GENERATED {ALWAYS | BY DEFAULT} [as-identity-clause}]

as-attribute:

[INCLUDING IDENTITY [COLUMN ATTRIBUTES]]
[INCLUDING [COLUMN] DEFAULTS]
[USING TYPE DEFAULTS]

as-identity-clause:

AS IDENTITY 
    [({START WITH {numeric-constant | 1} | INCREMENT BY {numeric-constant | 1} | 
    {CACHE 20 | NO CACHE | CACHE integer}}][,..])]

table-name

Names the temporary table. The qualifier, if specified explicitly, must be SESSION. If the qualifier is not specified, it is implicitly defined to be SESSION.

If a table, view, synonym, or alias already exists with the same name and an implicit or explicit qualifier of SESSION:

• The declared temporary table is still defined with SESSION.table-name. An error is not issued because the resolution of a declared temporary table name does not include the persistent and shared names in the XDB catalog tables.
• Any references to SESSION.table-name will resolve to the declared temporary table rather than to any existing SESSION.table-name whose definition is persistent and is in the XDB catalog tables.

PUBLIC implicitly acquires ALL PRIVILEGES on the table and authority to drop the table. These implicit privileges are not recorded in the XDB catalog and cannot be revoked.

column-spec

Defines the attributes of a column for each instance of the table. The number of columns defined must not exceed 750. The maximum record size must not exceed 32714 bytes. The maximum row size must not exceed 32706 bytes (8 bytes less than the maximum record size).

column-name

Names the column. The name must not be qualified and must not be the same as the name of another column in the table.

data-type

Specifies the data type of the column. The data type can be any built-in data type that can be specified for the CREATE TABLE statement except for a LOB (BLOB, CLOB, and DBCLOB) or ROWID type. The FOR subtype DATA clause can be specified as part of data-type.

NOT NULL

Specifies that the column cannot contain nulls. Omission of NOT NULL indicates that the column can contain nulls.

DEFAULT

The default value assigned to the column in the absence of a value specified on INSERT. Do not specify DEFAULT for a column that is defined AS IDENTITY (an identity column); XDB generates default values.

If DEFAULT is not specified, the default value for the column is the null value.

If DEFAULT is specified without a value after it, the default value of the column depends on the data type of the column, as follows:

A default value other than the one that is listed above can be specified in one of the following forms:

In a given column definition:

• NOT NULL and DEFAULT NULL cannot both be specified.
• DEFAULT cannot be specified for an identity column.
• Omission of NOT NULL and DEFAULT for a column other than an identity column is an implicit specification of DEFAULT NULL. For an identity column, it is an implicit specification of NOT NULL, and XDB generates default values.

GENERATED

Specifies that XDB generates values for the column. You must specify GENERATED if the column is to be considered an identity column (a column defined with the AS IDENTITY clause).

LIKE table-name or view-name

Specifies that the columns of the table have exactly the same name and description as the columns of the identified table or view. The name specified must identify a table, view, synonym, or alias that exists at the current server. The identified table must not be an auxiliary table or a declared temporary table.

The privilege set must include the SELECT privilege on the identified table or view.

This clause is similar to the LIKE clause on CREATE TABLE, but it has the following differences:

• If LIKE results in a column having a LOB data type, a ROWID data type, or distinct type, the DECLARE GLOBAL TEMPORARY TABLE statement fails.
• In addition to these data type restrictions, if any column has any other attribute value that is not allowed in a declared temporary table, that attribute value is ignored. The corresponding column in the new temporary table has the default value for that attribute unless otherwise indicated.

When the identified object is a table, the column name, data type, nullability, and default attributes are determined from the columns of the specified table; any identity column attributes are inherited only if the INCLUDING IDENTITY COLUMN ATTRIBUTES clause is specified. When the identified object is a view, only the column name, data type, and nullability attributes are determined from the columns of the specified view.

AS (subselect) DEFINITION ONLY

Specifies that the columns of the table are to have the same name and description as the columns that would appear in the derived result table of the subselect if the subselect were to be executed. The use of AS subselect is an implicit definition of n columns for the table, where n is the number of columns that would result from the subselect.

DEFINITION ONLY indicates that the subselect is not executed. Therefore, there is no result table with a set of rows with which to automatically populate the declared temporary table. However, you can use the INSERT INTO statement with the same subselect specified in the AS clause to populate the declared temporary table with the set of rows from the result table of the subselect.

The implicit definition includes all the attributes of the n columns of subselect that are applicable for a declared temporary table with the exception of these column attributes:

• The default value assigned to a column when a value is not specified on INSERT
• The identity attribute, if any

The behavior of these column attributes are controlled with the INCLUDING or USING TYPE DEFAULTS clauses, which are defined below.

If subselect results in a column having a LOB data type, a ROWID data type, or a distinct type, the DECLARE GLOBAL TEMPORARY statement fails.

If subselect results in other column attributes that are not applicable for a declared temporary table, those attributes are ignored in the implicit definition for the declared temporary table.

The implicitly defined columns of the declared temporary table inherit the names of the columns from the result table of the subselect. Therefore, a column name must be specified in the subselect for all result columns. For result columns that are derived from expressions, constants, and functions, the subselect must include the AS column-name clause immediately after the result column.

The subselect must not refer to host variables or include parameter markers (question marks).

INCLUDING IDENTITY COLUMN ATTRIBUTES

Specifies that the declared temporary table inherits the identity attribute, if any, of the columns resulting from subselect. In general, the identity attribute is copied if the element of the corresponding column in the table, view, or subselect is the name of a table column or the name of a view column that directly or indirectly maps to the name of a base table column with the identity property. The columns of the new table do not inherit the identity attribute in the following cases:

• The select list of the subselect includes multiple instances of an identity column name (that is, selecting the same column more than once).
• The select list of the subselect includes multiple identity columns (that is, it involves a join).
• The identity column is included in an expression in the select list.
• The subselect includes a set operation (union).

If INCLUDING IDENTITY is not specified, the declared temporary table will not have an identity column.

INCLUDING COLUMN DEFAULTS

Specifies that the declared temporary table inherits the default values of the columns resulting from subselect. A default value is the value assigned to a column when a value is not specified on an INSERT.

Do not specify INCLUDING COLUMN DEFAULTS, if you specify USING TYPE DEFAULTS.

If neither INCLUDING COLUMN DEFAULTS nor USING TYPE DEFAULTS is specified, the default values of the columns of the declared temporary table are either null or there are no default values. If the column can be null, the default is the null value; if the column cannot be null, there is no default value, and an error occurs if a value is not provided for a column on an INSERT for the declared temporary table.

USING TYPE DEFAULTS

Specifies that the default values for the declared temporary table depend on the data type of the columns that result from subselect, as follows:

Do not specify USING TYPE DEFAULTS, if you specify INCLUDING COLUMN DEFAULTS.

ON COMMIT commit-action ROWS

Specifies whether the contents of the table are to be deleted or preserved across a commit operation.

Example:

Define a declared temporary table with column definitions for an employee number, salary, commission, and bonus.

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_EMP
   (EMPNO   CHAR(6)        NOT NULL,
    SALARY  DECIMAL(9, 2),
    BONUS   DECIMAL(9, 2),
    COMM    DECIMAL(9, 2))
    CCSID EBCDIC
    ON COMMIT PRESERVE ROWS;