This topic identifies the most common alter table actions
and special considerations in the presence of attached and detached
data partitions.
The STATUS column of the SYSCAT.DATAPARTITIONS
catalog view contains the state information for the partitions of
a table.
- If the STATUS is the empty string, the partition is visible and
is in the normal state.
- If the STATUS is 'A', the partition is newly attached and the
SET INTEGRITY statement must be issued to bring the attached partition
into the normal state.
- If the STATUS is 'D', 'L', or 'I', the partition is being detached,
but the detach operation has not completed.
- For a partition in the 'D' state, the SET INTEGRITY statement
must be issued on all detached dependent tables in order to transition
the partition to the logically detached state.
- For a partition in the 'L' state, the partition is a logically
detached partition and the asynchronous partition detach task is completing
the detach of the partition for DB2® Version
9.7 Fix Pack 1 and later releases.
- For a partition in the 'I' state the asynchronous partition detach
task has completed and asynchronous index cleanup is updating nonpartitioned
indexes defined on the partition.
- Adding or altering a constraint
- Adding a check or a foreign key constraint
is supported with attached and detached data partitions. When a partitioned
table has detached partitions in state 'D' or 'L', adding a primary
or unique constraint will return an error if the system has to generate
a new partitioned index to enforce the constraint. For a partition
in the 'L' state, the operation returns SQL20285N (SQLSTATE 55057).
For a partition in the 'D' state, the operation returns SQL20054 (SQLSTATE
55019).
- Adding a column
- When adding a column to a table with attached data
partitions, the column is also added to the attached data partitions.
When adding a column to a table with detached data partitions in
the 'I' state, the column is not added to the detached data partitions
because the detached data partitions are no longer physically
associated to the table.
For a detached partition in the 'L' or 'D'
state, the operation fails and an error is returned. For a partition
in the 'L' state, the operation returns SQL20285N (SQLSTATE 55057).
For a partition in the 'D' state, the operation returns SQL20296N
(SQLSTATE 55057).
- Altering a column
- When altering a column in a table with attached data partitions,
the column will also be altered on the attached data partitions.
When altering a column in a table with detached data partitions, the
column is not altered on the detached data partitions, because the
detached data partitions are no longer physically associated to the
table.
When dropping or renaming a column when a partition
is detached in the 'L' or 'D' state the operation fails and an error
is returned. For a partition in the 'L' state, the operation returns
SQL20285N (SQLSTATE 55057). For a partition in the 'D' state, the
operation returns SQL0270N (SQLSTATE 42997).
- Adding a generated column
- When adding a generated column to a partitioned table with attached
or detached data partitions, it must respect the rules for adding
any other types of columns.
- Adding or modifying a nonpartitioned index
- When creating, recreating, or reorganizing
an index on a table with attached data partitions, the index does
not include the data in the attached data partitions because the SET
INTEGRITY statement maintains all indexes for all attached data partitions.
When creating, recreating or reorganizing an index on a table with
detached data partitions, the index does not include the data in the
detached data partitions, unless the detached data partition has a
detached dependent table or staging tables that need to be incrementally
refreshed with respect to the data partition, the partition is in
the 'D' state. In this case, the index includes the data for this
detached data partition.
- Adding or modifying a partitioned index
When creating a partitioned index in the presence of attached
data partitions, an index partition for each attached data partition
will be created. The index entries for index partitions on attached
data partitions will not be visible until the SET INTEGRITY statement
is run to bring the attached data partitions online. Note that because
create index includes the attached data partitions, creation of a
unique partitioned index may find rows in the attached data partition
which are duplicate key values and thus fail the index creation.
It is recommended that users do not attempt to create partitioned
indexes in the presence of attached partitions to avoid this problem.
If the table has any detached dependent tables,
creation of partitioned indexes is not supported on partitioned tables
with detached dependent tables. Any attempt to create a partitioned
index in this situation will result in SQLSTATE 55019. When creating
a partitioned index on a table that has partitions in 'L' state, the
operation returns SQL20285N (SQLSTATE 55057).
- WITH EMPTY TABLE
- You cannot empty a table with attached data partitions.
- ADD MATERIALIZED QUERY AS
- Altering a table with attached data partitions to an MQT is not
allowed.
- Altering additional table attributes that are stored in a data
partition
- The following table attributes are also stored in a data partition.
Changes to these attributes are reflected on the attached data partitions,
but not on the detached data partitions.
- DATA CAPTURE
- VALUE COMPRESSION
- APPEND
- COMPACT/LOGGED FOR LOB COLUMNS
DB2 Version 9.7 for Linux, UNIX, and Windows