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 | Base tables | Created temporary tables | Declared temporary tables |
|---|---|---|---|
| Creation, persistence, and ability to share table descriptions | CREATE TABLE statement puts a description of the
table in catalog table SYSTABLES. The table description is persistent
and is shareable across application processes. The name of the table in the CREATE statement can be a two-part or three-part name. If the table name is not qualified, Db2 implicitly qualifies the name using the standard Db2 qualification rules applied to the SQL statements. |
CREATE GLOBAL TEMPORARY TABLE statement puts a
description of the table in catalog table SYSTABLES. The table description
is persistent and is shareable across application processes. The name of the table in the CREATE statement can be a two-part- or three-part name. If the table name is not qualified, Db2 implicitly qualifies the name using the standard Db2 qualification rules applied to the SQL statements. The table space that is used by created temporary tables is reset by the following commands: START DB2, START DATABASE, and START DATABASE(dbname) SPACENAM(tsname), where dbname is the name of the database and tsname is the name of the table space. |
DECLARE GLOBAL TEMPORARY TABLE statement does not
put a description of the table in catalog table SYSTABLES. The table
description is not persistent beyond the life of the application process
that issued the DECLARE statement and the description is known only
to that application process. Thus, each application process could
have its own possibly unique description of the same table. The name of the table in the DECLARE statement can be a two-part or three-part name. If the table name is qualified, SESSION must be used as the qualifier for the owner (the second part in a three-part name). If the table name is not qualified, Db2 implicitly uses SESSION as the qualifier. The table space used by declared temporary tables is reset by the following commands: START DB2, START DATABASE, and START DATABASE(dbname) SPACENAM(tsname), where dbname is the name of the database and tsname is the name of the table space. |
| Table instantiation and ability to share data | CREATE TABLE statement creates one empty instance of the table, and all application processes use that one instance of the table. The table and data are persistent. | 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, or DELETE operation that is executed by any program in the application process creates an empty instance of the given table. Each application process has its own unique instance of the table, and the instance is not persistent beyond the life of the application process. | DECLARE GLOBAL TEMPORARY TABLE statement creates an empty instance of the table for the application process. Each application process has its own unique instance of the table, and the instance is not persistent beyond the life of the application process. |
| References to the table in application processes | References to the table name in multiple
application processes 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, Db2 implicitly qualifies the name using the standard Db2 qualification rules that apply to the SQL statements. The name can be a two-part- or three-part name. |
References to the table name in multiple
application processes refer to the same single persistent table description
but to a distinct instance of the table for each application process
at the current server. If the table name that is being referenced is not qualified, Db2 implicitly qualifies the name using the standard Db2 qualification rules that apply to the SQL statements. The name can be a two-part or three-part name. |
References to that table name in multiple
application processes refer to a distinct description and instance
of the table for each application process 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 qualifier (the first part in a two-part table name or the second part in a three-part name). If the table name is not qualified with SESSION, Db2 assumes the reference is to a base table. |
| Table privileges and authorization | 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. |
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, but only with the ALL
clause; individual table privileges cannot be granted or revoked. Another authorization ID can access the table only if it has been granted ALL privileges for the table. |
PUBLIC implicitly has all table privileges
on the table without GRANT authority and has the authority to drop
the table. These table privileges cannot be granted or revoked. Any authorization ID can access the table without a grant of any privileges for the table. |
| Indexes and other SQL statement support | Indexes and SQL statements that modify data (INSERT, UPDATE, DELETE, and so on) are supported. | Indexes, UPDATE (searched or positioned), and DELETE (positioned only) are not supported. | Indexes and SQL statements that modify data (INSERT, UPDATE, DELETE, and so on) are supported. |
| Locking, logging, and recovery | Locking, logging, and recovery do apply. | Locking, logging, and recovery do not apply. Work files are used as the space for the table. | Some locking, logging, and limited recovery do apply. No row or table locks are acquired. Share-level locks on the table space and DBD are acquired. A segmented table lock is acquired when all the rows are deleted from the table or the table is dropped. Create and drop actions for the table are always logged. Logging of insert, update, and delete operations can be disabled with the NOT LOGGED option. Undo recovery (rolling back changes to a savepoint or the most recent commit point) is supported, but redo recovery (forward log recovery) is not supported. |
| Table space and database operations | Table space and database operations do apply. | Table space and database operations do not apply. | Table space and database operations do apply. |
| Table space requirements and table size limitations | The table can be stored in implicitly created table spaces
and databases. The table cannot span table spaces. Therefore, the size of the table is limited by the table space size (as determined by the primary and secondary space allocation values that are specified for the table space's data sets) and the shared usage of the table space among multiple users. When the table space is full, an error occurs for the SQL operation. |
The table is stored in table spaces
in the work file database. The table can span work file table spaces. Therefore, the size of the table is limited by the number of available work file table spaces, the size of each table space, and the number of data set extents that are allowed for the table spaces. Unlike the other types of tables, created temporary tables do not reach size limitations as easily. |
The table is stored in a table space in the work file
database. The table cannot span table spaces. Therefore, the size of the table is limited by the table space size (as determined by the primary and secondary space allocation values that are specified for the table space's data sets) and the shared usage of the table space among multiple users. When the table space is full, an error occurs for the SQL operation. |