Creating a system-period temporal table results in a table that tracks when data changes occur and preserves historical versions of that data.
The example in the following section shows the creation of a table that stores policy information for the customers of an insurance company.
To create a system-period temporal table.
policy_id | coverage | sys_start | sys_end | ts_id |
---|---|---|---|---|
policy_id | coverage | sys_start | sys_end | ts_id |
---|---|---|---|---|
CREATE TABLE policy_info
(
policy_id CHAR(4) NOT NULL,
coverage INT NOT NULL,
sys_start TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN IMPLICITLY HIDDEN,
sys_end TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END IMPLICITLY HIDDEN,
ts_id TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID IMPLICITLY HIDDEN,
PERIOD SYSTEM_TIME (sys_start, sys_end)
) in policy_space;
Creating
the hist_policy_info history table using the LIKE
clause of the CREATE TABLE statement results in the history table
inheriting the implicitly hidden attribute from the policy_info table.
If you do not use the LIKE clause when creating the history table,
then any columns marked as hidden in the system-period temporal table
must also be marked as hidden in the associated history table.ALTER TABLE employees
ADD COLUMN sys_start TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN;
ALTER TABLE employees
ADD COLUMN sys_end TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END;
ALTER TABLE employees
ADD COLUMN ts_id TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID;
ALTER TABLE employees ADD PERIOD SYSTEM_TIME(sys_start, sys_end);
These
new columns can be hidden by including the IMPLICITLY HIDDEN clause
in the ALTER TABLE statementA history table must be created and versioning added to finish this task.