Creating a system-period temporal table

Creating a system-period temporal table and a corresponding history table that are in a versioning relationship results in two tables that track when data changes occur and preserves historical versions of that data.

When creating a system-period temporal table, you must:

Define your new or existing table so it can be used as a system-period temporal table by:
- Defining row-begin, row-end, and transaction start-ID columns for the database manager to use for maintaining historical times for each row.
- Defining a SYSTEM_TIME period to track when a row is current.
Create a history table to receive old rows from the system-period temporal table.
Add versioning to establish the link between the system-period temporal table and the history table.

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

Create a table with row-begin, row-end, and transaction start-ID columns and a SYSTEM_TIME period.
In the following example, the POLICY_INFO table stores information about a customer's insurance coverage. The SYSTEM_TIME period columns, SYS_START and SYS_END, show when a row is current. The TS_ID column shows the time of the first data change operation in the transaction that impacted the row.
```
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, 
    sys_end    TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END, 
    ts_id      TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID, 
    PERIOD SYSTEM_TIME (sys_start, sys_end) );
```
Create a matching history table. You can create a history table with the same column 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 hist_policy_info LIKE policy_info;
```
Add versioning to the system-period temporal table to establish a link to the history table. For example:
```
ALTER TABLE policy_info ADD VERSIONING USE HISTORY TABLE hist_policy_info;
```

Once all three steps are complete, the HIST_POLICY_INFO history table receives the old rows from the POLICY_INFO table.

The ON DELETE ADD EXTRA ROW clause can optionally be specified on the ALTER TABLE ADD VERSIONING statement. If the clause is specified, an extra row is inserted into the history table when a row is deleted from the system-period temporal table. When the additional row is written to the history table, the values for the generated expression columns, including the row-begin and row-end column, are generated. For more information on how ON DELETE ADD EXTRA ROW can be used for tracking audit information, see Using a system-period temporal table for tracking auditing information.