Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

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.

Chapter 5. Temporal data management and analytics in an operational warehouse 193

Figure 5-9 Example CUSTOMER_PROFILE contents before INSERT

Assume that we insert the records as shown in Example 5-6.

Example 5-6 Inserting records

INSERT INTO CUSTOMER_PROFILE(CUSTOMER_ID, GENDER, MARITAL_STATUS) VALUES(20,

‘Male’, ‘Married’);

INSERT INTO CUSTOMER_PROFILE(CUSTOMER_ID, GENDER, MARITAL_STATUS) VALUES(21,

‘Male’, ‘Single’);

The new rows are inserted as usual into the CUSTOMER_PROFILE table, and

DB2 automatically generates the values for SYS_START, SYS_END and

TRANS_START. Because there are no existing rows corresponding to the new

rows that are updated or deleted, there is nothing added to the HISTORY table

for these inserts.

Figure 5-10 shows the updated CUSTOMER_PROFILE table after the insert

statements.

Figure 5-10 CUSTOMER_PROFILE table after INSERTs

194 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

Figure 5-11 shows the empty HISTORY table.

Figure 5-11 CUSTOMER_PROFILE_HISTORY after INSERTs

The SYS_START values for the new CUSTOMER_ID records 20 and 21 reflect

the date (and time, though not shown) when the new records were inserted into

the base table. Like all records in the base table, the SYS_END date and time for

those records is effective indefinitely though precisely until December 30, 9999.

This indicates the rows have “current” or “unexpired” data.

Updating data in the system-period temporal table

When we update existing records in the system-period temporal table, the history

table begins to play a role in managing our information. For example, we update

the marital status to “Married’ for customer number 1. Also, we record that

customer 10 was recently divorced so their status changed to “Divorced”.

Example 5-7 shows the SQL for these changes.

Example 5-7 SQL statements to update the CUSTOMER_PROFILE table

UPDATE CUSTOMER_PROFILE SET MARITAL_STATUS=’Married’ WHERE CUSTOMER_ID=1;

UPDATE CUSTOMER_PROFILE SET MARITAL_STATUS=’Divorced’ WHERE CUSTOMER_ID=10;

We can see the impact of the UPDATE SQL on the CUSTOMER_PROFILE table

in Figure 5-12.

Figure 5-12 CUSTOMER_PROFILE table after UPDATEs