DB2 10.5 for Linux, UNIX, and Windows

Dropping a system-period temporal table

Dropping a system-period temporal table also drops its associated history table and any indexes defined on the history table.

Before you begin

To drop a system-period temporal table, you must be authorized to drop its history table.

About this task

A history table is implicitly dropped when its associated system-period temporal table is dropped. A history table cannot be explicitly dropped by using the DROP statement.

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. If you try to drop a system-period temporal table and its history table has the RESTRICT ON DROP attribute, the drop of the system-period temporal table fails (SQLSTATE 42893). In such cases, you must break the link between the system-period temporal table and the history table by removing the VERSIONING attribute and then rerun the DROP statement.

When a table is altered to drop VERSIONING, all packages with the versioning dependency on the table are invalidated. Other dependent objects, for example, views or triggers are marked invalid 'N' in the system catalog. Auto-revalidation is done. Any objects failing revalidation are left as invalid in the catalog. Some objects can become valid after only explicit user action.

Procedure

To drop a system-period temporal table and its associated history table:

Optional: Protect historical data from deletion:
1. If the history table was not created with the RESTRICT ON DROP attribute, alter the history table to set the RESTRICT ON DROP attribute. For example, if audit requirements made it necessary to preserve the history of insurance policies then the history table must be protected.
```
ALTER TABLE hist_policy_info ADD RESTRICT ON DROP;
```
2. Break the link between the system-period temporal table and a history table with RESTRICT ON DROP attribute by removing the VERSIONING attribute. For example:
```
ALTER TABLE policy_info DROP VERSIONING; 
```
Drop the system-period temporal table with the DROP statement. For example, the insurance policy tables created in the example in the Creating a system-period temporal table topic are no longer required.
```
DROP TABLE policy_info;   
```

Results

The preceding commands affect the policy_info and hist_policy_info tables as follows:

The DROP statement explicitly drops the system-period temporal table and implicitly drops the associated history table. The policy_info and hist_policy_info tables are deleted. Any objects that are directly or indirectly dependent on those tables are either deleted or made inoperative.
After the RESTRICT ON DROP attribute is associated with the history table, any attempt to drop the policy_info table would fail (SQLSTATE 42893). A system-period temporal table can also be created or altered to use the RESTRICT ON DROP attribute.
After the link between the system-period temporal table and its history table is broken, the policy_info table can be dropped and the hist_policy_info history table would remain.

Dropping table spaces

If a table space contains a history table, but does not contain the associated system-period temporal table, that table space cannot be explicitly dropped. For example, using the insurance policy tables that were created in the policy_space and hist_space table spaces, the following statement is blocked:

DROP TABLESPACE hist_space;

If table space that contains a history table and the table space containing the associated system-period temporal table are included together, then the statement is allowed. For example, the following statement would succeed:

DROP TABLESPACE policy_space hist_space;

A history table is implicitly dropped when the table space for its associated system-period temporal table is dropped. For example, the following statement would drop the hist_policy_info history table:

DROP TABLESPACE policy_space;