Neither the definition nor the 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 it is needed.
What this means is that 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 as soon as your session logs off.
Volatile tables are always created in the login user space, regardless of the current default database setting. That is, the database name for the table is the login user name. Space usage is charged to login user spool space. Each user session can materialize as many as 1000 volatile tables at a time.
Limitations
The following CREATE TABLE options are not permitted for volatile tables:
- Permanent journaling
- Referential integrity constraints
This means that 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
- PARTITION BY clause
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. This means that you can log on multiple sessions and create volatile tables with the same name in each session.
However, at the time 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 login user.
For example, suppose you log on two sessions, Session 1 and Session 2. Assume the default database name is your login 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 login 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 login user name as the database. |
| 3 | — | Create a volatile table named VT2. | Session 2 receives 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 login 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 login 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, any reference to VT3 in Session 1 is mapped to the volatile table VT3 until it is dropped (see Step 10). On the other hand, in Session 2, references to VT3 remain mapped to the permanent table named VT3. |
| 7 | — | Create volatile table VT3. | Session 2 receives a CREATE TABLE error for attempting 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. |