DBSPACETEMP

The DBSPACETEMP environment variable specifies the dbspaces in which temporary tables are built

You can list dbspaces, separated by colon ( : ) or comma ( , ) symbols to spread temporary space across any number of disks.

                        .-,------------.
                        V              |
>>-setenv--DBSPACETEMP----temp_dbspace-+-----------------------><

                        .-;------------.
                        V              |
>>-setenv--DBSPACETEMP----temp_dbspace-+-----------------------><

temp_dbspace

is the name of a valid existing temporary dbspace.

DBSPACETEMP overrides any default dbspaces that the DBSPACETEMP parameter specifies in the configuration file of the database server.

For example, the following command to set the DBSPACETEMP environment variable specifies three dbspaces for temporary tables:

setenv DBSPACETEMP sorttmp1:sorttmp2:sorttmp3

Separate the dbspace entries with either colons or commas. 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 not set, the database server uses the ONCONFIG parameter DBSPACETEMP. If this is not set, the database server creates the explicit temporary table in the same dbspace where the database resides.

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 these implicit temporary tables, the database server uses disk space for writing the temporary data, in the following order:

1. The operating-system directory or directories that the environment variable PSORT_DBTEMP specifies, if they are set
2. The dbspace or dbspaces that the environment variable DBSPACETEMP specifies, if they are set
3. The dbspace or dbspaces that the ONCONFIG parameter DBSPACETEMP specifies.
4. The operating-system file space in /tmp (UNIX) or %temp% (Windows)