Neither the definition nor contents of a volatile table persist across a system restart. You must use CREATE TABLE with the VOLATILE keyword to create a new volatile table each time you start a session in which you need the table.
You can create volatile tables as you need them. Being able to create a table quickly provides you with the ability to build scratch tables whenever you need them. Any volatile tables you create are dropped automatically when your session logs off.
Volatile tables are created in the logon user space, regardless of the current default database setting. That is, the database name for the table is the logon user name. Space usage is charged to logon user spool space. Each user session can materialize up to 1000 volatile tables at a time.
Limitations
- Permanent journaling
- Referential integrity constraints
A volatile table cannot be the referencing or referenced table in a referential integrity constraint.
- Check constraints
- Compressed columns
- DEFAULT clause
- TITLE clause
- Named indexes
- Column partitioning
References to volatile tables are not permitted in FastLoad or MultiLoad.
Non-ANSI Extensions
Volatile tables are not defined in ANSI.
Privileges Required
To create a volatile table, you do not need any privileges.
No access logging is performed on volatile tables, so no access log entries are generated.
Volatile Table Maintenance among Multiple Sessions
Volatile tables are private to a session. You can log on multiple sessions and create volatile tables with the same name in each session.
However, when you create a volatile table, the name must be unique among all global and permanent temporary table names in the database that has the name of the logon user.
For example, suppose you log on to Session 1 and Session 2. Assume the default database name is your logon user name. Consider the following scenario.
| Stage | Session 1 | Session 2 | Result |
|---|---|---|---|
| 1 | Create a volatile table named VT1. | Create a volatile table named VT1. | Each session creates its own copy of volatile table VT1 using your logon user name as the database. |
| 2 | Create a permanent table with an unqualified table name of VT2. | — | Session 1 creates a permanent table named VT2 using your logon user name as the database. |
| 3 | — | Create a volatile table named VT2. | Session 2 gets a CREATE TABLE error, because there is already a permanent table with that name. |
| 4 | Create a volatile table named VT3. | — | Session 1 creates a volatile table named VT3 using your logon user name as the database. |
| 5 | — | Create a permanent table with an unqualified table name of VT3. | Session 2 creates a permanent table named VT3 using your logon user name as the database. Because a volatile table is known only to the session that creates it, a permanent table with the same name as the volatile table VT3 in Session 1 can be created as a permanent table in Session 2. |
| 6 | Insert into VT3. | — | Session 1 references volatile table VT3. Volatile tables take precedence over permanent tables in the same database in a session.
Because Session 1 has a volatile table VT3, a reference to VT3 in Session 1 is mapped to the volatile table VT3 until VT3 is dropped. Select from VT3. However, in Session 2, references to VT3 remain mapped to the permanent table named VT3. |
| 7 | — | Create volatile table VT3. | Session 2 gets a CREATE TABLE error for trying to create the volatile table VT3 because of the existence of that permanent table. |
| 8 | — | Insert into VT3. | Session 2 references permanent table VT3. |
| 9 | Drop VT3. | — | Session 2 drops volatile table VT3. |
| 10 | Select from VT3. | — | Session 1 references the permanent table VT3. |