DB2 10.5 for Linux, UNIX, and Windows
Altering partitioned tables
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.
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 PARTITION operation must have the authority necessary to ALTER, to SELECT from, and to DELETE from the source table.
- The user must also have the authority necessary 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
About this task
- 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.
Procedure
To alter a partitioned table from the command line, issue
the ALTER TABLE statement.