The valid period of data (period in which entities exist) in an instantiated temporary table depends on whether the temporary table is a transaction-specific temporary table or an SQL session-specific temporary table. The table below describes when the valid period of data begins and ends for a temporary table. Figure 12-47 and Figure 12-48 show examples of a valid period of data and the data stored at a given point in time.

Table 12-27 Start and end of a valid period of data for temporary tables

Type of temporary table	Start timing	End timing
Transaction-specific temporary table	When the first INSERT statement is executed on the temporary table during a transaction	When the transaction is completed
SQL session-specific temporary table	When the first INSERT statement is executed on the temporary table during an SQL session	When the SQL session is completed When the back-end server that instantiated the temporary table is terminated When the unit containing the back-end server that instantiated the temporary table is terminated When a system switchover occurs on the back-end server that instantiated the temporary table or on the unit containing that back-end server

Figure 12-47 Example of a valid period of data and the data stored at a given point in time (1)

Explanation:

The following table shows at time T the data contained in temporary tables TMP1 and TMP2 which are used by SQL sessions 1 and 2:

SQL session	Temporary table	Data contained
SQL session 1	TMP1	Data inserted in 4
	TMP2	Data inserted in 1 and 3
SQL session 2	TMP1	No data at this point in time
	TMP2	Data inserted in 1 and 3

Figure 12-48 Example of a valid period of data and the data stored at a given point in time (2)

Explanation:

The following table shows at times T1 and T2 the data contained in temporary table TMP2 which is used by the SQL sessions 1 and 2:

Time	SQL session	Data contained
T1	SQL session 1	There is no data at this point in time, because the table data was deleted in 2. However, there is an instantiation of TMP2.
	SQL session 2	Data inserted in 1
T2	SQL session 1	Data inserted in 3 and 4
	SQL session 2	There is no data at this point in time. There is no instantiation of TMP2, because processing has rolled back to the synchronization point before TMP2 was instantiated.

Note

• Performing search, update, or deletion processing on a temporary table whose valid period of data has expired has the same result as when an SQL statement is executed on an empty table.
• For a HiRDB parallel server configuration, if a back-end server on which a SQL session-specific temporary table has been instantiated (or the unit containing such a back-end server) terminates abnormally or results in a system switchover, the valid period of data ends. Therefore, if data manipulation is attempted on the corresponding temporary table before the SQL session terminates, an SQL error results.