Purpose
Create a new ANSI bitemporal table.
Syntax
There is no special temporal syntax for creating bitemporal tables. It simply combines the forms discussed in CREATE TABLE (ANSI System-Time Table Form) and CREATE TABLE (ANSI Valid-Time Table Form). Note that a bitemporal table also combines the rules, restrictions, and usage notes listed for both system-time and valid-time tables..
For example, a bitemporal table cannot be the source table for a CREATE TABLE ... AS statement, and statistics cannot be collected on the derived period columns of bitemporal tables.
All restrictions for system-time and valid-time columns described in Working With ANSI System-Time Tables and Working With ANSI Valid-Time Tables apply to bitemporal tables.
Example: Creating an ANSI Bitemporal Table
The following example creates an ANSI bitemporal table.
CREATE MULTISET TABLE employee_bitemporal ( eid INTEGER NOT NULL, ename VARCHAR(5), deptno INTEGER NOT NULL, terms VARCHAR(5), job_start DATE NOT NULL, job_end DATE NOT NULL, PERIOD FOR job_dur(job_start,job_end) AS VALIDTIME, sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL GENERATED ALWAYS AS ROW START, sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME(sys_start,sys_end) ) PRIMARY INDEX (eid) WITH SYSTEM VERSIONING;