Configure dbspaces for temporary tables and sort files

Applications that use temporary tables or large sort operations require a large amount of temporary space. To improve performance of these applications, use the DBSPACETEMP configuration parameter or the DBSPACETEMP environment variable to designate one or more dbspaces for temporary tables and sort files.

Depending on how the temporary space is created, the database server uses the following default locations for temporary table and sort files when you do not set DBSPACETEMP:
  • The dbspace of the current database, when you create an explicit temporary table with the TEMP TABLE clause of the CREATE TABLE statement and do not specify a dbspace for the table either in the IN dbspace clause or in the FRAGMENT BY clause

    This action can severely affect I/O to that dbspace. If the root dbspace is mirrored, you encounter a slight double-write performance penalty for I/O to the temporary tables and sort files.

  • The root dbspace when you create an explicit temporary table with the INTO TEMP option of the SELECT statement

    This action can severely affect I/O to the root dbspace. If the root dbspace is mirrored, you encounter a slight double-write performance penalty for I/O to the temporary tables and sort files.

  • The operating-system directory or file that you specify in one of the following variables:
    • In UNIX™, the operating-system directory or directories that the PSORT_DBTEMP environment variable specifies, if it is set

      If PSORT_DBTEMP is not set, the database server writes sort files to the operating-system file space in the /tmp directory.

    • In Windows™, the directory specified in TEMP or TMP in the User Environment Variables window on Control Panel > System.

    The database server uses the operating-system directory or files to direct any overflow that results from the following database operations:

    • SELECT statement with GROUP BY clause
    • SELECT statement with ORDER BY clause
    • Hash-join operation
    • Nested-loop join operation
    • Index builds
Warning: If you do not specify a value for the DBSPACETEMP configuration parameter or the DBSPACETEMP environment variable, the database server uses this operating-system file for implicit temporary tables. If this file system has insufficient space to hold a sort file, the query that performs the sort returns an error. Meanwhile, the operating system might be severely impacted until you remove the sort file.

You can improve performance with the use of temporary dbspaces that you create exclusively to store temporary tables and sort files. Use the DBSPACETEMP configuration parameter and the DBSPACETEMP environment variable to assign these tables and files to temporary dbspaces.

When you specify dbspaces in either the DBSPACETEMP configuration parameter or the DBSPACETEMP environment variable, you gain the following performance advantages:
  • Reduced I/O impact on the root dbspace, production dbspaces, or operating-system files
  • Use of parallel sorts into the temporary files (to process query clauses such as ORDER BY or GROUP BY, or to sort index keys when you execute CREATE INDEX) when you specify more than one dbspace for temporary tables and PDQ priority is set to greater than 0.
  • Improved speed with which the database server creates temporary tables when you assign two or more temporary dbspaces on separate disks
  • Automatic fragmentation of the temporary tables across dbspaces when SELECT....INTO TEMP statements are run

The following table shows statements that create temporary tables and information about where the temporary tables are created.

Statement That Creates Temporary Table Database Logged WITH NO LOG clause FRAGMENT BY clause Where Temp Table Created
CREATE TEMP TABLE Yes No No Root dbspace
CREATE TEMP TABLE Yes Yes No One of dbspaces that are specified in DBSPACETEMP
CREATE TEMP TABLE Yes No Yes Cannot create temp table. Error 229/196
SELECT ..INTO TEMP Yes Yes No Fragmented by round-robin only in the non-logged dbspaces that are specified in DBSPACETEMP
Important: Use the DBSPACETEMP configuration parameter or the DBSPACETEMP environment variable for better performance of sort operations and to prevent the database server from unexpectedly filling file systems. The dbspaces that you list must be composed of chunks that are allocated as unbuffered devices.