Description
The DECLARE GLOBAL TEMPORARY TABLE statement creates a temporary table, also referred to as a session-scope table. Temporary tables are useful in applications that need to manipulate intermediate results and want to minimize the processing overhead associated with creating tables.
Temporary tables have the following characteristics:
• Temporary tables are visible only to the session that created them.
• Temporary tables are deleted when the session ends (unless deleted explicitly by the session). They do not persist beyond the duration of the session.
• Temporary tables can be created, deleted, and modified during an online checkpoint.
The SESSION schema qualifier on the table name is optional, as described in
SESSION Schema Qualifier (see page
SESSION Schema Qualifier).
If the LOCATION parameter is omitted, the temporary table is located on the default database location (if the temporary table requires disk space). If the subselect is omitted, the temporary table is created as a heap. If the LOCATION parameter is included, its value can be ii_database or the name of an alternate location that has been previously created and marked as a work location.
When a transaction is rolled back, any temporary table that was in the process of being updated is dropped (because the normal logging and recovery processes are not used for temporary tables).
Note: If II_DECIMAL is set to comma, you must follow any comma required in SQL syntax (such as a list of table columns or SQL functions with several parameters) by a space. For example:
SELECT col1, IFNULL(col2, 0), LEFT(col4, 22) FROM version;