Explicit inserts with SELECT...INTO TEMP statements

The database server can insert rows in parallel into explicit temporary tables that you specify in SQL statements of the form SELECT....INTO TEMP.

About this task

For example, the database server can perform the inserts in parallel into the temporary table, temp_table, as the following example shows: 
SELECT * FROM table1 INTO TEMP temp_table

Procedure

To perform parallel inserts into a temporary table:

  1. Set PDQ priority > 0.

    You must meet this requirement for any query that you want the database server to perform in parallel.

  2. Set DBSPACETEMP to a list of two or more dbspaces.

    This step is required because of the way that the database server performs the insert. To perform the insert in parallel, the database server first creates a fragmented temporary table. So that the database server knows where to store the fragments of the temporary table, you must specify a list of two or more dbspaces in the DBSPACETEMP configuration parameter or the DBSPACETEMP environment variable. In addition, you must set DBSPACETEMP to indicate storage space for the fragments before you execute the SELECT...INTO statement.

Results

The database server performs the parallel insert by writing in parallel to each of the fragments in a round-robin fashion. Performance improves as you increase the number of fragments.