Altering table spaces

Use the ALTER TABLESPACE statement to change the description of a table space at the current server.

About this task

Begin general-use programming interface information.

For partition-by-range and partition-by-growth table spaces, most attributes can be changed with ALTER TABLESPACE statements, often through pending definition changes.

Pending definition changes are changes that are not immediately materialized. For detailed information about pending definition changes, how to materialize them, and related restrictions, see Pending data definition changes

Immediate definition changes are changes that are materialized immediately. Most immediate definition changes are restricted while pending definition changes exist for an object. For a list of such restrictions, see Restrictions for pending data definition changes.

However, depending on the type of table space and the attributes that you want to change, you might instead need to drop the table space, and create it again with the new attributes. Many fewer types of changes are supported by ALTER TABLESPACE statements for the deprecated non-UTS table space types. In such cases, it is best to first convert the table space to a partition-by-range or partition-by-growth table space first and then use ALTER TABLESPACE statements with pending definition changes to make the changes.

Procedure

To change the attributes of a table space, use any of the following approaches:

Use the ALTER TABLESPACE statements to change the table space type and attributes, or to enable or disable MEMBER CLUSTER.

For example, you might make the following changes:
- Use the MAXPARTITIONS clause to change the maximum partition size for partition-by-growth table spaces. You can also use this attribute to convert a simple table space, or a single-table segmented (non-UTS) table space to a partition-by-growth table space.
- Use the SEGSIZE clause to convert a partitioned (non-UTS) table space to a partition-by-range table space. For more information, see Converting partitioned (non-UTS) table spaces to partition-by-range universal table spaces.
- FL 508 Use the MOVE TABLE clause to move tables from a multi-table segmented (non-UTS) table space to partition-by-growth table spaces. For more information, see Moving tables from multi-table table spaces to partition-by-growth table spaces.
Drop the table space and create it again with the new attributes, as described in Dropping and re-creating a table space to change its attributes.
For example, some changes are not supported by ALTER TABLESPACE statements, such as the following changes:
- Changing the CCSID to an incompatible value
- Moving the table space to a different database
- Converting a multi-table segmented (non-UTS) table space to a UTS table space type, while keeping the same tables in the UTS table space that were in the segmented table space.
  FL 508 However, if your Db2 function level is V12R1M508 or higher, you can move each of the tables in a multi-table segmented (non-UTS) table space to its own partition-by-growth table space.

What to do next

When ready, materialize any pending definition changes, as described in Materializing pending definition changes.

You can also use the DROP PENDING CHANGES clause to drop all pending definition changes for the table space and for any of the objects in the table space.