You use ALTER SPACE to modify the parameters of the catalog space or of a user space.
The SPACE view of the INFORMATION_SCHEMA provides you with information on which user spaces have been defined (see chapter "Information schemas").
The current authorization identifier must own the space. If the storage group is modified, the current authorization identifier must have the special privilege USAGE for the new storage group.
ALTER SPACE space
[PCTFREE percent | NO LOG] ...
[USING STOGROUP stogroup]
You must specify at least one of the parameters PCTFREE, NO LOG or USING STOGROUP, and each parameter may only be specified once.
space
Name of the space for which parameters are to be modified.
You can qualify the space name with a database name.
The universal user may specify the space name "CATALOG" (in double quotes) even if he/she is not the owner of the space. The NO LOG parameter may not be specified here.
PCTFREE percent
Free space reservation in the space file expressed as a percentage. percent must be an unsigned integer between 0 and 70. The modified free space reservation is not evaluated until the next time the database is reorganized with the REORG utility statement.
PCTFREE percent omitted:
The setting for the free space reservation remains unchanged.
NO LOG
Deactivate logging.
Logging is deactivated immediately after the current transaction is terminated with the COMMIT statement.
NO LOG omitted:
The logging setting remains unchanged.
USING STOGROUP stogroup
The name of the storage group containing the volumes to be used for the space file. The new storage group is not evaluated until the next time the database is recovered or reorganized with the utility statements RECOVER and REORG respectively.
You can qualify the name of the storage group with a database name. This database name must be the same as the database name of the space.
USING STOGROUP stogroup omitted:
The storage group for the space remains unchanged.
Example
This example shows how to modify the free space reservation and the storage group for a space.
ALTER SPACE indexspace PCTFREE 20 USING STOGROUP stogroup3
See also
CREATE SPACE, CREATE STOGROUP