192 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition
5.2.2 System-period temporal tables
Now that you have an understanding of how to create and enable tables for
system-period temporal data management, we shift our attention to how to
manipulate and query these tables. We start with describing what happens when
data is inserted into the base table.
Inserting data into a system-period temporal table
When data is inserted into a system-period temporal table, the new rows are
added to the table just like any other regular table with the exception of how the
new TIMESTAMP columns are assigned. The INSERT statement itself is 100%
identical to an INSERT into a regular table. This feature allows the introduction of
system-period temporal tables to have no impact on existing applications and
operations.
If we return to our example tables from the previous discussion, the
CUSTOMER_PROFILE table, we might have the following data already present
as shown in Figure 5-9 on page 193.
Dropping a column: There are cases where it is necessary to make the table
modifications previously shown, but because those modifications can cause a
potential loss of data, they are blocked by default. For those cases, when this
action is justified and the consequences are well understood, it is possible to
disable versioning and make the necessary modifications.
A useful example of dropping a column from a system-period temporal table is
given in Best Practices: Temporal Data Management with DB2 in the section
“How to drop a column from a system-period temporal table”. This paper is
available at the following website:
http://www.ibm.com/developerworks/data/bestpractices/temporal/index.html
Note: For all examples used in this section on system-period tables, the
SYS_START and SYS_END values are shown only the DATE part of the
TIMESTAMP(12) value. This is a shorthand convenience for the purposes of
simplifying the illustration only.
The true value of these columns is a full TIMESTAMP(12) value.