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-45 Overview of temporary tables
![[Figure]](figure/zu122200.gif)
- 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