Where Temporary Tables are Stored

The distribution scheme that you specify with the CREATE Temporary 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.

3If you do not specify an explicit distribution scheme for 3a temporary table, its storage location depends on the DBSPACETEMP (or DBSPACETEMP) 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 a databases that support transaction logging, the temporary table is created in a standard dbspace. The database server keeps track of 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) in order to allocate I/O operations approximately evenly among those dbspaces.

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

Temporary tables created with SELECT... INTO TEMP or SELECT... INTO SCRATCH also behave this way, so that 3DBSPACETEMP (or 3DBSPACETEMP) settings that specify multiple dbspaces result in round-robin fragmentation.

For more information on the DBSPACETEMP environment variable, see the IBM Informix Guide to SQL: Reference. For more information on the DBSPACETEMP configuration parameter, see your IBM Informix Administrator's Reference.

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 temp1;

In Extended Parallel Server, to re-create this example, use the EXECUTE PROCEDURE statement instead of the EXECUTE FUNCTION statement.