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 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 |
|
Figure 12-46 Example of a valid period of data and the data stored at a given point in time (1)
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-47 Example of a valid period of data and the data stored at a given point in time (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. |