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
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 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.
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 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.
Procedure
To detach a data partition of a partitioned table, issue
the ALTER TABLE statement with the DETACH PARTITION clause.
Example
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 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;
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.