CREATE GLOBAL TEMPORARY TABLE

Create a temporary table to store the intermediate results of SQL transactions. The CREATE GLOBAL TEMPORARY TABLE statement creates a description of a temporary table at the current server.

Invocation

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

Authorization

Your privilege set must include at least one of the following:

The CREATETMTAB system privilege
The CREATETAB database privilege for any database
DBADM, DBCTRL, or DBMAINT authority for any database
SYSADM or SYSCTRL authority

You may require additional authorization if you use the LIKE keyword.

Syntax

Bold text indicates clauses or options that are supported only syntactically.

CREATE GLOBAL TEMPORARY TABLE table-name 
    (column-spec ,...) 
    [LIKE {table-name | view-name}] 
    [CCSID {ASCII | EBCDIC}]

column-spec:

column-name data-type 
    [FOR {BIT | MIXED | SBCS} DATA]
    [NOT NULL]

Parameters:

`table-name`	Names the temporary table. The name, including the implicit or explicit qualifier, must not identify a table, view, alias, synonym, or temporary table that exists at the current server. In the LIKE clause, `table-name` specifies that the columns of the table have the exact same name and description as the columns of the identified table.
`view-name`	Specifies that the columns of the table have exactly the same name and description as the columns of the identified view.
`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 types allowed and the rules followed are the same as those for the CREATE TABLE statement.

FOR {BIT | MIXED | SBCS} DATA

Specifies a subtype for a character string column. The subtypes allowed and the rules followed are the same as those for the CREATE TABLE statement.

data-type

Specifies the data type of the column. The data type can be a built-in data type or a distinct type.

built-in-data-type	Any built-in data type that can be specified for the CREATE TABLE statement with the exception that you cannot define a temporary table with a LOB or ROWID column.
distinct-type	Any distinct type except one that is based on a LOB or ROWID data type. The privilege set must implicitly or explicitly include the USAGE privilege on the distinct type.

NOT NULL

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

LIKE {`table-name` | `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 after LIKE must identify a table, view, or temporary table that exists at the current server. The privilege set must implicitly or explicitly 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 any column of the identified table or view has an attribute value that is not allowed for a column in a 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.
If any column of the identified table or view allows a default value other than null, then that default value is ignored and the corresponding column in the new temporary table has no default value. A default value other than null is not allowed for any column in a temporary table.