DECLARE GLOBAL TEMPORARY TABLE statement
The DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for the current session.
The declared temporary table description does not appear in the system catalog. It is not persistent and cannot be shared with other sessions. Each session that defines a declared global temporary table of the same name has its own unique description of the temporary table. When the session terminates, the rows of the table are deleted, and the description of the temporary table is dropped.
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.
Authorization
- USE privilege on the USER TEMPORARY table space
- DBADM authority
- SYSADM authority
- SYSCTRL authority
- SELECT privilege on the table or view
- SELECTIN privilege on the schema containing the table or view
- CONTROL privilege on the table or view
- DATAACCESS authority on the schema containing the table or view
- DATAACCESS authority
Syntax
- 1 The FOR BIT DATA clause can be specified in any order with the other column constraints that follow. The FOR BIT DATA clause cannot be specified with string units CODEUNITS32 (SQLSTATE 42613).
Description
-
table-name
- Names the temporary table. The qualifier, if specified explicitly, must be SESSION, otherwise an
error is returned (SQLSTATE 428EK). If the qualifier is not specified, SESSION is implicitly
assigned.
Each session that defines a declared temporary table with the same table-name has its own unique description of that declared temporary table. The WITH REPLACE clause must be specified if table-name identifies a declared temporary table that already exists in the session (SQLSTATE 42710).
It is possible that a table, view, alias, or nickname already exists in the catalog, with the same name and the schema name SESSION. In this case:- A declared temporary table table-name may still be defined without any error or warning
- Any references to SESSION.table-name will resolve to the declared temporary table rather than the SESSION.table-name already defined in the catalog.
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 row-organized table can have the following attributes:
- A 4K page size with a maximum of 500 columns, where the byte counts of the columns must not be greater than 4005.
- An 8K page size with a maximum of 1012 columns, where the byte counts of the columns must not be greater than 8101.
- A 16K page size with a maximum of 1012 columns, where the byte counts of the columns must not be greater than 16,293.
- A 32K page size with a maximum of 1012 columns, where the byte counts of the columns must not be greater than 32,677.
A column-organized table can have a maximum of 1012 columns, regardless of its page size. The byte count of each column must not exceed 32,677. Extended row size support does not apply to column-organized tables.
A created temporary table cannot have a row-begin column, row-end column, or a transaction-start-ID column.
For more details, see
Row Size
in CREATE TABLE statement.
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.A SYSPROC.DB2SECURITYLABEL data type cannot be specified for a declared temporary table.
column-options
- Specifies a built-in data type. See
- 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 statement. 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).
- Specifies the constant as the default value for the column. The specified constant must:
- IDENTITY and identity-options
- For specification of identity columns, see IDENTITY and
identity-options in
CREATE TABLE
.
- 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).
- 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).A table that has a column defined as IMPLICITLY HIDDEN cannot be specified
(SQLSTATE
560AE).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. 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. The data types of the view columns must be data types that are valid for columns of a table.
- If a nickname is identified, then the implicit definition includes the column name, data type, and nullability characteristic of each column of nickname.
- If a random distribution table using the random by generation method is identified, then the RANDOM_DISTRIBUTION_KEY column used for generation of random distribution values is not included. Unless the new table being created shares the same table distribution.
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.
If row or column level access control (RCAC) is enforced for table-name1, RCAC is not inherited by the new table.
- AS (fullselect)
- Specifies
that, for each column in the derived result table of the fullselect, a
corresponding column is to be defined for the table. Each defined column adopts the following
attributes from its corresponding column of the result table (if applicable to the data type):
- Column name
- Column description
- Data type, length, precision, and scale
- Nullability
The following attributes are not included (although the default value and identity attributes can be included by using the copy-options):- Default value
- Identity attributes
- Hidden attribute
- ROW CHANGE TIMESTAMP
- Any other optional attributes of the tables or views referenced in the fullselect
The following restrictions apply:- 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 cannot refer to host variables or include parameter markers.
- The data types of the result columns of the fullselect must be data types that are valid for columns of a table.
- If row or column level access control (RCAC) is activated for any table that is specified in the fullselect, RCAC is not cascaded to the new table.
- WITH NO DATA | WITH DATA
- Determines whether to fill the columns of the table with data:
- WITH NO DATA
- Do not execute the fullselect. It is used only to define the table, which is not populated with the results of the query.
- WITH DATA
- Execute the fullselect and populate the table with the results of the query.
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.
- ORGANIZE BY
- Specifies how the data is organized in the data pages of the table:
- ROW
- The data is stored by row in the data pages of the table. Each data page stores the data for one or more rows of the table.
- COLUMN
- The data is stored by column in the data pages of the table. Each data page stores data for one column of the table.
The default is determined by the value of the dft_table_org database configuration parameter.
- ON COMMIT
- Specifies the action taken on the global 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 LOGGED.
- 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.
- 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 global 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.
Note: Declared global temporary tables using NOT LOGGED ON ROLLBACK PRESERVE ROWS cannot be column-organized.
- WITH REPLACE
- Indicates that, in the case that a declared temporary table already exists with the specified
name, the existing table is replaced with the temporary table defined by this statement (and all
rows of the existing table are deleted).
When WITH REPLACE is not specified, then the name specified must not identify a declared temporary table that already exists in the current session (SQLSTATE 42710).
- IN tablespace-name
- Identifies the table space in which the declared 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
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 statement.
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, a default distribution key is automatically defined.
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.
- DISTRIBUTE BY RANDOM
- Specifies that the database manager will select a distribution key to spread data evenly across all database partitions of the database partitioning group. Data distribution is accomplished by using a random by generation method. In this method, the database manager will include a column in the table to generate and store a generated value to use in the hashing function. The column will be created with the IMPLICITLY HIDDEN clause so that it does not appear in queries unless explicitly included. The value of the column will be automatically generated as new rows are added to the table. By default, the column name is RANDOM_DISTRIBUTION_KEY. If it collides with the existing column, a non-conflicting name will be generated by the database manager.
Notes
- A user temporary table space must exist before a declared temporary table can be declared (SQLSTATE 42727).
- Referencing
a declared temporary table: The description of a declared temporary table does not appear in
the database catalog (SYSCAT.TABLES); therefore, it is not persistent and is not shareable across
database connections. This means that each session that defines a declared temporary table called
table-name has its own possibly unique description of that declared global
temporary table.
In order to reference the declared temporary table in an SQL statement (other than the DECLARE GLOBAL TEMPORARY TABLE statement), the table must be explicitly or implicitly qualified by the schema name SESSION. If table-name is not qualified by SESSION, declared temporary tables are not considered when resolving the reference.
A reference to SESSION.table-name in a connection that has not declared a declared temporary table by that name will attempt to resolve from persistent objects in the catalog. If no such object exists, an error occurs (SQLSTATE 42704).
- When binding a package that has static SQL statements that refer to tables implicitly or explicitly qualified by SESSION, those statements will not be bound statically. When these statements are invoked, they will be incrementally bound, regardless of the VALIDATE option chosen while binding the package. At runtime, each table reference will be resolved to a declared temporary table, if it exists, or a created temporary table, or permanent table. If none exist, an error will be raised (SQLSTATE 42704).
- Privileges: When a declared temporary table is defined, the definer of the table is granted all table privileges on the table, including the ability to drop the table. Additionally, these privileges are granted to PUBLIC. (None of the privileges are granted with the GRANT option, and none of the privileges appear in the catalog table.) This enables any SQL statement in the session to reference a declared temporary table that has already been defined in that session.
- Instantiation and termination: For the following explanations, P denotes a session
and T is a declared temporary table in the session P:
- An empty instance of T is created as a result of the DECLARE GLOBAL TEMPORARY TABLE statement 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.
- If a DECLARE GLOBAL TEMPORARY TABLE statement is specified within the SQL procedure compound statement (defined by BEGIN and END), the scope of the declared temporary table is the connection, not just the compound statement, and the table is known outside of the compound statement. The table is not implicitly dropped at the END of the compound statement. A declared temporary table cannot be defined multiple times by the same name in other compound statements in that session, unless the table has been explicitly dropped.
- 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 SESSION.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 SESSION.T:
- If NOT LOGGED was specified, all rows from SESSION.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 SESSION.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 declaration of SESSION.T, then the rollback includes the operation DROP SESSION.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 declared temporary table SESSION.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 declared T terminates or disconnects from the database, T is dropped and its instantiated rows are destroyed.
- When the connection to the server at which T was declared terminates, T is dropped and its instantiated rows are destroyed.
- Restrictions on the use of declared temporary tables: Declared temporary tables
cannot:
- Be specified in an ALTER, COMMENT, GRANT, LOCK, RENAME or REVOKE statement (SQLSTATE 42995).
- Be referenced in an AUDIT, CREATE ALIAS, or CREATE VIEW statement (SQLSTATE 42995).
- Be specified in referential constraints (SQLSTATE 42995).
- Data row compression is enabled for a declared temporary table. When the database manager determines that there is a performance gain, table row data including XML documents stored inline in the base table object will be compressed. However, data compression of the XML storage object of a declared temporary table is not supported.
- Index compression is enabled for indexes that are created on declared temporary tables.
- Index compression is enabled by default for indexes that are created on declared 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.
-
Syntax alternatives: The following alternatives are non-standard. They are supported
for compatibility with earlier product versions or with other database products.
- DEFINITION ONLY can be specified in place of WITH NO DATA.
- The PARTITIONING KEY clause or DISTRIBUTE ON clause can be specified in place of the DISTRIBUTE BY clause.
- When specifying the value of the datetime special register, NOW() can be specified in place of CURRENT_TIMESTAMP.
- In a CHAR or VARCHAR
column definition, you do not need to specify the CCSID explicitly; the correct CCSID will be used
automatically. However, if you do specify the CCSID explicitly, it must correspond to the type of
database being used:
CCSID ASCII
for a non-unicode databaseCCSID UNICODE
for a unicode database
Examples
- Example 1: Define a declared temporary table with column definitions for an employee
number, salary, bonus, and
commission.
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_EMP (EMPNO CHAR(6) NOT NULL, SALARY DECIMAL(9, 2), BONUS DECIMAL(9, 2), COMM DECIMAL(9, 2)) ON COMMIT PRESERVE ROWS
- Example 2: Assume that base table USER1.EMPTAB exists and that it contains three columns,
one of which is an identity column. Declare a temporary table that has the same column names and
attributes (including identity attributes) as the base table.
In this example, SESSION is used as the implicit qualifier for TEMPTAB1.DECLARE GLOBAL TEMPORARY TABLE TEMPTAB1 LIKE USER1.EMPTAB INCLUDING IDENTITY ON COMMIT PRESERVE ROWS
Compatibility for Netezza Performance Server (NPS) temporary tables
If you need to declare a temporary table, it is recommended that you use a DECLARE GLOBAL TEMPORARY TABLE statement. However, to provide compatibility with IBM® Netezza® SQL, a CREATE TEMPORARY TABLE statement can be used instead.- In order to be able to reference a declared temporary table within STATIC SQL, the package must be bound with VALIDATE RUN.
- The session's ability to use the package cache might be impeded as a consequence.
- The schema name cannot begin with SYS (SQLSTATE 42939).
- A list of column definitions.
- A DISTRIBUTE BY HASH clause. For compatibility reasons, DISTRIBUTE ON HASH can be specified as an alternative.
- A DISTRIBUTE BY HASH clause. For compatibility reasons, DISTRIBUTE BY RANDOM can be specified as an alternative.
- An AS fullselect clause. Note that the fullselect can be enclosed in parentheses.
CREATE TEMPORARY TABLE table_name (column_definition) AS fullselect
DECLARE GLOBAL TEMPORARY TABLE table_name (column_definition) AS (fullselect) WITH DATA
ON COMMIT PRESERVE ROWS LOGGED