DB2--DB2 Creation of temporary tables

DB2 supports session temp table. The table can only exist in a session or in a transaction context and is not visible to other users. DB2 V8 adds support for indexing of temporary tables, which is useful for increasing query speed.

The following example illustrates the use of the session temp table:

1. Before creating a session temp table, you must create a temporary table space
Create user temporary tablespace test
PageSize 4 K Managed by system
using (' [curdir]\temp ');

2. Define the session temp table, which copies the table structure of transactions, on commit preserve rows to specify that even if the transaction is submitted, the data in the T1 temp table still exists,
The T1 table disappears only when the session is terminated.

If you do not add on commit preserve rows, the data in the temporary table will be automatically deleted when the insert temporary tables are submitted.

Declare global temporary table T1
Like transactions
On commit preserve rows not logged in test;

3. Insert the records of the transaction table into the T1 temporary table.
INSERT INTO SESSION.T1
SELECT * FROM Transactions

4. Select a record from the T1 table, and notice how fast this statement executes.
SELECT * FROM session. T1
Where store= ' Rockwood ';

5. Create an index on the T1 table, DB2 V8 allows you to create an index on a temporary table to increase the query speed on the staging table.
Create INDEX Session.t1index on SESSION.T1 (store);

6. If you execute the same SELECT statement again, you can see that the query speed has improved.
SELECT * FROM session. T1
Where store= ' Rockwood ';

7. In DB2 9, you do not have to create a temporary table space yourself, and a temporary table space Systoolstmpspace is created automatically when you create the staging tables.

DB2--DB2 Creation of temporary tables