Creating and connecting to created temporary tables

Created temporary tables are created using the CREATE GLOBAL TEMPORARY TABLE statement. The first time an application refers to a created temporary table using a connection, a private version of the created temporary table is instantiated for use by the application using the connection.

About this task

Similar to declared temporary tables, created temporary tables are used by applications that work with data in the database, where the results from manipulation of the data need to be stored temporarily in a table. Whereas declared temporary table information is not saved in the system catalog tables, and must be defined in every session where it is used, created temporary table information is saved in the system catalog and is not required to be defined in every session where it is used, thus making it persistent and able to be shared with other applications, across different connections. A user temporary table space must exist before created temporary tables can be created.
Note: The first implicit or explicit reference to the created temporary table that is executed by any program using the connection creates an empty instance of the given created temporary table. Each connection that references this created temporary table has its own unique instance of the created temporary table, and the instance is not persistent beyond the life of the connection.

References to the created temporary table name in multiple connections refer to the same, single, persistent created temporary table definition, and to a distinct instance of the created temporary table for each connection at the current server. If the created temporary table name that is being referenced is not qualified, it is implicitly qualified using the standard qualification rules that apply to SQL statements.

The owner implicitly has all table privileges on the created temporary table, including the authority to drop it. The owner's table privileges can be granted and revoked, either individually or with the ALL clause. Another authorization ID can access the created temporary table only if it has been granted appropriate privileges.

Indexes and SQL statements that modify data (such as INSERT, UPDATE, and DELETE) are supported. Indexes can only be created in the same table space as the created temporary table.

For the CREATE GLOBAL TEMPORARY TABLE statement: locking and recovery do not apply; logging applies only when the LOGGED clause is specified. For more options, see the CREATE GLOBAL TEMPORARY statement.

Created temporary tables cannot be:
  • Associated with security policies
  • Table partitioned
  • Multidimensional clustering (MDC) tables
  • Insert time clustering (ITC) tables
  • Range-clustered (RCT)
  • Distributed by replication

Materialized query tables (MQTs) cannot be created on created temporary tables.

Created temporary tables do not support the following column types, object types, and table or index operations:
  • XML columns
  • Structured types
  • Referenced types
  • Constraints
  • Index extensions
  • LOAD
  • LOAD TABLE
  • ALTER TABLE
  • RENAME TABLE
  • RENAME INDEX
  • REORG TABLE
  • REORG INDEX
  • LOCK TABLE

For more information, see the CREATE GLOBAL TEMPORARY TABLE statement.

Example

   CREATE GLOBAL TEMPORARY TABLE temptbl
      LIKE empltabl
      ON COMMIT DELETE ROWS
      NOT LOGGED
      IN usr_tbsp

This statement creates a temporary table called temptbl. This table is defined with columns that have exactly the same name and description as the columns of the empltabl. The implicit definition only includes the column name, data type, nullability characteristic, and column default value attributes of the columns in empltab1. All other column attributes including unique constraints, foreign key constraints, triggers, and indexes are not implicitly defined.

A COMMIT always deletes the rows from the table. If there are any HOLD cursors open on the table, they can be deleted using TRUNCATE statement, which is faster, but will normally have to be deleted row by row. Changes made to the temporary table are not logged. The temporary table is placed in the specified user temporary table space, usr tbsp. This table space must exist or the creation of this table will fail.

When an application that instantiated a created temporary table disconnects from the database, the application's instance of the created temporary table is dropped.