All relevant clauses of the ALTER TABLE statement are supported
for a partitioned table. In addition, the ALTER TABLE statement allows
you to ADD new data partitions, roll-in (ATTACH) new data partitions,
and roll-out (DETACH) existing data partitions.
About this task
Usage guidelines - Each ALTER TABLE statement issued with the PARTITION clause must
be in a separate SQL statement.
- No other ALTER operations are permitted in an SQL statement containing
an ALTER TABLE...PARTITION operation. For example, you cannot attach
a data partition and add a column to the table in a single SQL statement.
- Multiple ALTER statements can be executed, followed by a single
SET INTEGRITY statement.
To alter a partitioned table from the command line, issue the
ALTER TABLE statement.
Before you begin
To alter a partitioned table to detach a data partition the
user must have the following authorities or privileges:
- The user performing the DETACH operation must have the authority
needed to ALTER, to SELECT from, and to DELETE from the source table.
- The user must also have the authority needed to create the target
table. Therefore, to alter a table to detach a data partition, the
privilege held by the authorization ID of the statement must include
at least one of the following authorities or privileges on the target
table:
- DBADM authority
- CREATETAB authority on the database and USE privilege on the table
spaces used by the table as well as one of:
- IMPLICIT_SCHEMA authority on the database, if the implicit or
explicit schema name of the table does not exist
- CREATEIN privilege on the schema, if the schema name of the table
refers to an existing schema.
To alter a partitioned table to attach a data partition, the
privileges held by the authorization ID of the statement must include
at least one of the following authorities or privileges on the source
table:
- DATAACCESS authority or SELECT privilege on
the source table and DBADM authority or DROPIN privilege on the schema
of the source table
- CONTROL privilege on the source table
To alter a partitioned table to add a data partition, the privileges
held by the authorization ID of the statement must have privileges
to use the table space where the new partition is added, and include
at least one of the following authorities or privileges on the source
table:
- ALTER privilege
- CONTROL privilege
- DBADM
- ALTERIN privilege on the table schema
DB2 Version 9.7 for Linux, UNIX, and Windows