The DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for the current connection. These tables do not reside in the system catalogs and are not persistent. Temporary tables exist only during the connection that declared them and cannot be referenced outside of that connection. When the connection closes, the rows of the table are deleted, and the in-memory description of the temporary table is dropped.
Temporary tables are useful when:
Names the temporary table. If a schema-Name other than SESSION is specified, an error will occur (SQLSTATE 428EK). If the schema-Name is not specified, SESSION is assigned. Multiple connections can define declared global temporary tables with the same name because each connection has its own unique table descriptor for it.
Using SESSION as the schema name of a physical table will not cause an error, but is discouraged. The SESSION schema name should be reserved for the temporary table schema.
See
Supported data-types are:
Specifies the action taken on the global temporary table when a COMMIT operation is performed.
All rows of the table will be deleted if no hold-able cursor is open on the table. This is the default value for ON COMMIT. If you specify ON ROLLBACK DELETE ROWS, this will delete all the rows in the table only if the temporary table was used. ON COMMIT DELETE ROWS will delete the rows in the table even if the table was not used (if the table does not have hold-able cursors open on it).
The rows of the table will be preserved.
Specifies the action taken on the global temporary table when a rollback operation is performed. When a ROLLBACK (or ROLLBACK TO SAVEPOINT) operation is performed, 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 restored with no rows.
This is the default value for NOT LOGGED. NOT LOGGED [ON ROLLBACK DELETE ROWS ]] specifies the action that is to be taken on the global temporary table when a ROLLBACK or (ROLLBACK TO SAVEPOINT) operation is performed. If the table data has been changed, all the rows will be deleted.
Note that temporary tables can only be declared in the SESSION schema. You should never declare a physical schema with the SESSION name.
The
following is a list of DB2 UDB DECLARE GLOBAL TEMPORARY TABLE functions that
are not supported by
Temporary tables cannot be specified in the following statements:
Temporary tables cannot be specified in referential constraints.
There is no check constraints support for columns.
The following data types cannot be used with Declared Global Temporary Tables:
Temporary tables cannot be referenced in a triggered-SQL-statement.
If a statement performing an insert, update, or delete to the temporary table encounters an error, all the rows of the table are deleted.
Any statements referencing SESSION schema tables and views will not be cached.