Temporary Tables
Temporary tables are useful in applications that need to manipulate intermediate results and minimize the processing overhead associated with creating tables.
Temporary tables reduce overhead in the following ways:
• No logging or locking is performed on temporary tables.
• No page locking is performed on temporary tables.
• Disk space requirements are minimized. If possible, the temporary table is created in memory and never written to disk.
• No system catalog entries are made for temporary tables.
Because no logging is performed, temporary tables can be created, deleted, and modified during an online checkpoint.
Temporary tables are:
• Visible only to the session that creates them
• Deleted automatically when the session ends
• Declarable by any user, whether or not the user has the create_table permission
The DECLARE GLOBAL TEMPORARY TABLE statement is used to create temporary (session-scope) tables. In VDBA, use the Create Table dialog.
All temporary tables are automatically deleted at the end of the session. To delete a temporary table before the session ends, issue a DROP TABLE statement.
Temporary Table Declaration and the Optional SESSION Schema Qualifier
The DBMS Server supports two syntaxes for declaring and referencing global temporary tables:
With the SESSION Schema Qualifier
If the DECLARE GLOBAL TEMPORARY TABLE statement defines the table with the SESSION schema qualifier, then subsequent SQL statements that reference the table must use the SESSION qualifier.
When using this syntax, the creation of permanent and temporary tables with the same name is allowed.
Without the SESSION Schema Qualifier
If the DECLARE GLOBAL TEMPORARY TABLE statement defines the table without the SESSION schema qualifier, then subsequent SQL statements that reference the table can optionally omit the SESSION qualifier. This feature is useful when writing portable SQL.
When using this syntax, the creation of permanent and temporary tables with the same name is not allowed.
Note: In both modes, a session table is local to the session, which means that two sessions can declare a global temporary table of the same name and they do not conflict with each other.
Note: Syntaxes cannot be mixed in a single session. For example, if the table is declared with SESSION the first time, all declarations must use SESSION.
Examples of Working with Temporary Tables
To create two temporary tables, names and employees, for the current session, issue the following statements:
DECLARE GLOBAL TEMPORARY TABLE SESSION.names
(name VARCHAR(20), empno VARCHAR(5))
ON COMMIT PRESERVE ROWS
WITH NORECOVERY;
DECLARE GLOBAL TEMPORARY TABLE SESSION.employees AS
SELECT name, empno FROM employees
ON COMMIT PRESERVE ROWS
WITH NORECOVERY;
Note: The “session.” qualifier in the example is optional. If omitted, the name of the temporary table cannot be the same as any permanent table names.
The names of temporary tables must be unique only in a session.
For more information on working with temporary tables, see the descriptions for DECLARE GLOBAL TEMPORARY TABLE and DROP statements.
Last modified date: 01/30/2023