DECLARE GLOBAL TEMPORARY TABLE
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.
Invocation
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
Authorization
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:
- The SELECT privilege for the table or view
- The LIKE clause and AS select-statement using the WITH NO DATA clause do not require *READ authority.
- Ownership of the table or view
- Database administrator authority
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 each distinct type identified in the statement:
- The USAGE privilege on the distinct type, and
- The USAGE privilege on the schema containing the distinct type
- Database administrator authority
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.
Syntax
- 1 The same clause must not be specified more than once.
- 2 The optional clauses can be specified in any order.
- 3 data-type is optional for row change timestamp columns, row-begin and row-end columns, and transaction-start-ID columns.
- 4 The datalink-options can only be specified for DATALINKs and distinct-types sourced on DATALINKs.
- 1 GENERATED can be specified only if the column has a ROWID data type (or a distinct type that is based on a ROWID data type), or the column is an identity column, or the column is a row change timestamp.
Description
- table-name
- Names the temporary table. The
qualifier, if specified explicitly, must be SESSION, otherwise an
error is returned. If the qualifier is not specified, it is implicitly
defined to be SESSION. If a declared temporary table, or an index
or view that is dependent on a declared temporary table already exists
with the same name, an error is returned.
If a persistent table, view, index, or alias already exists with the same name and the schema name 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 a permanent library.
- Any references to SESSION.table-name will resolve to the declared temporary table rather than to a permanent table, view, index, or alias with a name of SESSION.table-name.
The table will be created in library QTEMP.
- FOR SYSTEM NAME system-object-identifier
- Identifies the system-object-identifier of the table. system-object-identifier must
not be the same as a table, view, alias, or index that already exists
at the current server. The system-object-identifier must be
an unqualified system identifier.
When system-object-identifier is specified, table-name must not be a valid system object name.
column-definition
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.
- column-name
- Names a column of the table. Do not qualify column-name and do not use the same name for more than one column of the table or for a system-column-name of the table.
- FOR COLUMN system-column-name
- Provides
an IBM® i name for
the column. Do not use the same name for more than one column of the
table or for a column-name of the table.
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.
- 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 ROWID column or a DATALINK column with FILE LINK CONTROL cannot be specified for a declared temporary table.
- distinct-type-name
- Specifies that the data type of the column is a distinct type (a user-defined data type). The length, precision, and scale of the column are respectively the length, precision, and scale of the source type of the 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.
- DEFAULT
- Specifies a default value for the column. This clause cannot
be specified more than once in a column-definition. DEFAULT cannot be
specified for the following types of columns because Db2®
generates default values:
- an identity column (a column that is defined AS IDENTITY)
- a row change timestamp column
- a row-begin column
- a row-end column
- a transaction-start-ID column
- a generated expression column
- GENERATED
- Specifies that the database manager
generates values for the column. GENERATED may be specified if the
column is to be considered one of the following types of columns:
- an identity column
- a row change timestamp column
GENERATED must be specified if the column is to be considered one of the following types of columns:
- a row-begin column
- a row-end column
- a transaction-start-ID column
- a generated expression column
- ALWAYS
- Specifies that the database manager will always generate a value for the column when a row is inserted or updated and a default value must be generated. ALWAYS is the recommended value.
- BY DEFAULT
- Specifies that the database manager will generate a value for
the column when a row is inserted 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.
- AS IDENTITY
- Specifies that the column is an identity
column for the table. A table can have only one identity column. AS
IDENTITY can be specified only if the data type for the column is
an exact numeric type with a scale of zero (SMALLINT, INTEGER, BIGINT,
DECIMAL or NUMERIC with a scale of zero, or a distinct type based
on one of these data types). If a DECIMAL or NUMERIC data type is
specified, the precision must not be greater than 31.
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.
- FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
- Specifies that the column is a timestamp and the values will be generated by the database manager. The database manager generates a value for the column for each row as a row is inserted, and for every row in which any column is updated. The value generated for a row change timestamp column is a timestamp corresponding to the time of the insert or update of the row. If multiple rows are inserted with a single SQL statement, the value for the row change timestamp column may be different for each row to reflect when each row was inserted. The generated value is not guaranteed to be unique.
- AS ROW BEGIN
- Specifies that the column contains timestamp data and that the values are generated by the database manager. The database manager generates a value for the column for each row as the row is inserted, and for every row in which any column is updated. The generated value is a timestamp that corresponds to the start time that is associated with the most recent transaction. If multiple rows are inserted with a single SQL statement, the values for the transaction start timestamp column are the same for each row.
- AS ROW END
- Specifies that a value for the data type of the column is assigned by the database manager whenever a row is inserted or any column in the row is updated. The assigned value is TIMESTAMP '9999-12-30-00.00.00.000000000000'.
- AS TRANSACTION START ID
- Specifies that the value is assigned by the database manager whenever a row is inserted into the table or any column in the row is updated. The database manager assigns a unique timestamp value per transaction or the null value. The null value is assigned to the transaction-start-ID column if the column is nullable and if there is a row-begin column in the table for which the value did not need to be adjusted. Otherwise the value is generated using a reading of the time-of-day clock during execution of the first data change statement in the transaction that requires a value to be assigned to a row-begin column or transaction-start-ID column in the table, or a row in a system-period temporal table is deleted. If multiple rows are inserted or updated within a single SQL transaction, the values for the transaction-start-ID column are the same for all the rows and are unique from the values generated for the column for another transaction.
- DATA CHANGE OPERATION
- Specifies that the database manager generates a value for each
row that is inserted, for every row in which any column is updated,
and for all rows deleted from a system-period temporal table when
the history table is defined with ON DELETE ADD EXTRA ROW. The column
will contain one of the following values:
- I
- insert operation
- U
- update operation
- D
- delete operation
- special-register
- Specifies that the value of a special register is assigned by the database manager for each row that is inserted, for every row in which any column is updated, and for all rows deleted from a system-period temporal table when the history table is defined with ON DELETE ADD EXTRA ROW. The value of the special register at the time of the data change statement is used. If multiple rows are changed with a single SQL statement, the value for the column will be the same for all of the rows.
- built-in-global-variable
- Specifies that the value of a built-in global variable is assigned by the database manager for each row that is inserted, for every row in which any column is updated, and for all rows deleted from a system-period temporal table when the history table is defined with ON DELETE ADD EXTRA ROW. The value of the built-in global variable at the time of the data change statement is used. If multiple rows are changed with a single SQL statement, the value for the column will be the same for all of the rows.
- 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.
- NOT NULL
- Prevents the column from containing null values. Omission of NOT NULL implies that the column can be null. NOT NULL is required for a row change timestamp column, a row-begin column, and a row-end column.
- NOT HIDDEN
- Indicates the column is included in implicit references to the table in SQL statements. This is the default.
- IMPLICITLY HIDDEN
- Indicates the column is not visible in SQL statements unless it is referred to explicitly by name. For example, SELECT * does not include any hidden columns in the result. A table must contain at least one column that is not IMPLICITLY HIDDEN.
- datalink-options
- Specifies the options associated
with a DATALINK data type.
- LINKTYPE URL
- Defines the type of link as a Uniform Resource Locator (URL).
- NO LINK CONTROL
- Specifies that there will not be any check made to determine that the linked files exist. Only the syntax of the URL will be checked. There is no database manager control over the linked files.
- period-definition
- PERIOD FOR
- Defines a period for the table.
- SYSTEM_TIME (begin-column-name, end-column-name)
- Defines a system period with the name SYSTEM_TIME. There must not be a column in the table with the name SYSTEM_TIME. A table can have only one SYSTEM_TIME period.
- begin-column-name
- Identifies the column that records the beginning of the period of time in which a row is valid. The name must identify a column that exists in the table. begin-column-name must not be the same as end-column-name. begin-column-name must be defined as AS ROW BEGIN.
- end-column-name
- Identifies the column that records the end of the period of time in which a row is valid. In the history table that is associated with a system-period temporal table, the history table column that corresponds to end-column-name in the system-period temporal table is set to reflect the deletion of the row. The name must identify a column that exists in the table. end-column-name must be defined as AS ROW END.
LIKE
- table-name or view-name
- Specifies that the columns defined
in the specified table or view are included in this table. The table-name or view-name specified
in a LIKE clause must identify the table or view that already exists
at the application server.
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):
- Column name (and system column name)
- Data type, length, precision, and scale
- CCSID
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):
- Default value, if a table-name is specified (view-name is not specified)
- Identity attributes
- Nullability
- Hidden attributes
- Row change timestamp attribute
- Column heading and text (see LABEL)
If table-name contains a row change timestamp column, row-begin column, row-end column, transaction-start-ID column, or generated expression column, the corresponding column of the new table inherits only the data type of the source column. The new column is not considered a generated column.
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.
as-result-table
- column-name
- Names a column of the table. Do not qualify column-name and do not use the same name for more than one column of the table or for a system-column-name of the table.
- FOR COLUMN system-column-name
- Provides an IBM i name
for the column. Do not use the same name for more than one column
of the table or for a column-name of the table.
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.
- select-statement
- 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 select-statement if the select-statement were
to be executed. The use of AS select-statement is
an implicit definition of n columns for
the table, where n is the number of columns
that would result from the select-statement.
The implicit definition includes the following attributes of the n columns
(if applicable to the data type):
- Column name (and system column name)
- Data type, length, precision, and scale
- CCSID
- Nullability
- Column heading and text (see LABEL)
The following attributes are not included (some attributes can be included by using the copy-options):
- Default value
- Hidden attribute
- Identity attributes
- Row change timestamp attribute
- Row-begin, row-end, and transaction-start-ID attribute
- Generated expression attribute
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 the select-statement contains an isolation-clause the isolation level specified in the isolation-clause applies to the entire SQL statement.
- WITH DATA
- Specifies that the select-statement is executed. After the table is created, the result table rows of the select-statement are automatically inserted into the table.
- WITH NO DATA
- Specifies that the select-statement is not executed. Therefore, there is no result table with a set of rows with which to automatically populate the table.
copy-options
- INCLUDING IDENTITY COLUMN ATTRIBUTES or EXCLUDING IDENTITY COLUMN ATTRIBUTES
- Specifies
whether identity column attributes are inherited.
- INCLUDING IDENTITY COLUMN ATTRIBUTES
- Specifies that the table inherits the identity attribute, if any,
of the columns resulting from select-statement, table-name,
or view-name. In general, the identity attribute
is copied if the element of the corresponding column in the table,
view, or select-statement 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 attribute.
If the INCLUDING IDENTITY COLUMN ATTRIBUTES clause is specified with
the AS select-statement clause, the columns
of the new table do not inherit the identity attribute in the following
cases:
- The select list of the select-statement includes multiple instances of an identity column name (that is, selecting the same column more than once).
- The select list of the select-statement includes multiple identity columns (that is, a join returned more than one identity column).
- The identity column is included in an expression in the select list.
- The select-statement includes a set operation (UNION or INTERSECT).
If INCLUDING IDENTITY is not specified, the table will not have an identity column.
- EXCLUDING IDENTITY COLUMN ATTRIBUTES
- Specifies that the table does not inherit the identity attribute, if any, of the columns resulting from the fullselect, table-name, or view-name.
- EXCLUDING COLUMN DEFAULTS or INCLUDING COLUMN DEFAULTS or USING TYPE DEFAULTS
- Specifies whether column defaults are inherited.
- EXCLUDING COLUMN DEFAULTS
- Specifies that the column defaults are not inherited from the definition of the source table. The default values of the column of the new 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 INSERT for the new table.
- INCLUDING COLUMN DEFAULTS
- Specifies that the table inherits the default values of the columns
resulting from the select-statement, table-name,
or view-name. 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 INCLUDING COLUMN DEFAULTS is not specified, the default values are not inherited.
- USING TYPE DEFAULTS
- Specifies that the default values for the table depend on the
data type of the columns that result from the select-statement, table-name,
or view-name. If the column is nullable,
then the default value is the null value. Otherwise, the default value
is as follows:
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.
- INCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES or EXCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES
- Specifies whether implicitly hidden columns are inherited.
- INCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES
- Specifies that the table inherits implicitly hidden columns from select-statement, table-name,
or view-name and those columns will be defined
with the implicitly hidden attribute in the new table.
If INCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES is not specified, the table will not have any implicitly hidden columns.
- EXCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES
- Specifies that the table does not inherit implicitly hidden columns from the fullselect, table-name, or view-name.
- INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES or EXCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES
- Specifies whether the row change timestamp attribute is inherited.
- INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES
- Specifies that the table inherits the row change timestamp attribute,
if any, of the columns resulting from select-statement, table-name,
or view-name. In general, the row change
timestamp attribute is copied if the element of the corresponding
column in the table, view, or select-statement 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 row
change timestamp attribute. If the INCLUDING ROW CHANGE TIMESTAMP
COLUMN ATTRIBUTES clause is specified with the AS select-statement clause,
the columns of the new table do not inherit the row change timestamp
in the following cases:
- The select list of the select-statement includes multiple instances of a row change timestamp column name (that is, selecting the same column more than once).
- The select list of the select-statement includes multiple row change timestamp columns (that is, a join returned more than one row change timestamp column).
- The row change timestamp column is included in an expression in the select list.
- The select-statement includes a set operation (UNION or INTERSECT).
If INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES is not specified, the table will not have a row change timestamp column.
- EXCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES
- Specifies that the table does not inherit the row change timestamp attribute, if any, of the columns resulting from the fullselect, table-name, or view-name.
- WITH REPLACE
- Specifies 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.
- ON COMMIT
- Specifies the action taken
on the declared temporary table when a COMMIT operation is performed.
The default is DELETE ROWS.
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.
- 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.
- NOT LOGGED
- Changes to the table are not logged, including creation of the
table. When a ROLLBACK (or ROLLBACK TO SAVEPOINT) operation is performed and the table was changed
in the unit of work (or savepoint), the changes are not rolled back. If the table was created in the
unit of work (or savepoint), the table will be dropped. If the table was dropped in the unit of work
(or savepoint), the table will be recreated with no rows.
- ON ROLLBACK
- Specifies the action taken on the declared temporary table when a
ROLLBACK 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.
- DELETE ROWS
- All rows of the table will be deleted. This is the default.
- PRESERVE ROWS
- Rows of the table will be preserved.
- RCDFMT format-name
- An unqualified name that designates
the IBM i record
format name of the table. A format-name is
a system identifier.
If a record format name is not specified, the format-name is the same as the system-object-name of the table.
- media-preference
- Specifies the preferred
storage media for the table.
- UNIT ANY
- No storage media is preferred. Storage for the table will be allocated from any available storage media.
- UNIT SSD
- Solid state disk storage media is preferred. Storage for the table may be allocated from solid state disk storage media, if available.
- KEEP IN MEMORY
- Specifies
whether the data for the table should be brought into a main storage
pool when the data is used in a query.
- NO
- The data will not be brought into a main storage pool.
- YES
- The data will be brought into a main storage pool.
Notes
Instantiation, scope, and termination: Let P denote an application process and let T be a declared temporary table in an application program in P:
- When a program in P issues a DECLARE GLOBAL TEMPORARY TABLE statement, an empty instance of T is created.
- Any program in P can reference T, and any of those references
is a reference to that same instance of T. (If a DECLARE GLOBAL TEMPORARY
statement is specified within a compound statement of an SQL function,
SQL procedure, or trigger; the scope of the declared temporary table
is the application process and not the compound statement.)
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.
- If T is defined with the ON COMMIT DELETE ROWS clause, when a commit operation terminates a unit of work in P and no program in P has a WITH HOLD cursor open that is dependent on T, all rows are deleted.
- If T is defined with the ON ROLLBACK DELETE ROWS clause, when a rollback operation terminates a unit of work in P, all rows are deleted.
- When the application process that declared T terminates, T is dropped.
Temporary table ownership: The owner of the table is the user profile of the thread 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:
- Declared temporary tables cannot be specified in an ALTER TABLE, COMMENT, CREATE ALIAS, GRANT, LABEL, LOCK, RENAME, or REVOKE statement.
- Declared temporary tables cannot be specified as the parent table in referential constraints
- If a declared temporary table is referenced in a CREATE INDEX or CREATE VIEW statement, the index or view must be created in SESSION (or library QTEMP).
Creating a table using a remote select-statement: The select-statement for an as-result-table can refer to tables on a different server than where the table is being created. This can be done by using a three-part object name or an alias that is defined to reference a three-part name of a table or view. The result of the select-statement cannot contain a column that has a field procedure defined. If the remote server is Db2 for LUW or Db2 for z/OS®, copy-options are not allowed. If the remote server is Db2 for LUW, a column list should be explicitly specified before the AS keyword.
Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:
- INLINE LENGTH is a synonym for ALLOCATE.
- DEFINITION ONLY is a synonym for WITH NO DATA
Examples
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.