DB2 Version 10.1 for Linux, UNIX, and Windows

History tables

Each system-period temporal table requires a history table. When a row is updated or deleted from a system-period temporal table, the database manager inserts a copy of the old row into its associated history table. This storage of old system-period temporal table data gives you the ability to retrieve data from past points in time.

In order to store row data, the history table columns and system-period temporal table columns must have the same names, order, and data types. You can create a history table with the same names and descriptions as the columns of the system-period temporal table by using the LIKE clause of the CREATE TABLE statement, for example:

CREATE TABLE employees_history LIKE employees IN hist_space;

An existing table can be used as a history table if it avoids the restrictions listed in the description of the ALTER TABLE statement USE HISTORY clause.

After you create a history table, you add versioning to establish the link between the system-period temporal table and the history table.

ALTER TABLE employees ADD VERSIONING USE HISTORY TABLE employees_history;

A history table is subject to the following rules and restrictions when versioning is enabled:

A history table cannot explicitly be dropped. It can only implicitly be dropped when the associated system-period temporal table is dropped.
History table columns cannot explicitly be added, dropped, or changed.
A history table must not be defined as parent, child, or self-referencing in a referential constraint. Access to the history table is restricted to prevent cascaded actions to the history table.
A table space that contains a history table, but not its associated system-period temporal table, cannot be dropped.

You should rarely need to explicitly change a history table. Doing so might jeopardize your ability to audit a system-period temporal table data history. You should restrict access to a history table to protect its data.

Under normal operations, a history table experiences mostly insert and read activities. Updates and deletes are rare. The absence of updates and deletes means that history tables typically do not have free space that can be reused for the inserting of new rows. If row inserts into the history table are negatively impacting workload performance, you can eliminate the search for free space by altering the definition of the history table by using the APPEND ON option. This option avoids the processing associated with free space searches and directly appends new rows to the end of the table.

ALTER TABLE employees_history APPEND ON;

When a system-period temporal table is dropped, the associated history table and any indexes defined on the history table are implicitly dropped. To avoid losing historical data when a system-period temporal table is dropped, you can either create the history table with the RESTRICT ON DROP attribute or alter the history table by adding the RESTRICT ON DROP attribute.

CREATE TABLE employees_history LIKE employees WITH RESTRICT ON DROP;

Because history tables experience more inserts than deletes, your history tables are always growing and so are consuming an increasing amount of storage. Deciding how to prune your history tables to get rid of the rows that you no longer need can be a complex task. You need to understand the value of your individual records. Some content, like customer contracts, might be untouchable and can never be deleted. While other records, like website visitor information, can be pruned without concern. Often it is not the age of a row that determines when it can be pruned and archived, but rather it is some business logic that is the deciding factor. The following list contains some possible rules for pruning:

Prune rows selected by a user-supplied query that reflects business rules.
Prune rows older than a certain age.
Prune history rows when more than N versions exist for that record (retain only the latest N versions).
Prune history rows when the record is deleted from the associated system-period temporal table (when there are no current versions).

There are several ways to periodically prune old data from a history table:

Use range partitioning and detach old partitions from the history table.
Use DELETE statements to remove rows from the table. If using DELETE statements, you might observe the following guidelines:
- Periodically reorganize the history table to release the free space left behind by the delete operations.
- Ensure that the history table was not altered to use the APPEND ON option, allowing inserts to search for free space.