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-+-----------------------><
 

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 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. What the IN or FRAGMENT BY clause of a DDL or DML statement specifies
2. For Extended Parallel Server, what a SET TEMP TABLE_SPACE statement specifies
3. On UNIX platforms, the operating-system directory or directories that the environment variable PSORT_DBTEMP specifies, if this is set
4. The dbspace or dbspaces that the environment variable DBSPACETEMP specifies, if this is set
5. The dbspace or dbspaces that the ONCONFIG parameter DBSPACETEMP specifies.
6. The dbspace or dbspaces that the ONCONFIG parameter TABLESPACE specifies.
7. The operating-system file space in /tmp (UNIX) or %temp% (Windows)
8. For Extended Parallel Server, in a non-criticl space, if none of the above are specified
9. For Dynamic Server, in the space where the database was created, if none of the above are specified