The CREATE GLOBAL TEMPORARY TABLE statement creates a description
of a temporary table at the current server. Each session that selects
from a created temporary table retrieves only rows that the same session
has inserted. When the session terminates, the rows of the table associated
with the session are deleted.
Invocation
This statement can be embedded
in an application program or issued through the use of dynamic SQL
statements. It is an executable statement that can be dynamically
prepared only if DYNAMICRULES run behavior is in effect for the package
(SQLSTATE 42509).
Authorization
The privileges
held by the authorization ID of the statement must include either
DBADM authority, or CREATETAB authority in combination with further
authorization, as described here:
- One of the following privileges and authorities:
- USE privilege on the table space
- SYSADM
- SYSCTRL
- Plus one of these privileges and authorities:
- IMPLICIT_SCHEMA authority on the database, if the implicit or
explicit schema name of the table does not exist
- CREATEIN privilege on the schema, if the schema name of the table
refers to an existing schema
When defining a table using LIKE or a fullselect, the
privileges held by the authorization ID of the statement must also
include at least one of the following on each identified table or
view:
- SELECT privilege on the table or view
- CONTROL privilege on the table or view
- DATAACCESS authority
Syntax
>>-CREATE GLOBAL TEMPORARY TABLE--table-name-------------------->
.-,---------------------.
V |
>--+-(----| column-definition |-+--)-----------------------------+-->
+-LIKE--+-table-name1-+--+------------------+-----------------+
| '-view-name---' '-| copy-options |-' |
'-AS--(--fullselect--)--DEFINITION ONLY--+------------------+-'
'-| copy-options |-'
.-ON COMMIT DELETE ROWS---.
>--●--+-------------------------+--●---------------------------->
'-ON COMMIT PRESERVE ROWS-'
.-ON ROLLBACK DELETE ROWS-.
.-NOT LOGGED--+-------------------------+-.
>--+-----------------------------------------+--●--------------->
+-NOT LOGGED--ON ROLLBACK PRESERVE ROWS---+
'-LOGGED----------------------------------'
>--+---------------------+--●--+-------------------------+--●--><
'-IN--tablespace-name-' '-| distribution-clause |-'
column-definition
|--column-name--| data-type |--+--------------------+-----------|
'-| column-options |-'
data-type
|--+-| built-in-type |--+---------------------------------------|
'-distinct-type-name-'
built-in-type
|--+-+-SMALLINT----+-----------------------------------------------------------------+--|
| +-+-INTEGER-+-+ |
| | '-INT-----' | |
| '-BIGINT------' |
| .-(5,0)-------------------. |
+-+-+-DECIMAL-+-+--+-------------------------+------------------------------------+
| | '-DEC-----' | | .-,0-------. | |
| '-+-NUMERIC-+-' '-(integer-+----------+-)-' |
| '-NUM-----' '-,integer-' |
| .-(53)------. |
+-+-FLOAT--+-----------+--+-------------------------------------------------------+
| | '-(integer)-' | |
| +-REAL------------------+ |
| | .-PRECISION-. | |
| '-DOUBLE--+-----------+-' |
| .-(34)-. |
+-DECFLOAT--+------+--------------------------------------------------------------+
| '-(16)-' |
| .-(1 BYTE)------------. |
+-+-+-+-CHARACTER-+--+---------------------+----------+--+--------------------+-+-+
| | | '-CHAR------' | .-BYTE-. | | | (1) | | |
| | | '-(integer-+------+-)-' | '-------FOR BIT DATA-' | |
| | | .-BYTE-. | | |
| | '-+-VARCHAR----------------+--(integer-+------+-)-' | |
| | '-+-CHARACTER-+--VARYING-' | |
| | '-CHAR------' | |
| | .-(1M)-------------. | |
| '-+-CLOB------------------------+--+------------------+-----------------------' |
| '-+-CHARACTER-+--LARGE OBJECT-' '-(integer-+---+-)-' |
| '-CHAR------' +-K-+ |
| +-M-+ |
| '-G-' |
| .-(1)-------. |
+-+-GRAPHIC--+-----------+-------+------------------------------------------------+
| | '-(integer)-' | |
| +-VARGRAPHIC--(integer)--------+ |
| | .-(1M)-------------. | |
| '-DBCLOB--+------------------+-' |
| '-(integer-+---+-)-' |
| +-K-+ |
| +-M-+ |
| '-G-' |
| .-(1)-------. |
+-+-+-+-NCHAR-------------------+--+-----------+------+-------+-------------------+
| | | '-NATIONAL--+-CHAR------+-' '-(integer)-' | | |
| | | '-CHARACTER-' | | |
| | '-+-NVARCHAR-------------------------+--(integer)-' | |
| | +-NCHAR VARYING--------------------+ | |
| | '-NATIONAL--+-CHAR------+--VARYING-' | |
| | '-CHARACTER-' | |
| | .-(1M)-------------. | |
| '-+-NCLOB---------------------------+--+------------------+-' |
| +-NCHAR LARGE OBJECT--------------+ '-(integer-+---+-)-' |
| '-NATIONAL CHARACTER LARGE OBJECT-' +-K-+ |
| +-M-+ |
| '-G-' |
| .-(1M)-------------. |
+-+-BLOB----------------+--+------------------+-----------------------------------+
| '-BINARY LARGE OBJECT-' '-(integer-+---+-)-' |
| +-K-+ |
| +-M-+ |
| '-G-' |
'-+-DATE-------------------------+------------------------------------------------'
+-TIME-------------------------+
| .-(--6--)-------. |
'-TIMESTAMP--+---------------+-'
'-(--integer--)-'
column-options
|--●--+----------+--●------------------------------------------->
'-NOT NULL-'
>--+-------------------------------------------------------------------+--●--|
+-| default-clause |------------------------------------------------+
'-GENERATED--+-ALWAYS-----+--AS--IDENTITY--+----------------------+-'
'-BY DEFAULT-' '-| identity-options |-'
default-clause
.-WITH-.
|--+------+--DEFAULT--+--------------------+--------------------|
'-| default-values |-'
default-values
|--+-constant-------------------------------------------+-------|
+-datetime-special-register--------------------------+
+-user-special-register------------------------------+
+-CURRENT SCHEMA-------------------------------------+
+-NULL-----------------------------------------------+
+-cast-function--(--+-constant------------------+--)-+
| +-datetime-special-register-+ |
| +-user-special-register-----+ |
| '-CURRENT SCHEMA------------' |
+-EMPTY_CLOB()---------------------------------------+
+-EMPTY_DBCLOB()-------------------------------------+
+-EMPTY_NCLOB()--------------------------------------+
'-EMPTY_BLOB()---------------------------------------'
copy-options
|--●--+-------------------------------------+--●---------------->
| .-COLUMN-. |
'-+-INCLUDING-+--+--------+--DEFAULTS-'
'-EXCLUDING-'
.-COLUMN ATTRIBUTES-.
.-EXCLUDING IDENTITY--+-------------------+-.
>--+-------------------------------------------+--●-------------|
| .-COLUMN ATTRIBUTES-. |
'-INCLUDING IDENTITY--+-------------------+-'
distribution-clause
.-,-----------.
.-HASH-. V |
|--DISTRIBUTE BY--+------+--(----column-name-+--)---------------|
Notes:
- The FOR BIT DATA clause can be specified in any order with
the other column constraints that follow.
Description
- table-name
- Names the table. The name, including the implicit or explicit
qualifier, must not identify a table, view, nickname, or alias described
in the catalog. If a two-part name is specified, the schema name cannot
begin with 'SYS' (SQLSTATE 42939).
- column-definition
- Defines the attributes of a column of the temporary table.
- column-name
- Names a column of the table. The name cannot be qualified, and
the same name cannot be used for more than one column of the table
(SQLSTATE 42711).
A table can have the following:
- A 4K page size with a maximum of 500 columns, where the byte counts
of the columns must not be greater than 4 005.
- An 8K page size with a maximum of 1 012 columns, where the byte
counts of the columns must not be greater than 8 101.
- A 16K page size with a maximum of 1 012 columns, where the byte
counts of the columns must not be greater than 16 293.
- A 32K page size with a maximum of 1 012 columns, where the byte
counts of the columns must not be greater than 32 677.
For more details, see
"Row Size" in
"CREATE TABLE".
- data-type
- Specifies the data type of the column
- built-in-type
- Specifies a built-in data type. See "CREATE TABLE" for a
description of built-in-type.
An XML and
SYSPROC.DB2SECURITYLABEL data type cannot be specified for a created
temporary table.
- distinct-type-name
- For a user-defined type that is a distinct type. If a distinct
type name is specified without a schema name, the distinct type name
is resolved by searching the schemas on the SQL path (defined by the
FUNCPATH preprocessing option for static SQL and by the CURRENT PATH
register for dynamic SQL).
If a column is defined using a distinct
type, then the data type of the column is the distinct type. The length
and the scale of the column are respectively the length and the scale
of the source type of the distinct type.
- column-options
- Defines additional options related to the columns of the table.
- NOT NULL
- Prevents the column from containing null values. For specification
of null values, see NOT NULL in "CREATE TABLE".
- default-clause
- Specifies a default value for the column.
- WITH
- An optional keyword.
- DEFAULT
- Provides a default value in the event a value is not supplied
on INSERT or is specified as DEFAULT on INSERT or UPDATE. If a default
value is not specified following the DEFAULT keyword, the default
value depends on the data type of the column as shown in "ALTER
TABLE".
If the column is based on a column of a typed table, a specific
default value must be specified when defining a default. A default
value cannot be specified for the object identifier column of a typed
table (SQLSTATE 42997).
If a column is defined using a distinct
type, then the default value of the column is the default value of
the source data type cast to the distinct type.
If a column
is defined using a structured type, the default-clause cannot
be specified (SQLSTATE 42842).
Omission of DEFAULT from a column-definition results
in the use of the null value as the default for the column. If such
a column is defined NOT NULL, then the column does not have a valid
default.
- default-values
- Specific types of default values that can be specified are as
follows.
- constant
- Specifies the constant as the default value for the column. The
specified constant must:
- represent a value that could be assigned to the column in accordance
with the rules of assignment
- not be a floating-point constant unless the column is defined
with a floating-point data type
- be a numeric constant or a decimal floating-point
special value if the data type of the column is a decimal floating-point.
Floating-point constants are first interpreted as DOUBLE and then
converted to decimal floating-point if the target column is DECFLOAT.
For DECFLOAT(16) columns, decimal constants having precision greater
than 16 digits will be rounded using the rounding modes specified
by the CURRENT DECFLOAT ROUNDING MODE special register.
- not have nonzero digits beyond the scale of the column data type
if the constant is a decimal constant (for example, 1.234 cannot be
the default for a DECIMAL(5,2) column)
- be expressed with no more than 254 bytes including the quote characters,
any introducer character such as the X for a hexadecimal constant,
and characters from the fully qualified function name and parentheses
when the constant is the argument of a cast-function
- datetime-special-register
- Specifies the value of the datetime special register (CURRENT
DATE, CURRENT TIME, or CURRENT TIMESTAMP) at the time of INSERT, UPDATE,
or LOAD as the default for the column. The data type of the column
must be the data type that corresponds to the special register specified
(for example, data type must be DATE when CURRENT DATE is specified).
- user-special-register
- Specifies the value of the user special register (CURRENT USER,
SESSION_USER, SYSTEM_USER) at the time of INSERT, UPDATE, or LOAD
as the default for the column. The data type of the column must be
a character string with a length not less than the length attribute
of a user special register. Note that USER can be specified in place
of SESSION_USER and CURRENT_USER can be specified in place of CURRENT
USER.
- CURRENT SCHEMA
- Specifies the value of the CURRENT SCHEMA special register at
the time of INSERT, UPDATE, or LOAD as the default for the column.
If CURRENT SCHEMA is specified, the data type of the column must be
a character string with a length greater than or equal to the length
attribute of the CURRENT SCHEMA special register.
- NULL
- Specifies NULL as the default for the column. If NOT NULL was
specified, DEFAULT NULL may be specified within the same column definition
but will result in an error on any attempt to set the column to the
default value.
- cast-function
- This form of a default value can only be used with columns defined
as a distinct type, BLOB or datetime (DATE, TIME or TIMESTAMP) data
type. For distinct type, with the exception of distinct types based
on BLOB or datetime types, the name of the function must match the
name of the distinct type for the column. If qualified with a schema
name, it must be the same as the schema name for the distinct type.
If not qualified, the schema name from function resolution must be
the same as the schema name for the distinct type. For a distinct
type based on a datetime type, where the default value is a constant,
a function must be used and the name of the function must match the
name of the source type of the distinct type with an implicit or explicit
schema name of SYSIBM. For other datetime columns, the corresponding
datetime function may also be used. For a BLOB or a distinct type
based on BLOB, a function must be used and the name of the function
must be BLOB with an implicit or explicit schema name of SYSIBM.
- constant
- Specifies a constant as the argument. The constant must conform
to the rules of a constant for the source type of the distinct type
or for the data type if not a distinct type. If the cast-function is
BLOB, the constant must be a string constant.
- datetime-special-register
- Specifies CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP. The
source type of the distinct type of the column must be the data type
that corresponds to the specified special register.
- user-special-register
- Specifies CURRENT USER, SESSION_USER, or SYSTEM_USER. The data
type of the source type of the distinct type of the column must be
a string data type with a length of at least 8 bytes. If the cast-function is
BLOB, the length attribute must be at least 8 bytes.
- CURRENT SCHEMA
- Specifies the value of the CURRENT SCHEMA special register. The
data type of the source type of the distinct type of the column must
be a character string with a length greater than or equal to the length
attribute of the CURRENT SCHEMA special register. If the cast-function
is BLOB, the length attribute must be at least 8 bytes.
- EMPTY_CLOB(), EMPTY_DBCLOB(), or EMPTY_BLOB()
- Specifies a zero-length string as the default for the column.
The column must have the data type that corresponds to the result
data type of the function.
If the value specified is not valid, an error
is returned (SQLSTATE 42894).
- IDENTITY and identity-options
- For specification of identity columns, see IDENTITY and identity-options in "CREATE
TABLE".
- LIKE table-name1 or view-name or nickname
- Specifies that the columns of the table have exactly the same
name and description as the columns of the identified table (table-name1),
view (view-name), or nickname (nickname).
The name specified after LIKE must identify a table, view, or nickname
that exists in the catalog, or a declared temporary table. A typed
table or typed view cannot be specified (SQLSTATE 428EC). A protected
table cannot be specified (SQLSTATE 42962).
The use of LIKE is an
implicit definition of
n columns, where
n is the number
of columns in the identified table (including implicitly hidden columns),
view, or nickname. A column of the new table that corresponds to an
implicitly hidden column in the existing table is also defined as
implicitly hidden. The implicit definition depends on what is identified
after LIKE.
- If a table is identified, then the implicit definition includes
the column name, data type and nullability characteristic of each
of the columns of table-name1. If EXCLUDING
COLUMN DEFAULTS is not specified, then the column default is also
included.
- If a view is identified, then the implicit definition includes
the column name, data type, and nullability characteristic of each
of the result columns of the fullselect defined in view-name.
- If a nickname is identified, then the implicit definition includes
the column name, data type, and nullability characteristic of each
column of nickname.
Column default and identity column attributes may be included
or excluded, based on the copy-attributes clauses.
The implicit definition does not include any other attributes of the
identified table, view, or nickname. Thus the new table does not have
any unique constraints, foreign key constraints, triggers, or indexes.
The table is created in the table space implicitly or explicitly specified
by the IN clause, and the table has any other optional clause only
if the optional clause is specified.
When a table
is identified in the LIKE clause and that table contains a ROW CHANGE
TIMESTAMP column, the corresponding column of the new table inherits
only the data type of the ROW CHANGE TIMESTAMP column. The new column
is not considered to be a generated column. The table identified in
the LIKE clause must not contain a ROW CHANGE TIMESTAMP column that
is also defined as IMPLICITLY HIDDEN (SQLSTATE 42867).
- AS (fullselect) DEFINITION ONLY
- Specifies that the columns of the table have the same name and
description as the columns that would appear in the derived result
table of the fullselect if the fullselect were to be executed. The
use of AS (fullselect) is an implicit definition
of n columns for the created temporary table, where n is
the number of columns that would result from the fullselect.
The
implicit definition includes the following attributes of the
n columns
(if applicable to the data type):
- Column name
- Data type, length, precision, and scale
- Nullability
The following attributes are not included (the default
value and identity attributes can be included by using the
copy-options):
- Default value
- Identity attributes
- ROW CHANGE TIMESTAMP
The implicit definition does not include any other optional
attributes of the tables or views referenced in the fullselect.
Every
select list element must have a unique name (SQLSTATE 42711). The
AS clause can be used in the select clause to provide unique names.
The fullselect must not refer to host variables or include parameter
markers.
- copy-options
- These options specify whether to copy additional attributes of
the source result table definition (table, view, or fullselect).
- INCLUDING COLUMN DEFAULTS
- Column defaults for each updatable column of the source result
table definition are copied. Columns that are not updatable will not
have a default defined in the corresponding column of the created
table.
If LIKE table-name1 is specified,
and table-name1 identifies a base table,
created temporary table, or declared temporary table, then INCLUDING
COLUMN DEFAULTS is the default.
- EXCLUDING COLUMN DEFAULTS
- Column defaults are not copied from the source result table definition.
This
clause is the default, except when LIKE table-name is
specified and table-name identifies a base
table, created temporary table, or declared temporary table.
- INCLUDING IDENTITY COLUMN ATTRIBUTES
- If available, identity column attributes (START WITH, INCREMENT
BY, and CACHE values) are copied from the source's result table definition.
It is possible to copy these attributes if the element of the corresponding
column in the table, view, or fullselect is the name of a column of
a table, or the name of a column of a view which directly or indirectly
maps to the column name of a base table or created temporary table
with the identity property. In all other cases, the columns of the
new temporary table will not get the identity property. For example:
- The select list of the fullselect includes multiple instances
of the name of an identity column (that is, selecting the same column
more than once)
- The select list of the fullselect includes multiple identity columns
(that is, it involves a join)
- The identity column is included in an expression in the select
list
- The fullselect includes a set operation (union, except, or intersect).
- EXCLUDING IDENTITY COLUMN ATTRIBUTES
- Identity column attributes are not copied from the source result
table definition.
- ON COMMIT
- Specifies the action taken on the created temporary table when
a COMMIT operation is performed. The default is DELETE ROWS.
- DELETE ROWS
- All rows of the table will be deleted if no WITH HOLD cursor is
open on the table.
- PRESERVE ROWS
- Rows of the table will be preserved.
- LOGGED or NOT LOGGED
- Specifies whether operations for the table are
logged. The default is NOT LOGGED ON ROLLBACK DELETE ROWS.
- NOT LOGGED
- Specifies that insert, update, or delete operations against the
table are not to be logged, but that the creation or dropping of the
table is to be logged. During a ROLLBACK (or ROLLBACK TO SAVEPOINT)
operation:
- If the table had been created within a unit of work (or savepoint),
the table is dropped
- If the table had been dropped within a unit of work (or savepoint),
the table is recreated, but without any data
- ON ROLLBACK
- Specifies the action that is to be taken on the not logged created
temporary table when a ROLLBACK (or ROLLBACK TO SAVEPOINT) operation
is performed. The default is DELETE ROWS.
- DELETE ROWS
- If the table data has been changed, all the rows will be deleted.
- PRESERVE ROWS
- Rows of the table will be preserved.
- LOGGED
- Specifies that insert, update, or delete operations against the
table as well as the creation or dropping of the table are to be logged.
- IN tablespace-name
- Identifies the table space in which the created temporary table
will be instantiated. The table space must exist and be a USER TEMPORARY
table space (SQLSTATE 42838), over which the authorization ID of the
statement has USE privilege (SQLSTATE 42501). If this clause is not
specified, a table space for the table is determined by choosing the
USER TEMPORARY table space with the smallest sufficient page size
over which the authorization ID of the statement has USE privilege.
When more than one table space qualifies, preference is given according
to who was granted the USE privilege:
- The authorization ID
- A group to which the authorization ID belongs
- PUBLIC
If more than one table space still qualifies, the final choice
is made by the database manager. When no USER TEMPORARY table space
qualifies, an error is raised (SQLSTATE 42727). Determination of
the table space can change when:
- Table spaces are dropped or created
- USE privileges are granted or revoked
The sufficient page size of a table is determined by either the
byte count of the row or the number of columns. For more details,
see
"Row Size" in
"CREATE TABLE".
- distribution-clause
- Specifies the database partitioning or the way the data is distributed
across multiple database partitions.
- DISTRIBUTE BY HASH (column-name,...)
- Specifies the use of the default hashing function on the specified
columns, called a distribution key, as the distribution method
across database partitions. The column-name must be an unqualified
name that identifies a column of the table (SQLSTATE 42703). The same
column must not be identified more than once (SQLSTATE 42709). No
column whose data type is BLOB, CLOB, DBCLOB,
XML, distinct type based on any of these types, or structured
type can be used as part of a distribution key (SQLSTATE 42962).
If
this clause is not specified, and the table resides in a multiple
partition database partition group with multiple database partitions,
the distribution key is defined as the first column whose data type
is valid for a distribution key.
If none of the columns satisfies
the requirements for a default distribution key, the table is created
without one. Such tables are allowed only in table spaces that are
defined on single-partition database partition groups.
For
tables in table spaces that are defined on single-partition database
partition groups, any collection of columns with data types that are
valid for a distribution key can be used to define the distribution
key. If this clause is not specified, no distribution key is created.
Notes
- A user temporary table space must exist before a created temporary table can be created (SQLSTATE
42727).
- Data
row compression is enabled for a created temporary table. When the
database manager determines that there is a performance gain, table
row data with XML documents stored inline in the base table object is
compressed. However, data compression of the XML storage object of
a created temporary table is not supported.
- Index
compression is enabled by default for indexes that are created on created temporary tables.
Compression will be shown as on, but indexes will not be compressed if the correct license (IBM DB2
Storage Optimization Feature) is not applied.
- Instantiation and termination: For the explanations
that follow, P denotes a session and T is a created temporary table
in the session P:
- An empty instance of T is created as a result of the first reference
to T that is executed in P.
- Any SQL statement in P can make reference to T and any reference
to T in P is a reference to that same instance of T.
- Assuming that the ON COMMIT DELETE ROWS clause was specified implicitly
or explicitly, then when a commit operation terminates a unit of work
in P, and there is no open WITH HOLD cursor in P that is dependent
on T, the commit includes the operation DELETE FROM T.
- When a rollback operation terminates a unit of work or a savepoint
in P, and that unit of work or savepoint includes a modification to
T:
- If NOT
LOGGED was specified, all rows from T are deleted unless ON ROLLBACK
PRESERVE ROWS was also specified
- If NOT LOGGED was not specified, the changes to T are undone
- If NOT
LOGGED was specified and an INSERT, UPDATE or DELETE statement fails
during execution (as opposed to a compilation error), all rows from
T are deleted.
- When a rollback operation terminates a unit of work or a savepoint
in P, and that unit of work or savepoint includes the creation of
T, then the rollback includes the operation DROP TABLE T.
- If a rollback operation terminates a unit of work or a savepoint
in P, and that unit of work or savepoint includes the drop of a created
temporary table T, then the rollback will undo the drop of the table.
If NOT LOGGED was specified, then the table will also have been emptied.
- When the application process that referenced T terminates or disconnects
from the database, the private instance of T is dropped and its instantiated
rows are destroyed.
- When the connection to the server at which T was referenced terminates,
the private instance of T is dropped and its instantiated rows are
destroyed.
- Restrictions on the use of created temporary tables:
Created temporary tables cannot:
- Be specified in an ALTER, LOCK, or RENAME statement (SQLSTATE
42995).
- Be specified in referential constraints (SQLSTATE 42995).
- Syntax alternatives: The following
are supported for compatibility with previous versions of DB2® and with other database products.
These alternatives are non-standard and should not be used.
- The PARTITIONING KEY clause can be specified in place of the DISTRIBUTE
BY clause
The following syntax is accepted as the default behavior:
- CCSID ASCII
- CCSID UNICODE
Example
Example 1: Create a temporary
table, CURRENTMAP. Name two columns, CODE and MEANING, both of which
cannot contain nulls. CODE contains numeric data and MEANING has character
data.
CREATE GLOBAL TEMPORARY TABLE CURRENTMAP
(CODE INTEGER NOT NULL,
MEANING VARCHAR(254) NOT NULL)
Example
2: Create a temporary table, TMPDEPT.
CREATE GLOBAL TEMPORARY TABLE TMPDEPT
(TMPDEPTNO CHAR(3) NOT NULL,
TMPDEPTNAME VARCHAR(36) NOT NULL,
TMPMGRNO CHAR(6),
TMPLOCATION CHAR(16) )