12.22.1 Valid period of data in temporary tables

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. Figures 12-46 and 12-47 show examples of a valid period of data and the data stored at a given point in time.

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

Type of temporary tableStart timingEnd timing
Transaction-specific temporary tableWhen the first INSERT statement is executed on the temporary table during a transactionWhen the transaction is completed
SQL session-specific temporary tableWhen 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-46 Example of a valid period of data and the data stored at a given point in time (1)

[Figure]

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 sessionTemporary tableData contained
SQL session 1TMP1Data inserted in 4
TMP2Data inserted in 1 and 3
SQL session 2TMP1No data at this point in time
TMP2Data inserted in 1 and 3

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

[Figure]
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:
TimeSQL sessionData contained
T1SQL session 1There 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 2Data inserted in 1
T2SQL session 1Data inserted in 3 and 4
SQL session 2There 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, 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.