DB2 Version 9.7 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 the table then adding new data. Table partitioning functionality allows you to detach the data partition with the obsolete data then attach a new data partition with the latest data.

About this task

To rotate data in a partitioned table, from the command line, issue the ALTER TABLE statement. The following example demonstrates how to update the stock table by removing the data from December 2001 and replacing it with the latest data from December 2003.

Remove the old data from table stock.

	ALTER TABLE stock DETACH PARTITION dec01 INTO newtable;

Load the new data. Using LOAD with the REPLACE option overwrites existing data.
```
LOAD FROM data_file OF DEL REPLACE INTO newtable
```
Note: If there are detached dependents, then you must run the SET INTEGRITY statement on the detached dependents before you can load the detached table.
If desired, perform data cleansing. Data cleansing activities include:
- 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 pre-aggregated during roll-in.)

Attach the new data as a new range.

ALTER TABLE stock ATTACH PARTITION dec03 
STARTING '12/01/2003' ENDING '12/31/2003'
 FROM newtable;

Attaching a data partition drains queries and invalidates packages.

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

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, 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 table to attach a data partition, the user must have the following authorities or privileges:

The user performing the attach must have the authority needed to ALTER and to INSERT into the target table
The user must also be able to SELECT from and to DROP the source table. Therefore, to alter a table to attach a data partition, the privileges held by the authorization ID of the statement must include at least one of the following 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