Purpose
Temporal syntax for ALTER TABLE allows you to create ANSI temporal system-time tables from existing tables.
Adding system-time to the table involves these ALTER TABLE operations:
Syntax
Use the following ALTER TABLE syntax to create a system-time table by adding a system-time derived period column and the component start and end columns in a single ALTER TABLE statement:
A system-time table is not considered to be a temporal table, and is not afforded any special temporal behaviors until system versioning has been added to the table. Add system versioning to the system-time table using the following syntax in a separate ALTER TABLE statement:
|
Syntax Element |
Description |
|
table_name |
The name of the system-time table. May include database qualifier. |
|
PERIOD FOR SYSTEM_TIME |
Creates the system-time derived period column. |
|
sys_start |
The name of the column that will store the beginning bound of the system-time period. |
|
GENERATED ALWAYS AS ROW START |
Required attribute for column that defines the beginning bound of system-time period. |
|
sys_end |
The name of the column that will store the ending bound of the system-time period. |
|
GENERATED ALWAYS AS ROW END |
Required attribute for column that defines the ending bound of system-time period. |
|
SYSTEM VERSIONING |
Required attribute for system-time temporal tables. |
Note: The ALTER TABLE statement components must be in the order shown, with the derived period column defined before the component start and end columns. There is no comma between the ADD clauses in this case.
This is ANSI SQL:2011 compliant.
Usage Notes
After a table has been converted to a system-versioned system-time table, most ALTER TABLE operations are not allowed. These tables are typically used for regulatory and compliance purposes, and modifications to the table structure could defeat those purposes. To restore a system-versioned system-time table to a nontemporal table involves dropping the system versioning, after which all normal ALTER TABLE operations are allowed.
For information on removing system versioning, see “Dropping System Versioning and System-Time” on page 34.
Example : ALTER TABLE to Convert a Nontemporal Table to an ANSI System-Time Table
The following SQL creates a regular nontemporal table, and inserts some rows:
CREATE MULTISET TABLE employee_systime (
eid INTEGER NOT NULL,
ename VARCHAR(10) NOT NULL,
deptno INTEGER NOT NULL,
sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL,
sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL
) PRIMARY INDEX(eid);
INSERT INTO employee_systime VALUES
(1001,'Sania',111,TIMESTAMP'2002-01-01 00:00:00.000000-08:00',
TIMESTAMP'9999-12-31 23:59:59.999999+00:00');
INSERT INTO employee_systime VALUES
(1002,'Ash',333,TIMESTAMP'2003-07-01 12:11:00.000000-08:00',
TIMESTAMP'9999-12-31 23:59:59.999999+00:00');
INSERT INTO employee_systime VALUES
(1003,'SRK',111,TIMESTAMP'2004-02-10 00:00:00.000000-08:00',
TIMESTAMP'2006-03-01 00:00:00.000000-08:00');
INSERT INTO employee_systime VALUES
(1004,'Fred',222, TIMESTAMP'2002-07-01 12:00:00.350000-08:00',
TIMESTAMP'2005-05-01 12:00:00.350000-08:00');
INSERT INTO employee_systime VALUES
(1005,'Alice',222,TIMESTAMP'2004-12-01 00:12:23.120000-08:00',
TIMESTAMP'2005-05-01 12:00:00.450000-08:00');
INSERT INTO employee_systime VALUES
(1004,'Fred',555, TIMESTAMP'2005-05-01 12:00:00.350000-08:00',
TIMESTAMP'9999-12-31 23:59:59.999999+00:00');
INSERT INTO employee_systime VALUES
(1005,'Alice',555,TIMESTAMP'2005-05-01 12:00:00.450000-08:00',
TIMESTAMP'9999-12-31 23:59:59.999999+00:00');
An unqualified SELECT on the table returns all rows, regardless of whether the row is open or closed in system time, because this is not yet a temporal table:
SELECT * FROM employee_systime;
eid ename deptno sys_start sys_end
---- ------ ------ -------------------------------- --------------------------------
1002 Ash 333 2003-07-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1005 Alice 222 2004-12-01 00:12:23.120000-08:00 2005-05-01 12:00:00.450000-08:00
1004 Fred 222 2002-07-01 12:00:00.350000-08:00 2005-05-01 12:00:00.350000-08:00
1005 Alice 555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred 555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00
1001 Sania 111 2002-01-01 00:00:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1003 SRK 111 2004-02-10 00:00:00.000000-08:00 2006-03-01 00:00:00.000000-08:00
Two ALTER TABLE statements can change the table into a system-time temporal table:
ALTER TABLE employee_systime
ADD PERIOD FOR SYSTEM_TIME(sys_start,sys_end)
ADD sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL
GENERATED ALWAYS AS ROW START
ADD sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL
GENERATED ALWAYS AS ROW END;
ALTER TABLE employee_systime
ADD SYSTEM VERSIONING;
Now an unqualified SELECT will show only the rows that are open in system time:
SELECT * FROM employee_systime;
eid ename deptno sys_start sys_end
---- ------ ------ -------------------------------- --------------------------------
1002 Ash 333 2003-07-01 12:11:00.000000+00:00 9999-12-31 23:59:59.999999+00:00
1001 Sania 111 2002-01-01 00:00:00.000000+00:00 9999-12-31 23:59:59.999999+00:00
1001 Fred 222 2002-07-01 12:00:00.350000+00:00 9999-12-31 23:59:59.999999+00:00
1003 Alice 222 2004-12-01 00:12:23.120000+00:00 9999-12-31 23:59:59.999999+00:00
Special temporal qualifiers allow you to display closed rows from system-time tables. For more information see “Querying ANSI System-Time Tables” on page 37.
Dropping System Versioning and System-Time
System-versioned system-time tables are typically used for regulatory and compliance purposes, and for keeping a table-resident history of database operations on the table data. Consequently, most types of ALTER TABLE changes to these tables are not allowed. However, ALTER TABLE can be used to remove system versioning. After system versioning has been removed from a temporal table, the table becomes a regular nontemporal table, and all normal ALTER TABLE operations are permitted.
Use the following ALTER TABLE syntax to remove system versioning from a system-time table:
ALTER TABLE your_system_time_table DROP SYSTEM VERSIONING;
Where your_system_time_table is the name of a system-versioned system-time table.
Note: Dropping the SYSTEM VERSIONING from a system-time table deletes all closed rows from the table, and makes the table a nontemporal table.
To drop the system-time columns, including the derived period column and its component beginning and ending bound TIMESTAMP columns, use the following ALTER TABLE syntax:
ALTER TABLE your_system_time_table DROP PERIOD FOR SYSTEM_TIME;
Where, again, your_system_time_table is the name of a system-versioned system-time table.
Dropping the system-time dervied period column will automatically drop the two component columns.
Note: You must drop the SYSTEM VERSIONING from a system-time table before you can drop the SYSTEM_TIME derived period column and component columns.