Distinctions between Db2 base tables and temporary tables
Db2 base tables and the two types of temporary tables have several distinctions.
The following table summarizes important distinctions between base tables, created
temporary tables, and declared temporary tables.
| Area of distinction | Distinction | ||
|---|---|---|---|
| Creation, persistence, and ability to share table descriptions | Base tables: The CREATE TABLE statement puts a description of the table in the catalog view SYSCAT.TABLES. The table description is persistent and is shareable across different connections. The name of the table in the CREATE TABLE statement can be qualified. If the table name is not qualified, it is implicitly qualified using the standard qualification rules applied to SQL statements. | ||
| Created temporary tables: The CREATE GLOBAL TEMPORARY TABLE statement puts a description of the table in the catalog view SYSCAT.TABLES. The table description is persistent and is shareable across different connections. The name of the table in the CREATE GLOBAL TEMPORARY TABLE statement can be qualified. If the table name is not qualified, it is implicitly qualified using the standard qualification rules applied to SQL statements. | |||
| Declared temporary tables: The DECLARE GLOBAL TEMPORARY
TABLE statement does not put a description of the table in the catalog. The table description is not
persistent beyond the life of the connection that issued the DECLARE GLOBAL TEMPORARY TABLE
statement and the description is known only to that connection. Thus, each connection could have its own possibly unique description of the same declared temporary table. The name of the table in the DECLARE GLOBAL TEMPORARY TABLE statement can be qualified. If the table name is qualified, SESSION must be used as the schema qualifier. If the table name is not qualified, SESSION is implicitly used as the qualifier. |
|||
| Table instantiation and ability to share data | Base tables: The CREATE TABLE statement creates one empty instance of the table, and all connections use that one instance of the table. The table and data are persistent. | ||
| Created temporary tables: The CREATE GLOBAL TEMPORARY TABLE statement does not create an instance of the table. The first implicit or explicit reference to the table in an open, select, insert, update, or delete operation that is executed by any program using the connection creates an empty instance of the given table. Each connection that references the table has its own unique instance of the table, and the instance is not persistent beyond the life of the connection. | |||
| Declared temporary tables: The DECLARE GLOBAL TEMPORARY TABLE statement creates an empty instance of the table for the connection. Each connection that declares the table has its own unique instance of the table, and the instance is not persistent beyond the life of the connection. | |||
| References to the table during the connection | Base tables: References to the table name in multiple connections refer to the same single persistent table description and to the same instance at the current server. If the table name that is being referenced is not qualified, it is implicitly qualified using the standard qualification rules that apply to SQL statements. | ||
| Created temporary tables: References to the table name in multiple connections refer to the same single persistent table description but to a distinct instance of the table for each connection at the current server. If the table name that is being referenced is not qualified, it is implicitly qualified using the standard qualification rules that apply to SQL statements. | |||
| Declared temporary tables: References to the table name in multiple connections refer to a distinct description and instance of the table for each connection at the current server. References to the table name in an SQL statement (other than the DECLARE GLOBAL TEMPORARY TABLE statement) must include SESSION as the schema qualifier. If the table name is not qualified with SESSION, the reference is assumed to be to a base table. | |||
| Table privileges and authorization | Base tables: The owner implicitly has all table
privileges on the table and the authority to drop the table. The owner's table privileges can be
granted and revoked, either individually or with the ALL clause. Another authorization ID can access the table only if it has been granted appropriate privileges for the table. |
||
| Created temporary tables: The owner implicitly has all
table privileges on the table and the authority to drop the table. The owner's table privileges can
be granted and revoked, either individually or with the ALL clause. Another authorization ID can access the table only if it has been granted appropriate privileges for the table. |
|||
| Declared temporary tables: PUBLIC implicitly has all
table privileges on the table without GRANT authority and also has the authority to drop the table.
These table privileges cannot be granted or revoked. Any authorization ID can access the table without requiring a grant of any privileges for the table. |
|||
| Indexes and other SQL statement support | Base tables: Indexes and SQL statements that modify data (INSERT, UPDATE, DELETE, and so on) are supported. Indexes can be in different table spaces. | ||
| Created temporary tables: Indexes and SQL statements that modify data (INSERT, UPDATE, DELETE, and so on) are supported. Indexes can only be in the same table space as the table. | |||
| Declared temporary tables: Indexes and SQL statements that modify data (INSERT, UPDATE, DELETE, and so on) are supported. Indexes can only be in the same table space as the table. | |||
| Locking, logging, and recovery | Base tables: Locking, logging, and recovery do apply. | ||
| Created temporary tables: Locking and recovery do not apply, however logging does apply when LOGGED is explicitly specified. Undo recovery (rolling back changes to a savepoint or the most recent commit point) is supported when only when LOGGED is explicitly specified. | |||
| Declared temporary tables: Locking and recovery do not apply, however logging only applies when LOGGED is explicitly or implicitly specified. Undo recovery (rolling back changes to a savepoint or the most recent commit point) is supported when LOGGED is explicitly or implicitly specified. | |||