DBSPACETEMP environment variable

The DBSPACETEMP environment variable specifies the dbspaces in which temporary tables are built. The list can include standard dbspaces, temporary dbspaces, or both.


1  setenv DBSPACETEMP + , dbspace
dbspace
is the name of an existing standard or temporary dbspace.
You can list dbspaces, separated by colon ( : ) or comma ( , ) symbols, to designate space for temporary tables across physical storage devices. For example, the following command to set the DBSPACETEMP environment variable specifies three dbspaces for temporary tables: 
setenv DBSPACETEMP sorttmp1:sorttmp2:sorttmp3

DBSPACETEMP overrides any default dbspaces that the DBSPACETEMP parameter specifies in the configuration file of the database server. For UPDATE STATISTICS operations, DBSPACETEMP is used only when you specify the HIGH keyword option.

On UNIX™ platforms, you might have better performance if the list of dbspaces in DBSPACETEMP is composed of chunks that are allocated as raw devices.

The number of dbspaces is limited by the maximum size of the environment variable, as defined by your operating system. Your database server does not create a dbspace specified by the environment variable if the dbspace does not exist.

The two classes of temporary tables are explicit temporary tables that the user creates and implicit temporary tables that the database server creates. Use DBSPACETEMP to specify the dbspaces for both types of temporary tables.

If you create an explicit temporary table with the CREATE TEMP TABLE statement and do not specify a dbspace for the table either in the IN dbspace clause or in the FRAGMENT BY clause, the database server uses the settings in DBSPACETEMP to determine where to create the table.

If you create an explicit temporary table with the SELECT INTO TEMP statement, the database server uses the settings in DBSPACETEMP to determine where to create the table.

If DBSPACETEMP is set, and the dbspaces that it lists include both logging and non-logging dbspaces, the database server stores temporary tables that implicitly or explicitly support transaction logging in a logged dbspace, and non-logging temporary tables in a non-logging dbspace.

The database server creates implicit temporary tables for its own use while executing join operations, SELECT statements with the GROUP BY clause, SELECT statements with the ORDER BY clause, and index builds.

When it creates explicit or implicit temporary tables, the database server uses disk space for writing the temporary data. If there are conflicts among settings or statement specifications for the location of a temporary table, these conflicts are resolved in this descending (highest to lowest) order of precedence:
  1. On UNIX platforms, the operating-system directory or directories that the environment variable PSORT_DBTEMP specifies, if this is set
  2. The dbspace or dbspaces that the environment variable DBSPACETEMP specifies, if this is set
  3. The dbspace or dbspaces that the ONCONFIG parameter DBSPACETEMP specifies.
  4. The operating-system file space specified by the DUMPDIR configuration parameter
  5. The directory $ONEDB_HOME/tmp (UNIX) or $ONEDB_HOME\tmp (Windows™).
Important: If the DBSPACETEMP environment variable is set to an invalid value, the database server defaults to the root dbspace for explicit temporary tables and to /tmp for implicit temporary tables, rather than to the setting of the DBSPACETEMP configuration parameter. In this situation, the database server might fill /tmp to the limit and eventually bring down the database server or kill the file system.