A temporary table is a base table that exists only during a transaction or an SQL session. A temporary table that exists only during a transaction is called a transaction-specific temporary table, and a table that exists only during an SQL session is called a SQL session-specific temporary table.

Temporary tables are not created when the table is defined. A temporary table is created when the first INSERT statement for the table is executed. This is called instantiating a temporary table.

A temporary table is created for each connection that is established for a single table definition (by executing the CONNECT statement). Therefore, a temporary table is not affected by data operations (SELECT, INSERT, UPDATE, and DELETE statements) by another user even when multiple users use temporary tables at the same time. A temporary table and the indexes defined for the temporary table (temporary table index) are stored in a temporary table RDAREA and are deleted automatically when the transaction is completed or the SQL session is terminated. For details about temporary table RDAREAs, see 14.7 Temporary table RDAREAs.

The following figure provides an overview of temporary tables.

Figure 12-46 Overview of temporary tables

Effects of using temporary tables

• If you perform complex processing during a transaction or SQL session, you can use a temporary table as a work table to store intermediate processing results and then obtain final results after further processing.
• If a part of a table containing many data items is accessed frequently during a transaction or SQL session, you can reduce the number of input and output operations by storing the corresponding data in a temporary table, thereby improving performance.
• Because temporary tables are deleted automatically when transactions are completed or SQL statements are terminated, no postprocessing is required by UAPs, thereby reducing the workload for UAP creation.

Criteria for temporary tables

We recommend that you use temporary tables for transactions that frequently access only part of a table containing many data items and for batch jobs that perform complex processing for which intermediate processing results need to be stored temporarily.

Organization of this section

12.22.1 Valid period of data in temporary tables

12.22.2 How to define temporary tables and temporary table indexes

12.22.3 Rules for choosing an RDAREA for storage

12.22.4 Processing when there are no available temporary table RDAREAs

12.22.5 Locking for temporary tables

12.22.6 Limitations on the use of temporary tables