SET Default Table Space

Use the SET Default Table Space statement to specify the default storage space used by subsequent statements in the same session that create tables. Only Extended Parallel Server supports this statement, which is an extension to the ANSI/ISO standard for SQL.

Syntax

>>-SET--+------+----TABLE_SPACE  TO----+-dbs_list--+-----------><
        '-TEMP-'                       +-DEFAULT---+
                                       +-IMMUTABLE-+
                                       '-MUTABLE---'

Element	Description	Restrictions	Syntax
dbs_list	A dbspace, dbslice, or a comma-separated list of dbspaces	Must exist	Identifier, p. Identifier

Usage

When the CREATE TABLE statement includes no fragmentation clause, the database server uses the dbspace of the current database as the default storage location. You can use the SET TABLE_SPACE statements to change the default to another dbslice or to a list of one or more dbspaces.

Similarly, you can use the SET TEMP TABLE_SPACE statement to change the default storage location for CREATE Temporary TABLE statements that do not include the Storage Options clause. This statement also sets the default dbspace for SELECT statements that include the INTO Table clause. These defaults persist for the rest of the current session, or until the next SET Default Table Space statement.

Specifying the TO DEFAULT option restores the default behavior.

The DBA can use the MUTABLE or IMMUTABLE keywords to enable (with MUTABLE) or prevent (with IMMUTABLE) changes by users to the default table space. The DBA typically sets the default table space in a sysdbopen procedure, and sets it to IMMUTABLE if the default should not be changed in a user session.