DBSPACETEMP

DBSPACETEMP gives the administrator the option to specify the location of temporary tables. Use of this parameter enables the database server to spread out I/O for temporary tables efficiently across multiple disks. The database server also uses temporary dbspaces during backups to store the before- images of data that is overwritten while the backup is occurring.

For the order of precedence that the database server uses when it creates implicit sort files, see your IBM Informix: Performance Guide.

If a client application needs to specify an alternative list of dbspaces to use for its temporary-table locations, the client can use the DBSPACETEMP environment variable to enumerate them.

onconfig.std
value

NOTCRITICAL

units

None

range of values

ALL allows the database server to use all dbspaces at each node for temporary storage, giving preference to temporary dbspaces.

NOTCRITICAL allows the database server to use all dbspaces except the rootdbs and other dbspaces that it treats as critical (for example, the dbspace that contains log files).

TEMP restricts the database server to use only temporary dbspaces for temporary storage.

A list of dbspace names or dbslice names, separated by colons or commas.

takes effect

Used with the keywords ALL, NOTCRITICAL, or TEMP, DBSPACETEMP can appear only in the ONCONFIG file, so it takes effect when the database server is brought online.

Alternately, you can specify a list of dbspaces or dbslices with the DBSPACETEMP environment variable. These items take effect for the current session only.

utilities

onutil CREATE TEMP DBSLICE
(See CREATE DBSLICE and Defining Temporary Dbspaces in a Dbslice.)

onutil CREATE TEMP DBSPACE
(See CREATE DBSPACE and Defining a Temporary Dbspace .)

onutil SET
(See SET)

refer to

What is a temporary dbspace in the chapter on where data is stored in the IBM Informix: Administrator's Guide

The DBSPACETEMP configuration parameter gives the database administrator the option of restricting which dbspaces or dbslices the database server uses for temporary storage.

By default, Extended Parallel Server uses all noncritical dbspaces, giving preference to dbspaces marked temporary. The database server never uses the root dbspace or the logging dbspace when DBSPACETEMP is set to NOTCRITICAL.

When your queries use temporary space on multiple coservers, you can create a temporary dbslice to facilitate management of these temporary dbspaces.

The list of dbslices can contain standard dbslices, temporary dbslices, or both. Use a colon or comma to separate the dbslices in your list.

The list of dbspaces can contain standard dbspaces, temporary dbspaces, or both.

If the DBSPACETEMP configuration parameter or environment variable contains both standard and temporary dbspaces or dbslices, the following rules apply:

• Sort, backup, implicit, and nonlogging explicit temporary tables are created in temporary dbspaces if adequate space exists.
• Explicit temporary tables created without the WITH NO LOG option are created in standard (rather than temporary) dbspaces.

For the order of precedence that the database server uses when it creates implicit sort files, see your IBM Informix: Performance Guide.

If an individual client application needs to specify an alternative list of dbspaces to use for its temporary-table locations, the client can use the DBSPACETEMP environment variable to enumerate them. For information about the DBSPACETEMP environment variables, see the IBM Informix: Guide to SQL Reference.