Additional system-period temporal table CREATE TABLE examples

Additional examples include hiding columns and changing an existing table into a system-period temporal table.

Hiding columns

The row-begin, row-end, and transaction start-ID columns can be defined as IMPLICITLY HIDDEN. Columns defined as IMPLICITLY HIDDEN do not appear in the select list unless explicitly referenced. Since values for the row-begin, row-end, and transaction start-ID columns are generated by the database manager, hiding them can minimize any potential impact on your existing applications.

For example:

A SELECT * query run against a table does not return any implicitly hidden columns in the result table.
An INSERT statement without a column list does not expect the default value to be specified for any implicitly hidden columns.

The following example creates the POLICY_INFO table with the TIMESTAMP(12) columns SYS_START, SYS_END, and TS_ID definied as implicitly hidden.

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) );

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 defined as hidden in the system-period temporal table must also be defined as hidden in the associated history table. If the column definitions do not match, you will not be able to add versioning.

Changing an existing table into a system-period temporal table

In the following example, the three timestamp columns and a SYSTEM_TIME period are added to the existing table, EMPLOYEES, preparing it to be used as a system-period temporal table.

ALTER TABLE employees 
  ADD COLUMN sys_start TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN
  ADD COLUMN sys_end TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END
  ADD COLUMN ts_id TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID
  ADD PERIOD SYSTEM_TIME(sys_start, sys_end);

A history table must be created and versioning added to finish enabling the tracking of historical rows.