DECLARE GLOBAL TEMPORARY TABLE statement
The DECLARE GLOBAL TEMPORARY TABLE statement defines a declared temporary table for the current application process. The declared temporary table resides in the work file database and its 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 and instance of the temporary table. When the application process terminates, the temporary table is dropped.
Invocation for DECLARE GLOBAL TEMPORARY TABLE
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
Authorization for DECLARE GLOBAL TEMPORARY TABLE
None are required, unless the LIKE clause or AS fullselect is specified. In those cases, additional privileges might be required.
PUBLIC implicitly has the following privileges without GRANT authority for declared temporary tables:
- The CREATETAB privilege to define a declared temporary table in the database that is defined AS WORKFILE, which is the database for declared temporary tables.
- The USE privilege to use the table spaces in the database that is defined as WORKFILE.
- All table privileges on the table and authority to drop the table. (Table privileges for a declared temporary table cannot be granted or revoked.)
These implicit privileges are not recorded in the Db2 catalog and cannot be revoked.
Syntax for DECLARE GLOBAL TEMPORARY TABLE
column-definition:
data-type:
built-in-type:
- 1 The CCSID clause must only be specified for a character string or a graphic string column in an EBCDIC created global temporary table.
as-result-table:
copy-options:
- 1 These clauses can be specified in any order and must not be specified more than one time.
- 2 EXCLUDING COLUMN DEFAULTS, INCLUDING COLUMN DEFAULTS, and USING TYPE DEFAULTS must not be specified with the LIKE clause.
identity-options:
Description for DECLARE GLOBAL TEMPORARY TABLE
- table-name
- Names the temporary table. The qualifier, if specified explicitly,
must be SESSION. If the qualifier is not specified, it is implicitly
defined to be SESSION.
If a table, view, synonym, or alias already exists with the same name and an implicit or explicit qualifier of 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 the persistent and shared names in the Db2 catalog tables.
- Any references to SESSION.table-name will resolve to the declared temporary table rather than to any existing SESSION.table-name whose definition is persistent and is in the Db2 catalog tables.
- column-definition
- Defines the attributes of a column for each instance of the table. The number of columns defined must not exceed 750. The maximum record size must not exceed 32683 bytes. The maximum row size must not exceed 32675 bytes (8 bytes less than the maximum record size).
- column-name
- Names the column. The name must not be qualified and must not be the same as the name of another column in the table.
- data-type
- Specifies the data type of the column. The data type can be any built-in data type that can be specified for the CREATE TABLE statement except for a LOB (BLOB, CLOB, and DBCLOB), ROWID, or XML type. The FOR subtype DATA clause can be specified as part of data-type. For more information on the data types and the rules that apply to them, see built-in-type.
- DEFAULT
- Specifies a default value for the column. This clause must
not be specified more than once in the same column-definition.
Omission of NOT NULL and DEFAULT from a column-definition is an implicit specification of DEFAULT NULL.
If DEFAULT is specified without a value after it, the default value of the column depends on the data type of the column, as follows:- Data type
- Default value
- Numeric
- 0
- Fixed-length character string
- A string of blanks
- Fixed-length graphic string
- A string of blanks
- Fixed-length binary string
- Hexadecimal zeros
- Varying-length string
- A string of length 0
- Date
- CURRENT DATE
- Time
- CURRENT TIME
- Timestamp
- CURRENT TIMESTAMP(p) where p is the corresponding timestamp precision.
- Timestamp with time zone
- CURRENT TIMESTAMP(p) WITH TIME ZONE where p is the corresponding timestamp precision.
A default value other than the one that is listed above can be specified in one of the following forms:- constant
- Specifies a constant as the default value for the column. The value of the constant must conform to the rules for assigning that value to the column. A hexadecimal graphic string constant (GX) cannot be specified.
- SESSION_USER or USER
- Specifies the value of the SESSION_USER (USER) special register at the time of an insert or update operation or LOAD as the default value for the column. The data type of the column must be a character string with a length attribute greater than or equal to the length attribute of the SESSION_USER special register.
- CURRENT SQLID
- Specifies the value of the SQL authorization ID of the process at the time of an SQL data change statement or LOAD as the default value for the column. The data type of the column must be a character string with a length attribute greater than or equal to the length attribute of the CURRENT SQLID special register.
- NULL
- Specifies null as the default value for the column. If NOT NULL was specified, DEFAULT NULL must not be specified within the same column-definition.
- GENERATED
- Specifies that Db2 generates values for the column. GENERATED
must be specified if the column is to be considered an IDENTITY column. If DEFAULT is specified for the column for an update
operation, Db2 generates a value
for both GENERATED ALWAYS and GENERATED BY DEFAULT.
- ALWAYS
- Specifies that Db2 always generates a value for the column when a row is inserted into the table.
- BY DEFAULT
- Specifies that Db2 generates
a value for the column when a row is inserted into the table unless
a value is specified. BY DEFAULT is the recommended value only when
you are using data propagation.
Defining a column as GENERATED BY DEFAULT does not necessarily guarantee the uniqueness of the values. To ensure uniqueness of the values, define a unique, single-column index on the 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 with a scale of zero).
An identity column is implicitly NOT NULL. An identity column cannot have a DEFAULT clause. For the descriptions of the identity attributes, see the description of the AS IDENTITY clause in CREATE TABLE statement.
- NOT NULL
- Specifies that the column cannot contain nulls. Omission of NOT NULL indicates that the column can contain nulls.
- LIKE table-name or view-name
- Specifies that the columns of the
table have the same name, data type, and nullability attributes as
the columns of the identified table or view. If a table is identified,
the column default attributes are also defined by that table. If
row permissions or column access control is enforced for the table
specified by table-name, row and column
access controls are not inherited by the new table. The name
specified must identify a table, view, synonym, or alias that exists
at the current server. The identified table must
not be an auxiliary table or an accelerator-only table.
The privilege set must include the SELECT privilege on the identified table or view.
This clause is similar to the LIKE clause on CREATE TABLE, but it has the following differences:
- If LIKE results in a column having a LOB data type, a ROWID data type, or distinct type, the DECLARE GLOBAL TEMPORARY TABLE statement fails.
- In addition to these data type restrictions, if any column has any other attribute value that is not allowed in a declared temporary table, that attribute value is ignored. The corresponding column in the new temporary table has the default value for that attribute unless otherwise indicated.
When the identified object is a table, the column name, data type, nullability, and default attributes are determined from the columns of the specified table; any identity column attributes are inherited only if the INCLUDING IDENTITY COLUMN ATTRIBUTES clause is specified.
- as-result-table
- Specifies
that the table definition is based on the column definitions from
the result of a query expression.
The behavior of these column attributes is controlled with the INCLUDING or USING TYPE DEFAULTS clauses, which are defined below.
- AS (fullselect)
- Specifies an implicit definition of n columns
for the declared global temporary table, where n is
the number of columns that would result from the fullselect.
The columns of the new table are defined by the columns that result
from the fullselect. Every select list element
must have a unique name. The AS clause can be used in the select-clause to
provide unique names. The implicit definition includes the column
name, data type, and nullability characteristic of each of the result
columns of fullselect. A column
of the new table that corresponds to an implicitly hidden column of
a base table referenced in the fullselect is
not considered hidden in the new table.Row
and column access controls that are enforced on the base table are
not inherited by the new table.
The result table of the fullselect must not contain a column that has a LOB data type, a ROWID data type, an XML data type or a distinct type.
If fullselect results in other column attributes that are not applicable for a declared temporary table, those attributes are ignored in the implicit definition for the declared temporary table.
If fullselect results in 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 as a generated column.
The fullselect must not refer to variables or include parameter markers (question marks), but can refer to global variables. The outermost SELECT list of the outermost fullselect must not reference data that is encoded with different encoding schemes, unless that data is from EBCDIC and Unicode columns in an EBCDIC table. The outermost SELECT list of the outermost fullselect must not result in a column that is an array.
- WITH NO DATA
- Specifies that the fullselect is not executed. You can use the INSERT INTO statement with the same fullselect specified in the AS clause to populate the declared temporary table with the set of rows from the result table of the fullselect.
- copy-options
- Specifies whether identity column attributes and column defaults
are inherited from the definition of the source of the result table.
- EXCLUDING IDENTITY COLUMN ATTRIBUTES or INCLUDING IDENTITY COLUMN ATTRIBUTES
- Specifies whether identity
column attributes are inherited from the columns resulting from the fullselect, table-name,
or view-name.
- EXCLUDING IDENTITY COLUMN ATTRIBUTES
- Specifies that the table does not inherit the identity attributes of the columns resulting from the fullselect, table-name, or view-name.
- INCLUDING IDENTITY COLUMN ATTRIBUTES
- Specifies that the table inherits the identity attributes, if
any, of the columns resulting from the fullselect or table-name.
In general, the identity attributes are copied if the element of
the corresponding column in the table or fullselect is
the name of a table column that directly or indirectly maps to the
name of a base table column that is an identity column.
If the INCLUDING IDENTITY COLUMN ATTRIBUTES clause is specified with the AS fullselect clause, the columns of the new table do not inherit the identity attribute in the following cases:
- The select list of the fullselect includes multiple instances of an identity column name (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.
If INCLUDING IDENTITY COLUMN ATTRIBUTES is not specified, the new table will not have an identity column.
If the LIKE clause identifies a view, INCLUDING IDENTITY COLUMN ATTRIBUTES must not be specified.
- EXCLUDING COLUMN DEFAULTS, INCLUDING COLUMN DEFAULTS, or USING TYPE DEFAULTS
- Specifies whether the table inherits the default values of the
columns of the fullselect. EXCLUDING COLUMN DEFAULTS, INCLUDING COLUMN
DEFAULTS, and USING TYPE DEFAULTS must not be specified if the LIKE
clause is specified.
- EXCLUDING COLUMN DEFAULTS
- Specifies that the table does not inherit the default values of the columns of the fullselect. 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 an insert operation for the new table.
- INCLUDING COLUMN DEFAULTS
- Specifies that the table inherits the default values of the columns of the fullselect. A default value is the value that is assigned to the column when a value is not specified on an insert operation or LOAD. Columns resulting from the fullselect that are not updatable will not have a default defined in the corresponding column of the created table.
- USING TYPE DEFAULTS
- Specifies that the
default values for the declared temporary table depend on the data
type of the columns that result from fullselect,
as follows:
- Data type
- Default value
- Numeric
- 0
- Fixed-length character string
- Blanks
- Fixed-length graphic string
- Blanks
- Fixed-length binary string
- Hexadecimal zeros
- Varying-length string
- A string of length 0
- Date
- CURRENT DATE
- Time
- CURRENT TIME
- Timestamp
- CURRENT TIMESTAMP(p) where p is the corresponding timestamp precision.
- Timestamp(integer) with time zone
- CURRENT TIMESTAMP(p) WITH TIME ZONE where p is the corresponding timestamp precision.
- CCSID encoding-scheme
- Specifies the encoding scheme
for string data that is stored in the table. For declared temporary
tables, the encoding scheme for the data cannot be specified for the
table space or database, and all data in one table space or the database
need not use the same encoding scheme. Because there can be only one work file database for all declared temporary tables
for each Db2 member, there can
be a mixture of encoding schemes in both the database and each table
space.
The CCSID clause can be specified for the declared temporary table, or for individual columns in the declared temporary table. If a CCSID clause is specified for the table, that CCSID specifies the encoding scheme for the table, regardless of whether the LIKE clause is also specified. If a CCSID clause is not specified for the table, the encoding scheme for the table is EBCDIC.
If a CCSID clause is specified for a column, the encoding scheme for the declared temporary table must be EBCDIC. If a CCSID clause is not specified for a column, and the LIKE clause is not specified for the table, the CCSID of the column is the same as the CCSID of the table. If the LIKE clause is specified, and the source table that is specified in the LIKE clause is an EBCDIC table with Unicode columns, the columns in the declared temporary table that correspond to the Unicode columns in the source table are also Unicode.
- ASCII
- Specifies that the data is encoded by using the ASCII CCSIDs of the server.
- EBCDIC
- Specifies that the data is encoded by using the EBCDIC CCSIDs of the server.
- UNICODE
- Specifies that the data is encoded by using the UNICODE CCSIDs of the server.
An error occurs if the CCSIDs for the encoding scheme have not been defined. Usually, each encoding scheme requires only a single CCSID. Additional CCSIDs are needed when mixed, graphic, or UNICODE data is used.
- ON COMMIT
- Specifies what happens
to the table for a commit operation. The default is ON COMMIT
DELETE ROWS.
- DELETE ROWS
- Specifies that all of the rows of the table are deleted if there is no open cursor that is defined as WITH HOLD that references the table.
- PRESERVE ROWS
- Specifies that all of the rows of the table are preserved. Thread reuse capability is not available to any application process or thread that contains, at its most recent commit, an active declared temporary table that was defined with the ON COMMIT PRESERVE ROWS clause.
- DROP TABLE
- Specifies that the table is implicitly dropped at commit if there is no open cursor that is defined as WITH HOLD that references the table. If there is an open cursor defined as WITH HOLD on the table at commit, the rows are preserved.
- LOGGED or NOT LOGGED
- Specifies
whether operations for the table are to be logged. This option also
applies to any indexes that are associated with the table. Indexes
inherit the logging attribute from their associated tables.
- LOGGED
- Specifies that insert, update, or delete operations for the declared temporary table are logged. Create and drop actions for the table are also logged. This is the default option.
- NOT LOGGED
- Specifies that insert, update, or delete operations for the declared
temporary table are not logged. However, create and drop actions for
the table are logged.
- ON ROLLBACK DELETE ROWS
- Specifies that when a ROLLBACK or ROLLBACK TO SAVEPOINT statement is issued, all rows of the global temporary table are deleted. This is the default.
- ON ROLLBACK PRESERVE ROWS
- Specifies that when a ROLLBACK or ROLLBACK TO SAVEPOINT statement is issued, all rows of the global temporary table are preserved.
If a ROLLBACK or ROLLBACK TO SAVEPOINT statement is issued, the following actions occur for tables that were created or dropped:- If the table was created within the unit of work or savepoint, the table is dropped.
- If the table was dropped within the unit of work or savepoint, the table is re-created without any data.
For statements that insert multiple rows, the ATOMIC and NOT ATOMIC CONTINUE ON SQLEXCEPTION options of the INSERT statement determine the result of an error. If ATOMIC is specified, an error during insertion causes all rows in the global temporary table to be deleted. If NOT ATOMIC CONTINUE ON SQLEXCEPTION is specified, an error during insertion causes all rows in the table to be deleted, but the next insert is processed. At the end of the insert, the table includes only the rows that were inserted after the last error.
Restriction: In CREATE TABLESPACE and ALTER TABLESPACE statements, LOG YES and LOG NO can be used as syntax alternatives for LOGGED and NOT LOGGED, respectively. These syntax alternatives cannot be used in a DECLARE GLOBAL TEMPORARY TABLE statement.
Notes for DECLARE GLOBAL TEMPORARY TABLE
- Instantiation, scope, and termination
-
For the following explanations, P denotes an application process, and T is a declared temporary table executed in P:
- An empty instance of T is created when a DECLARE GLOBAL TEMPORARY TABLE statement is executed in P.
- Any SQL statement in P can reference T, and any of those references to T in P is a reference to
that same instance of T. ()
If a DECLARE GLOBAL TEMPORARY statement is specified within an SQL PL compound statement, the scope of the declared temporary table is the application process and not just the compound statement. A declared temporary table cannot be defined multiple times by the same name in other compound statements in that application process, unless the table has been dropped explicitly.
- If T was declared at a remote server, the reference to T must use the same Db2 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 was defined with the ON COMMIT DELETE ROWS clause specified implicitly or explicitly, 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 deletes all rows from T.
- If T is defined with the ON COMMIT DROP TABLE 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, the commit includes the operation DROP TABLE T.
- When a rollback operation terminates a unit of work or savepoint in P, and that unit of work or
savepoint includes the declaration of SESSION.T, the changes to table T are undone.
When a rollback operation terminates a unit of work or savepoint in P, and that unit of work or savepoint includes the declaration of SESSION.T, the rollback drops table T.
When a rollback operation terminates a unit of work or savepoint in P, and that unit of work or savepoint includes the drop of the declaration of declared temporary table SESSION.T, the rollback undoes the drop of table T.
- When the application process that declared T terminates, T is dropped.
- 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
the following actions occur:
- 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 table T are undone.
- If NOT LOGGED was specified and an INSERT, UPDATE or DELETE statement fails during execution (not 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, the rollback includes the operation DROP SESSION.T.
- When 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, the rollback undoes the drop of the table. If NOT LOGGED was specified, the table is also emptied.
- When the application process that declared T terminates or disconnects from the database, T is dropped and its instantiated rows are destroyed.
- Privileges
- When a declared temporary table is defined, PUBLIC is implicitly granted all table privileges on the table and authority to drop the table. These implicit privileges are not recorded in the Db2 catalog and cannot be revoked. This enables any SQL statement in the application process to reference a declared temporary table that has already been defined in that application process.
- 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, you must qualify the table name with SESSION. You can either specify SESSION explicitly in the table name or use the QUALIFIER bind option to specify SESSION as the qualifier for all SQL statements in the plan or package.
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, Db2 assumes that you are not referring to a declared temporary table. Db2 resolves such table references to a table whose definition is persistent and appears in the Db2 catalog tables.
With the exception of the DECLARE GLOBAL TEMPORARY TABLE statement, any static SQL statement that references a declared temporary table is incrementally bound at run time. This is because the definition of the declared temporary table does not exist until the DECLARE GLOBAL TEMPORARY statement is executed in the application process that contains those SQL statements and the definition does not persist when the application process finishes running.
When a plan or package is bound, any static SQL statement (other than the DECLARE GLOBAL TEMPORARY TABLE statement) that references a table-name that is qualified by SESSION, regardless of whether the reference is for a declared temporary table, is not completely bound. However, the bind of the plan or package succeeds if there are no other errors. No object dependencies, including non-DECLARE GLOBAL TEMPORARY TABLE objects, are recorded in the Db2 catalog for any such statements. These static SQL statements are then incrementally bound at run time when the static SQL statement is issued.
The incremental binds are necessary for the following reasons:
- The definition of the declared temporary table does not exist until the DECLARE GLOBAL TEMPORARY TABLE statement for the table is executed in the same application process that contains those SQL statements. Therefore, Db2 must wait until the plan or package is run to determine if SESSION.table-name refers to a base table or a declared temporary table.
- The definition of a declared temporary table does not persist after the table it is explicitly dropped (DROP statement), implicitly dropped (ON COMMIT DROP TABLE), or the application process that defined it finishes running. When the application process terminates or is re-used as a reusable application thread, the instantiated rows of the table are deleted and the definition of the declared temporary table is dropped if it has not already been explicitly or implicitly dropped.
After the plan or package is bound, any static SQL statement that refers to a table-name that is qualified by SESSION has a new statement status of M in the Db2 catalog table (STATUS column of SYSIBM.SYSSTMT or SYSIBM.SYSPACKSTMT).
Considerations for column names longer than 30 bytes
If a length of a new column name is greater than 30 Unicode bytes, truncation occurs in the SQLNAME field of the SQLDA when the column is described in an application. A column name in UTF8, and its equivalent in the system EBCDIC CCSID, must be 128 bytes or less. For more information about long column names, see Column names longer than 30 bytes.
- Thread reuse
-
If a declared temporary table is defined in an application process that is running as a local thread, the application process or local thread that declared the table qualifies for explicit thread reuse if:
- The table was defined with both the default ON COMMIT DELETE ROWS attribute and the NOT LOGGED ON ROLLBACK DELETE ROWS attribute.
- The table was defined with PRESERVE ROWS specified on either the ON COMMIT or NOT LOGGED ON ROLLBACK option and the table was explicitly dropped with the DROP TABLE statement before the thread's commit operation.
- The table was defined with the ON COMMIT DROP TABLE attribute. When a declared temporary table is defined with the ON COMMIT DROP TABLE and a commit occurs, the table is implicitly dropped if there are no open cursors defined with the WITH HOLD option.
When the thread is reused, the declared temporary table is dropped and its rows are destroyed. However, if you do not explicitly or implicitly drop all declared temporary tables before or when your thread performs a commit and the thread becomes idle waiting to be reused, as with all thread reuse situations, the idle thread holds resources and locks. This includes some declared temporary table resources and locks on the table spaces and the database descriptor (DBD) for the work file database. So, instead of using the implicit drop feature of thread reuse to drop your declared temporary tables, it is recommended that you:
- Use the DROP TABLE statement to explicitly drop your declared temporary tables before the thread performs a commit and becomes idle.
- Define the declared temporary tables with ON COMMIT DROP TABLE clause so that the tables are implicitly dropped when a commit occurs.
Explicitly dropping the tables before a commit occurs or having them implicitly dropped when the commit occurs enables you to maximize the use of declared temporary table resources and release locks when multiple threads are using declared temporary table.
Remote threads qualify for thread reuse differently than local threads. If a declared temporary table is defined (with or without ON COMMIT DELETE ROWS) in an application process that is running as a remote or DDF thread (also known as Database Access Thread or DBAT), the remote thread qualifies for thread reuse only when the declared temporary table is explicitly dropped before the thread performs a commit operation. Dropping the declared temporary table enables the remote thread to qualify for the implicit thread reuse that is supported for DDF threads via connection pooling and to become an inactive DBAT (type 1 inactive thread) or an inactive connection (type 2 inactive thread).
- Parallelism support
- Only I/O and CP parallelism are supported. Any query that involves a declared temporary table is limited to parallel tasks on a single CPC.
- Restrictions on the use of declared temporary tables
- Declared temporary tables cannot:
- Be specified in referential constraints.
- Be referenced in any SQL statements that are defined in an SQL function body (CREATE FUNCTION or ALTER FUNCTION statements), a trigger body (CREATE TRIGGER statement). If you refer a table name that is qualified with SESSION in a trigger body, Db2 assumes that you are referring to a base table.
- Be referenced in a CREATE INDEX statement unless the schema name of the index is SESSION.
In addition, do not refer to a declared temporary table in any of the following statements.Statement Statement - ALTER INDEX
- ALTER TABLE
- COMMENT
- CREATE ALIAS
- CREATE FUNCTION (TABLE LIKE clause)
- CREATE MASK (ON table-name clause)
- CREATE PERMISSION (ON table-name clause)
- CREATE PROCEDURE (TABLE LIKE clause)
- CREATE TRIGGER
- CREATE VIEW
- GRANT (table or view privileges)
- LABEL
- LOCK TABLE
- REFRESH TABLE
- RENAME
- REVOKE (table or view privileges)
- Declared global temporary tables and dynamic statement caching
- The Db2 dynamic statement cache feature does not support dynamic SQL statements that reference declared temporary tables, even if the SQL statement also includes references to base or persistent tables. Db2 will not insert such statements into the dynamic statement cache. Instead, these dynamic statements are processed as if statement caching is not in effect. Declared temporary tables are unique and specific to an application process or Db2 thread, cannot be shared across threads, are not described in the Db2 catalog, and do not persist beyond termination of the Db2 thread or application process. These attributes prevent the use of the dynamic statement cache feature where tables and SQL statements are shared across threads or application processes.
- Table space requirements in the work file database
- Db2 stores all declared temporary tables in the work file database. You cannot define a declared temporary table unless a table space with at least an 32KB page size exists in the work file database.
- Alternative syntax and synonyms
- To provide compatibility with previous releases, Db2
allows you to specify:
- LONG VARCHAR as a synonym for VARCHAR(integer) and LONG VARGRAPHIC as a synonym for
VARGRAPHIC(integer) when defining the data type of a column.
However, the use of these synonyms is not encouraged because after the statement is processed, Db2 considers a LONG VARCHAR column to be VARCHAR and a LONG VARGRAPHIC column to be VARGRAPHIC.
- DEFINITION ONLY as a synonym for WITH NO DATA.
- TIMEZONE can be specified as an alternative to TIME ZONE.
- LONG VARCHAR as a synonym for VARCHAR(integer) and LONG VARGRAPHIC as a synonym for
VARGRAPHIC(integer) when defining the data type of a column.
Examples for DECLARE GLOBAL TEMPORARY TABLE
- 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)) CCSID EBCDIC 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 the above example, Db2 uses SESSION as the implicit qualifier for TEMPTAB1.DECLARE GLOBAL TEMPORARY TABLE TEMPTAB1 LIKE USER1.EMPTAB INCLUDING IDENTITY ON COMMIT PRESERVE ROWS;