INTO TEMP clause

The INTO TEMP clause creates a temporary table to hold the query results.

The default initial extent and next extent for a temporary table that the INTO TEMP clause creates are each eight pages. The temporary table must be accessible by the built-in RSAM access method of the database server; you cannot specify another access method.

If you use the same query results more than once, using a temporary table saves time. In addition, using an INTO TEMP clause often gives you clearer and more understandable SELECT statements.

Data values in a temporary table are static; they are not updated as changes are made to the tables that were used to build the temporary table. You can use the CREATE INDEX statement to create indexes on a temporary table.

A logged temporary table exists until one of the following events occurs:
  • The application disconnects from the database.
  • A DROP TABLE statement is issued on the temporary table.
  • The database is closed.
A nonlogging temporary table exists until one of the following events occurs:
  • The application disconnects from the database.
  • A DROP TABLE statement is issued on the temporary table.

If your HCL OneDB™ database does not have transaction logging, the temporary table behaves in the same way as a table created with the WITH NO LOG option.

If you specify more than one temporary dbspace in the DBSPACETEMP environment variable (or if this is not set, in the DBSPACETEMP configuration parameter), the INTO TEMP clause loads the rows of the results set of the query into each of these dbspaces in round-robin fashion. For more information about the storage location of temporary tables that queries with the INTO TEMP clause create, see Where temporary tables are stored.

Because operations on nonlogging temporary tables are not logged, using the WITH NO LOG option reduces the overhead of transaction logging.

Because nonlogging temporary tables do not disappear when the database is closed, you can use a nonlogging temporary table to transfer data from one database to another while the application remains connected. The behavior of a temporary table that you create with the WITH NO LOG option of the INTO TEMP clause resembles that of a RAW table.

For more information about temporary tables, see CREATE TEMP TABLE statement.