The DECLARE GLOBAL TEMPORARY TABLE statement defines a declared temporary table for the current application process. The declared temporary table description does not appear in the system catalog. It is not persistent and cannot be shared with other application processes. Each application process that defines a declared temporary table of the same name has its own unique description of the temporary table. When the application process ends, the temporary table is dropped.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
If the LIKE or AS select-statement clause is specified, the privileges held by the authorization ID of the statement must include at least one of the following on any table or view specified in the LIKE clause or as-result-table clause:
If a distinct type is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:
For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View and Corresponding System Authorities When Checking Privileges to a Distinct Type.
>>-DECLARE GLOBAL TEMPORARY TABLE--table-name-------------------> .-,------------------------------------------. V | >--+-(----+-column-definition----------------------+-+--)-+-----> | '-LIKE--+-table-name-+--+--------------+-' | | '-view-name--' '-copy-options-' | +-LIKE--+-table-name-+--+--------------+---------------+ | '-view-name--' '-copy-options-' | '-as-result-table--------------------------------------' .-----------------------------------------------. V | (1) >----+-WITH REPLACE------------------------------+-+------------> | .-ON COMMIT DELETE ROWS---. | +-+-------------------------+---------------+ | '-ON COMMIT PRESERVE ROWS-' | | .-ON ROLLBACK DELETE ROWS---. | '-NOT LOGGED--+---------------------------+-' '-ON ROLLBACK PRESERVE ROWS-' >--+---------------------+------------------------------------->< '-RCDFMT--format-name-' column-definition |--column-name--+-------------------------------------+---------> | .-COLUMN-. | '-FOR--+--------+--system-column-name-' (2) >--data-type----------------------------------------------------> .-----------------------------------------------------------------------. V | (1) >----+-------------------------------------------------------------------+-+------| +-+-default-clause------------------------------------------------+-+ | +-GENERATED--+-ALWAYS-----+--+-identity-options---------------+-+ | | | '-BY DEFAULT-' '-as-row-change-timestamp-clause-' | | | +-FIELDPROC--external-program-name--+--------------------+------+ | | | | .-,--------. | | | | | | V | | | | | | '-(----constant-+--)-' | | | +-NOT NULL------------------------------------------------------+ | | | .-NOT HIDDEN--------. | | | '-+-------------------+-----------------------------------------' | | '-IMPLICITLY HIDDEN-' | | (3) | '-datalink-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-' | | .-(--52--)------. | +-+-FLOAT--+---------------+-+----------------------------------------------------------------------------------------------+ | | '-(--integer--)-' | | | +-REAL---------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+----' | | .-(--34--)-. | +---DECFLOAT--+----------+--------------------------------------------------------------------------------------------------+ | '-(--16--)-' | | .-(--1--)-------. | +-+-+-+-CHARACTER-+--+---------------+-------------------------------+--+----------------+---------------------+------------+ | | | '-CHAR------' '-(--integer--)-' | +-FOR BIT DATA---+ | | | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)--+-----------------+-' +-FOR SBCS DATA--+ | | | | | '-CHAR------' | '-allocate-clause-' +-FOR MIXED DATA-+ | | | | '-VARCHAR----------------' '-ccsid-clause---' | | | | .-(--1M--)-------------. | | | '-----+-+-CHARACTER-+--LARGE OBJECT-+------+----------------------+--+-----------------+--+----------------+-' | | | '-CHAR------' | '-(--integer--+---+--)-' '-allocate-clause-' +-FOR SBCS DATA--+ | | '-CLOB------------------------' +-K-+ +-FOR MIXED DATA-+ | | +-M-+ '-ccsid-clause---' | | '-G-' | | .-(--1--)-------. | +-+---GRAPHIC----+---------------+----------------------------+--+--------------+-------------------------------------------+ | | '-(--integer--)-' | '-ccsid-clause-' | | +-+-GRAPHIC VARYING-+--(--integer--)--+-----------------+---+ | | | '-VARGRAPHIC------' '-allocate-clause-' | | | | .-(--1M--)-------------. | | | '---DBCLOB----+----------------------+--+-----------------+-' | | '-(--integer--+---+--)-' '-allocate-clause-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-+-NATIONAL CHARACTER-+--+---------------+-------------------------------+---------------------+--+------------------+-+ | | | +-NATIONAL CHAR------+ '-(--integer--)-' | | '-normalize-clause-' | | | | '-NCHAR--------------' | | | | | '-+-+-NATIONAL CHARACTER-+--VARYING-+--(--integer--)--+-----------------+-' | | | | | +-NATIONAL CHAR------+ | '-allocate-clause-' | | | | | '-NCHAR--------------' | | | | | '-NVARCHAR------------------------' | | | | .-(--1M--)-------------. | | | '-----+-+-NATIONAL CHARACTER-+--LARGE OBJECT-+------+----------------------+--+-----------------+-' | | | '-NCHAR--------------' | '-(--integer--+---+--)-' '-allocate-clause-' | | '-NCLOB--------------------------------' +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-BINARY--+---------------+------------------------------+-----------------+--------------------------------------------+ | | | '-(--integer--)-' | | | | | '-+-BINARY VARYING-+--(--integer--)--+-----------------+-' | | | | '-VARBINARY------' '-allocate-clause-' | | | | .-(--1M--)-------------. | | | '---+-BLOB----------------+----+----------------------+--+-----------------+-' | | '-BINARY LARGE OBJECT-' '-(--integer--+---+--)-' '-allocate-clause-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------+------------------------------------------------------------------------------------------------+ | | .-(--0--)-. | | | +-TIME--+---------+------+ | | | .-(--6--)-. | | | '-TIMESTAMP--+---------+-' | | .-(--200--)-----. | +---DATALINK--+---------------+--+-----------------+--+--------------+------------------------------------------------------+ | '-(--integer--)-' '-allocate-clause-' '-ccsid-clause-' | '---XML--+--------------+---------------------------------------------------------------------------------------------------' '-ccsid-clause-' ccsid-clause |--CCSID--integer--+------------------+-------------------------| '-normalize-clause-' normalize-clause .-NOT NORMALIZED-. |--+-NORMALIZED-----+-------------------------------------------| allocate-clause |--ALLOCATE--(integer)------------------------------------------|
default-clause .-WITH-. |--+------+--DEFAULT--+-------------------------------------------------+--| +-constant----------------------------------------+ +-USER--------------------------------------------+ +-NULL--------------------------------------------+ +-CURRENT_DATE------------------------------------+ +-CURRENT_TIME------------------------------------+ +-CURRENT_TIMESTAMP-------------------------------+ '-cast-function-name--(--+-constant----------+--)-' +-USER--------------+ +-CURRENT_DATE------+ +-CURRENT_TIME------+ '-CURRENT_TIMESTAMP-' identity-options |--AS IDENTITY--+------------------------------------------------------+--| | .--------------------------------------------. | | V .-1----------------. (1) | | '-(----+-START WITH--+-numeric-constant-+---+-----+--)-' | .-1----------------. | +-INCREMENT BY--+-numeric-constant-+-+ | .-NO MINVALUE----------------. | +-+-MINVALUE--numeric-constant-+-----+ | .-NO MAXVALUE----------------. | +-+-MAXVALUE--numeric-constant-+-----+ | .-NO CYCLE-. | +-+-CYCLE----+-----------------------+ | .-CACHE--20------. | +-+-NO CACHE-------+-----------------+ | '-CACHE--integer-' | | .-NO ORDER-. | '-+-ORDER----+-----------------------'
as-row-change-timestamp-clause |--FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP---------------| datalink-options .-LINKTYPE URL-. .-NO LINK CONTROL-. |--+--------------+--+-----------------+------------------------| copy-options .---------------------------------------------------------------. | .-COLUMN ATTRIBUTES-. | | .-EXCLUDING IDENTITY--+-------------------+-. | V | .-COLUMN ATTRIBUTES-. | | (1) |----+-+-INCLUDING IDENTITY--+-------------------+-+-------------+-+------| | .-COLUMN-. | | .-EXCLUDING--+--------+--DEFAULTS-. | | | .-COLUMN-. | | +-+-INCLUDING--+--------+--DEFAULTS-+-----------------------+ | '-USING TYPE DEFAULTS-------------' | | .-COLUMN ATTRIBUTES-. | | .-EXCLUDING IMPLICITLY HIDDEN -+-------------------+-. | | | .-COLUMN ATTRIBUTES-. | | +-+-INCLUDING IMPLICITLY HIDDEN -+-------------------+-+----+ | .-COLUMN ATTRIBUTES-. | | .-EXCLUDING ROW CHANGE TIMESTAMP -+-------------------+-. | | | .-COLUMN ATTRIBUTES-. | | '-+-INCLUDING ROW CHANGE TIMESTAMP -+-------------------+-+-' as-result-table |--+----------------------------------------------------------------+--> | .------------------------------------------------------. | | V | | '-(----column-name--+-------------------------------------+-+--)-' | .-COLUMN-. | '-FOR--+--------+--system-column-name-' >--AS--(--select-statement--)--+-WITH NO DATA-+--+--------------+--| '-WITH DATA----' '-copy-options-'
If a persistent table, view, index, or alias already exists with the same name and the schema name SESSION:
The table will be created in library QTEMP.
Defines the attributes of a column. There must be at least one column definition and no more than 8000 column definitions.
The sum of the row buffer byte counts of the
columns must not be greater than 32766 or, if a VARCHAR, VARGRAPHIC,
or VARBINARY column is specified, 32740. Additionally, if a LOB or
XML column is specified, the sum of the row data byte counts of the
columns must not be greater than 3.5 gigabytes. For information about
the byte counts of columns according to data type, see Maximum row sizes.
If the system-column-name is not specified, and the column-name is not a valid system-column-name, a system column name is generated. For more information about how system column names are generated, see Rules for Column Name Generation.
A ROWID column or a DATALINK column with FILE LINK CONTROL cannot be specified for a declared temporary table.
for an XML column,
an identity
column (a column that is defined AS IDENTITY) or a row change timestamp
column. The database manager generates default values for identity
columns and row change timestamp columns.
For an XML
column, the default is NULL unless NOT NULL is specified; in that
case there is no default.
If a value is not specified following
the DEFAULT keyword, then: | Data type | Default value |
|---|---|
| Numeric | 0 |
| Fixed-length character or graphic string | Blanks |
| Fixed-length binary string | Hexadecimal zeros |
| Varying-length string | A string length of 0 |
| Date | The current date at the time of INSERT |
| Time | The current time at the time of INSERT |
| Timestamp | The current timestamp at the time of INSERT |
| Datalink | A value corresponding to DLVALUE('','URL','') |
| distinct-type | The default value of the corresponding source type of the distinct type. |
Omission of NOT NULL and DEFAULT from a column-definition is an implicit specification of DEFAULT NULL.
| Data Type | Cast Function Name |
|---|---|
| Distinct type N based on a BINARY, VARBINARY, BLOB, CLOB, or DBCLOB | BINARY, VARBINARY, BLOB, CLOB, or DBCLOB * |
| Distinct type N based on a DATE, TIME, or TIMESTAMP | N (the user-defined cast function
that was generated when N was created) ** or |
| Distinct type N based on other data types | N (the user-defined cast function that was generated when N was created) ** |
| BINARY, VARBINARY, BLOB, CLOB, or DBCLOB | BINARY, VARBINARY, BLOB, CLOB, or DBCLOB * |
| DATE, TIME, or TIMESTAMP | DATE, TIME, or TIMESTAMP * |
| Notes:
* The name of the function must match the name of the data type (or the source type of the distinct type) with an implicit or explicit schema name of QSYS2. ** 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. |
|
If the value specified is not valid, an error is returned.
or updated
and a default value must be generated.
ALWAYS is the recommended
value.
or updated and
a default value must be generated, unless an explicit value is specified.
For an identity column or row change timestamp column, the database manager inserts or updates a specified value but does not verify that it is a unique value for the column unless the identity column or row change timestamp column has a unique constraint or a unique index that solely specifies the identity column or row change timestamp column.
An identity column is implicitly NOT NULL. An identity column cannot have a DEFAULT clause. See the AS IDENTITY clause in CREATE TABLE for the descriptions of the identity attributes.
FIELDPROC
Designates
an external-program-name as the field procedure exit routine
for the column. It must be an ILE program that does not contain SQL.
It cannot be a service program.
The field procedure encodes and decodes column values. Before
a value is inserted in the column, it is passed to the field procedure
for encoding. Before a value from the column is used, it is passed
to the field procedure for decoding.
The field procedure is also invoked during the processing of the
CREATE TABLE statement. When so invoked, the procedure provides DB2® with the column's field description.
The field description defines the data characteristics of the encoded
values. By contrast, the information supplied for the column in the
CREATE TABLE statement defines the data characteristics of the decoded
values.
A field procedure cannot be defined for a column
that is a ROWID or DATALINK or a distinct type based on a ROWID or
DATALINK. The column must not be an identity column or a row change
timestamp column. The column must not have a default value of CURRENT
DATE, CURRENT TIME, CURRENT TIMESTAMP, or USER. The column cannot
be referenced in a check condition. If it is part of a foreign key,
the corresponding parent key column must use the same field procedure.
See Embedded
SQL programming topic collection for an example of a field
procedure.
The use of LIKE is an implicit definition of n columns, where n is the number of columns in the identified table or view. The implicit definition includes the following attributes of the n columns (if applicable to the data type):
If the LIKE clause is specified immediately following the table-name and not enclosed in parenthesis, the following column attributes are also included, otherwise they are not included (the default value, identity, row change timestamp, and hidden attributes can also be controlled by using the copy-options):
If the specified table or view is a non-SQL created physical file or logical file, any non-SQL attributes are removed. For example, the date and time format will be changed to ISO.
The implicit definition does not include any other optional attributes of the identified table or view. For example, the new table does not automatically include a primary key or foreign key from a table. The new table has these and other optional attributes only if the optional clauses are explicitly specified.
If the system-column-name is not specified, and the column-name is not a valid system-column-name, a system column name is generated. For more information about how system column names are generated, see Rules for Column Name Generation.
The following attributes are not included (the default value, identity, row change timestamp, and hidden attributes may be included by using the copy-options):
The implicit definition does not include any other optional attributes of the tables or views referenced in the select-statement.
The implicitly defined columns of the table inherit the names of the columns from the result table of the select-statement. Therefore, a column name must be specified in the select-statement or in the column name list for all result columns. For result columns that are derived from expressions, constants, and functions, the select-statement must include the AS column-name clause immediately after the result column or a name must be specified in the column list preceding the select-statement.
The select-statement must not refer to variables or include parameter markers (question marks). The select-statement must not contain a PREVIOUS VALUE or a NEXT VALUE expression. The UPDATE, SKIP LOCKED DATA, and USE AND KEEP EXCLUSIVE LOCKS clauses may not be specified.
If INCLUDING IDENTITY is not specified, the table will not have an identity column.
Do not specify INCLUDING COLUMN DEFAULTS, if you specify USING TYPE DEFAULTS.
If INCLUDING COLUMN DEFAULTS is not specified, the default values are not inherited.
| Data type | Default value |
|---|---|
| Numeric | 0 |
| Fixed-length character or graphic string | Blanks |
| Fixed-length binary string | Hexadecimal zeros |
| Varying-length string | A string length of 0 |
| Date | The current date at the time of INSERT |
| Time | The current time at the time of INSERT |
| Timestamp | The current timestamp at the time of INSERT |
| Datalink | A value corresponding to DLVALUE('','URL','') |
| distinct-type | The default value of the corresponding source type of the distinct type. |
Do not specify USING TYPE DEFAULTS if INCLUDING COLUMN DEFAULTS is specified.
If INCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES is not specified, the table will not have any implicitly hidden columns.
If INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES is not specified, the table will not have a row change timestamp column.
When WITH REPLACE is not specified, then the name specified must not identify a declared temporary table that already exists in the current session.
The ON COMMIT clause does not apply if the declared temporary table is opened under isolation level No Commit (NC) or if a COMMIT HOLD operation is performed.
The ON ROLLBACK clause does not apply if the declared temporary table was opened under isolation level No Commit (NC) or if a ROLLBACK HOLD operation is performed.
If a record format name is not specified, the format-name is the same as the system-object-name of the table.
Instantiation, scope, and termination: Let P denote an application process and let T be a declared temporary table in an application program in P:
If T was declared at a remote server, the reference to T must use the same connection that was used to declare T and that connection must not have been terminated after T was declared. When the connection to the database server at which T was declared terminates, T is dropped.
Temporary table ownership: The owner of the table is the user profile of the job executing the statement.
Temporary table authority: When a declared temporary table is defined, PUBLIC implicitly is granted all table privileges on the table and authority to drop the table.
Referring to a declared temporary table in other SQL statements: Many SQL statements support declared temporary tables. To refer to a declared temporary table in an SQL statement other than DECLARE GLOBAL TEMPORARY TABLE, the table must be implicitly or explicitly qualified with SESSION.
If you use SESSION as the qualifier for a table name but the application process does not include a DECLARE GLOBAL TEMPORARY TABLE statement for the table name, the database manager assumes that you are not referring to a declared temporary table. The database manager resolves such table references to a permanent table.
Restrictions on the use of declared temporary tables:
Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:
Example 1: 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)) 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.
DECLARE GLOBAL TEMPORARY TABLE TEMPTAB1 LIKE USER1.EMPTAB INCLUDING IDENTITY ON COMMIT PRESERVE ROWS
In the above example, the database manager uses SESSION as the implicit qualifier for TEMPTAB1.