Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

182 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

5.2.1 Temporal data management with system time

System time refers to the time (a time stamp) at which point a data record is

changed in InfoSphere Warehouse 10 (that is, in a DB2 table). This change can

be one of an insert, update, or delete, and typically it is associated with some

business transaction of interest. The following are examples of events for which

you might want to track system time:

򐂰 A sales transaction is inserted into the database (table row insert)

򐂰 A medical insurance plan coverage amount for emergency care is increased

(table row update)

򐂰 A cable services customer drops the data plan from their package including

cable TV and voice services (database delete)

For the purposes of system audit, recovery or snapshot point-in-time queries, it

might be useful to be able to track precisely when these changes occurred and to

maintain a history of the data records in the database prior to the changes.

This is accomplished using the system time features of the temporal data

management in InfoSphere Warehouse 10.

For illustration purposes in the following discussion, consider the information

shown in Figure 5-1. This table captures customer profile information.

3

Figure 5-1 CUSTOMER_PROFILE table

Notice that the table contains several demographic attributes such as marital

status and gender for each customer. Some sample values are shown in the

table. Over the course of time, several of these attribute values might change for

a customer, and you might find it useful to track those changes using system

time temporal tables in InfoSphere Warehouse 10.

3

This table is derived from the INDIVIDUAL_CST_PROFILE table in the Customer Insight

Warehouse Pack solution.

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

InfoSphere Warehouse 10 makes several additions to a standard table to

manage it with system-period temporal awareness.

New time stamp columns in the base table

Three new time stamp columns are included in the base table (or query table) to

track period of validity for each row in the table. The new columns are listed here:

SYSTEM_TIME start This captures the time stamp at which time the new row

values are valid; that is, the moment at which the changes

were made in DB2.

SYSTEM_TIME end This is the time stamp indicating the moment when the

row values are no longer valid.

TRANS_START This tracks the time when the transaction first executed a

statement that changed the row’s data. This allows for

application logic to determine allows across multiple

tables that have been updated by the same transaction.

Each of these columns is type TIMESTAMP(12), and all columns are to be

created as GENERATED ALWAYS so that DB2 will automatically generate the

values on INSERT, UPDATE, and DELETE operations.

Furthermore, these columns can be defined as IMPLICITLY HIDDEN so that

they do not appear in SELECT * statements, but only if explicitly included in the

select list. Figure 5-2 shows our CUSTOMER_PROFILE table modified with the

new columns for system-period temporal data management. (The

TRANS_START column omitted to simplify the example and discussion.)

Figure 5-2 CUSTOM_PROFILE table enabled for system-period temporal data

Notice that all the rows in the table have a SYS_END column value of

“9999-12-30”, or December 30, 9999. This effectively means that the current row

value is valid indefinitely. We will see how these new columns are used as the

discussion continues.