Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

Chapter 5. Temporal data management and analytics in an operational warehouse 235

5.3.4 Backup, recovery, and pruning history with range partitioning

Temporal table and history tables are tables like any other in DB2 for all intents

and purposes, and as such they can be range partitioned. This means that

temporal and history tables can use range partitioning to enhance the

implementation of backup and recovery, and archive and pruning activities in the

warehouse solution.

Integrate pruning into procedures: Perform history table pruning as part of

the overall warehouse archival and data removal procedures. It is important to

avoid unintended or unauthorized history data removal. Only grant DELETE,

UPDATE, and INSERT privileges on the history table for the user ID that is

intended to perform the pruning processes.

Partitioning a history table: The partitioning scheme of a history table can

be different from that of its base temporal table. For example, the temporal

base table can be partitioned by month and the history table by year.

However, take care to ensure that any partitions defined for the history table

can absorb rows that are moved from the base to the history table.

236 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition