To drop a data partition, you detach the partition,
and drop the table created by the detach operation. Use the ALTER
TABLE statement with the DETACH PARTITION clause
to detach the partition and create a stand-alone table, and use the
DROP TABLE statement to drop the table.
About this task
To detach a data partition of a partitioned table,
issue the ALTER TABLE statement with the DETACH
PARTITION clause.
In the following example, the dec01 data partition
is detached from table STOCK and placed in table JUNK. After ensuring
that the asynchronous partition detach task has made the target table
JUNK available, you can drop the table JUNK, effectively dropping
the associated data partition.
ALTER TABLE stock DETACH PART dec01 INTO junk;
-- After the target table becomes available, issue the DROP TABLE statement
DROP TABLE junk;
Note: To make
the ALTER TABLE...DETACH as fast as possible with DB2® Version 9.7 Fix Pack 1 and later releases,
the asynchronous partition detach task completes the detach operation
asynchronously. If there are detached dependent tables, the asynchronous
partition detach task does not start and the detached data partition
does not become a stand-alone table. In this case, the SET INTEGRITY
statement must be issued on all detached dependent tables. After SET
INTEGRITY completes, the asynchronous partition detach task starts
and makes the target table accessible. When the target table is accessible
it can be dropped.
Before you begin
To detach a data partition from a partitioned table 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, in order 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 on the target able:
- 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 drop a table the user must have the following authorities
or privileges:
- You must either be the definer as recorded in the DEFINER column
of SYSCAT.TABLES, or have at least one of the following privileges:
- DBADM authority
- DROPIN privilege on the schema for the table
- CONTROL privilege on the table
Note: The implication of the detach data partition case is
that the authorization ID of the statement is going to effectively
issue a CREATE TABLE statement and therefore must have the necessary
privileges to perform that operation. The table space is the one where
the data partition that is being detached already resides. The authorization
ID of the ALTER TABLE statement becomes the definer of the new table
with CONTROL authority, as if the user had issued the CREATE TABLE
statement. No privileges from the table being altered are transferred
to the new table. Only the authorization ID of the ALTER TABLE statement
and DBADM or SYSADM have access to the data immediately after the
ALTER TABLE...DETACH PARTITION operation.