Dropping data partitions
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.
Before you begin
- The user performing the DETACH operation must have the authority to ALTER, to SELECT from and to DELETE from the source table.
- The user must also have the authority 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.
- 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
Procedure
Example
ALTER TABLE stock DETACH PART dec01 INTO junk;
-- After the target table becomes available, issue the DROP TABLE statement
DROP TABLE junk;
What to do next
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.