DB2 10.5 for Linux, UNIX, and Windows

Scenario: Rotating data in a partitioned table

Rotating data in DB2® databases refers to a method of reusing space in a data partition by removing obsolete data from a table (a detach partition operation) and then adding new data (an attach partition operation).

Before you begin

Alternatively, you can archive the detached partition and load the new data into a different source table before an attach operation is performed. In the following scenario, a detach operation precedes the other steps; it could as easily be the last step, depending on your specific requirements.

To alter a table to detach a data partition, the authorization ID of the statement must hold the following privileges and authorities:

At least one of the following authorities on the target table of the detached partition:
- CREATETAB authority on the database, and USE privilege on the table spaces used by the table, as well as one of the following authorities or privileges:
  - IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the new table does not exist
  - CREATEIN privilege on the schema, if the schema name of the new table refers to an existing schema
- DBADM authority
At least one of the following privileges and authorities on the source table:
- SELECT, ALTER, and DELETE privileges on the table
- CONTROL privilege on the table
- DATAACCESS authority

To alter a table to attach a data partition, the authorization ID of the statement must include the following privileges and authorities:

At least one of the following authorities or privileges on the source table:
- SELECT privilege on the table and DROPIN privilege on the schema of the table
- CONTROL privilege on the table
- DATAACCESS authority
A least one of the following authorities or privileges on the target table:
- ALTER and INSERT privileges on the table
- CONTROL privilege on the table
- DATAACCESS authority

Procedure

To rotate data in a partitioned table, issue the ALTER TABLE statement. The following example shows how to update the STOCK table by removing the data from December 2008 and replacing it with the latest data from December 2010.

Remove the old data from the STOCK table.

ALTER TABLE stock DETACH PARTITION dec08 INTO newtable;

Load the new data. Using the LOAD command with the REPLACE option overwrites existing data.
```
LOAD FROM data_file OF DEL REPLACE INTO newtable
```
Note: If there are detached dependents, issue the SET INTEGRITY statement on the detached dependents before loading the detached table. If SQL20285N is returned, wait until the asynchronous partition detach task is complete before issuing the SET INTEGRITY statement again.
If necessary, perform data cleansing activities, which can include the following actions:
- Filling in missing values
- Deleting inconsistent and incomplete data
- Removing redundant data arriving from multiple sources
- Transforming data
  - Normalization. Data from different sources that represents the same value in different ways must be reconciled as part of rolling the data into the warehouse.
  - Aggregation. Raw data that is too detailed to store in the warehouse must be aggregated before being rolled in.

Attach the data as a new range.

ALTER TABLE stock
  ATTACH PARTITION dec10
    STARTING '12/01/2008' ENDING '12/31/2010'
  FROM newtable;

Use the SET INTEGRITY statement to update indexes and other dependent objects. Read and write access is permitted during execution of the SET INTEGRITY statement.
```
SET INTEGRITY FOR stock
  ALLOW WRITE ACCESS
  IMMEDIATE CHECKED
  FOR EXCEPTION IN stock USE stock_ex;
```