The SET ENVIRONMENT USABLE_SPACES Statement

When the user is connected to a database, USABLE_SPACES can also be set by using the following new syntax of the SET ENVIRONMENT statement:

                                       .-,-----.
                                       V       |
>>-SET ENVIRONMENT USABLE_SPACES--+-'----space-+--'-+----------><
                                  +-'DEFAULT SPACE'-+
                                  +-DEFAULT---------+
                                  +-MUTABLE---------+
                                  '-IMMUTABLE-------'
 

Here space is the name of an existing dbspace or dbslice.

The list of spaces or the keyword that follows the USABLE_SPACES keyword has the following effect:

• 'space' (or the comma-separated list of space names) defines the only storage spaces that the previously listed DDL statements can specify in the session as the storage location for tables, indexes, fragments, and databases.
• 'DEFAULT SPACE' references whatever storage the most recently executed SET TABLE_SPACE or (for temporary tables) SET TEMP TABLE_SPACE statement in the same session specified.
• DEFAULT (by itself and unquoted) disables this feature, so that storage space in DDL operations of the session is unrestricted. This option has an effect only if USABLE_SPACES has been set.
• MUTABLE enables any user to reset the USABLE_SPACES session variable. Only the DBA can change the mutability attribute of USABLE_SPACES.
• IMMUTABLE prevents any user who is neither the DBA nor the owner of the database from executing the SET ENVIRONMENT USABLE_SPACES statement. The DBA access privilege is required to change the mutability attribute of USABLE_SPACES.

The SET ENVIRONMENT USABLE_SPACES statement affects only the current session.

By default, any user with Resource privilege can create database objects in any dbspace. In order for this feature to restrict where users create databases and database objects, USABLE_SPACES must be set prior to running any statements that create database objects, such as CREATE TABLE and CREATE INDEX, and the SET ENVIRONMENT USABLE_SPACES statement must be executed each time a new database is opened. The best method to ensure this is to create a sysdbopen( ) procedure for each user or PUBLIC that includes this statement.

To restrict where databases are created, USABLE_SPACES must either be set at the shell with the IBM_XPS_PARAMS environment variable, or else by creating the database while attached to a database where USABLE_SPACES has been set for the session by the SET ENVIRONMENT statement.