Where temporary tables are stored

The distribution scheme that you specify with the CREATE TEMP TABLE statement (either with the IN clause or the FRAGMENT BY clause) takes precedence over the information that the DBSPACETEMP environment variable or the DBSPACETEMP configuration parameter specifies.

If you do not specify an explicit distribution scheme for a temporary table, its storage location depends on the DBSPACETEMP environment variable (or DBSPACETEMP configuration parameter) setting.

  • If DBSPACETEMP and DBSPACETEMP are not set, all temporary tables are created without fragmentation in the same dbspace where the database was created (or in rootdbs, if the database was not created in another dbspace).
  • If only one dbspace for temporary tables is specified by DBSPACETEMP (or by DBSPACETEMP, if DBSPACETEMP is not set), all temporary tables are created without fragmentation in the specified dbspace.
  • If DBSPACETEMP (or DBSPACETEMP, if DBSPACETEMP is not set) specifies two or more dbspaces for temporary tables, then each temporary table is created in one of the specified dbspaces.

    In a non-logging database, each temporary table is created in a temporary dbspace; in databases that support transaction logging, the temporary table is created in a standard dbspace. The database server tracks which of these dbspaces was most recently used, and when it receives the next request to allocate temporary storage, the database server uses the next available dbspace (in a round-robin pattern) to allocate I/O operations evenly among the dbspaces.

For example, if you create three temporary tables in a database with logging where DBSPACETEMP specifies tempspc1, tempspc2, and tempspc3 as the default dbspaces for temporary tables, then the first table is created in the dbspace called tempspc1, the second table is created in tempspc2, and the third one is created in tempspc3, if these are the only requests for temporary storage.

Temporary tables created with SELECT INTO TEMP and WITH NO LOG are spread across the dbspaces listed in the DBSPACETEMP configuration parameter or DBSPACETEMP environment variable. Thus, the DBSPACETEMP (or DBSPACETEMP) settings that specify multiple dbspaces can result in round-robin fragmentation across all dbspaces in the temporary dbspace.

If you create a temporary table and specify WITH NO LOG, operations on the temporary table are not included in the transaction log records. If there is a logged space in the DBSPACETEMP list, the temporary table created with the SELECT .. INTO TEMP WITH NO LOG option is fragmented by a round-robin distribution scheme in the non-logged temporary dbspaces. For example, if from a list of 10 dbspaces, only one dbspace is logged, the table is fragmented by a round-robin distribution scheme in the 9 non-logged temporary dbspaces.

The following example shows how to insert data into a temporary table called result_tmp to output to a file the results of a user-defined function (f_one) that returns multiple rows: 
CREATE TEMP TABLE result_tmp( ... );
INSERT INTO result_tmp EXECUTE FUNCTION f_one();
UNLOAD TO 'file' SELECT * FROM result_tmp;