Temporary dbspaces

A temporary dbspace is a dbspace reserved exclusively for the storage of temporary tables. It behaves differently from a standard dbspace in many ways.

A temporary dbspace is temporary only in the sense that the database server does not preserve any of its contents when the database server restarts. The database server never drops a temporary dbspace unless it is explicitly directed to do so.

Temporary dbspaces cannot be mirrored by the database server.

Whenever you start the database server, all chunks in temporary dbspaces are recreated from scratch. These chunks can therefore be located on RAM drives if desired.

The database server does not perform logical or physical logging for temporary dbspaces. Because temporary dbspaces are not physically logged, fewer checkpoints and I/O operations occur, which improves performance.

For a temporary table in a standard dbspace, at minimum the server logs table creation, the allocation of extents, and the dropping of the table. In contrast, the database server does not log any operations on tables stored in temporary dbspaces. Logical-log suppression in temporary dbspaces reduces the number of log records to roll forward during logical recovery as well, thus improving the performance during critical downtime.

Temporary dbspaces are never archived by the database server, reducing the size of your storage-space backup.

In addition to temporary tables, the database server uses temporary dbspaces to store the before images of data that is overwritten while backups are occurring and overflow from query processing that occurs in memory. Make sure that you have correctly set the DBSPACETEMP environment variable or parameter to specify dbspaces with enough space for your needs. If there is not enough room in the specified dbspaces, the root dbspace is used. If the root dbspace fills, the backup may fail.

If you have more than one temporary dbspace and execute a SELECT statement into a temporary table, the results of the query are inserted in round robin order.

For detailed instructions on how to create a temporary dbspace, see Creating a temporary dbspace.