Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

228 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

5.3 Implications of temporal tables on operational

warehousing

The new temporal data management features in InfoSphere Warehouse 10.1

(and DB2 10.1) provide a new and enhanced capability to address many of the

requirements and challenges of a modern operational data warehousing

solution. In this section we examine some best practices for temporal data

management and analysis that drive the greatest benefit from this new

technology.

5

Areas where temporal data management and analysis have implications on

operational data warehousing include:

򐂰 Complex workload and concurrency: impacts of read/write environments with

high levels of user concurrency on data integrity of temporal tables

򐂰 Performance and storage efficiency: storage implications of system-period

temporal tables and corresponding history tables

򐂰 Warehouse archiving: implications and practices around data archiving and

system-period temporal history tables.

򐂰 Backup and recovery strategies: implications for range partitioning a temporal

table solution

򐂰 Additional recovery considerations: roll forward implications and practices for

system-period temporal and related history tables

5.3.1 Temporal tables, complex workloads and concurrency

Operational data warehousing solutions are characterized by high levels of user

concurrency and mixed read and write database workloads. The combination of

read and write and user concurrency can sometimes lead to issues of data

integrity in system-period temporal history tables. The DB2 engine in InfoSphere

Warehouse 10.1 ensures that any data integrity conflicts are detected. The DBA

has a choice on how to handle the resolution, either through system-time

adjustments or rollback.

First we examine how the high concurrency environment can create implications

for data integrity in history tables. Consider a simple example of a system-period

temporal table and its corresponding history table

6

. Imagine two concurrent

5

Much of this section’s content has been drawn from the IBM white paper by Matthias Nicola, “Best

practices: Temporal data management with DB2”, IBM Corp., April 2012, which is available at the

following website:

http://www.ibm.com/developerworks/data/bestpractices/temporal/index.html

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

transactions, A and B, that both insert and update the same temporal table; see

Table 5-1 .

Table 5-1 Concurrent write operations on system-period temporal table

For system-period temporal tables, the time stamp of the first write operation

within any transaction determines the system time that is assigned to

all rows

modified in the same transaction. From Table 5-1, we can see that transaction A

performs an INSERT and UPDATE on the same table, so all rows impacted by

those statements in both the base and history tables will have the system time

stamp T1.

When Transaction B commits the INSERT statement, that row has a time stamp

of T2. Starting with an empty system-period temporal table MYTABLE at the start

of this example, we see the contents of MYTABLE and MYTABLE_HISTORY at

time T3, as shown in Figure 5-56.

Figure 5-56 Base and history table contents as of time stamp T3

As of time T3, notice the following:

򐂰 The history table is empty, because we processed two INSERT statements

with new rows for the base system-period temporal table MYTABLE.

򐂰 There are two records INSERTED into the base table MYTABLE; one each

for Transactions A and B, respectively.

6

Source: Matthias Nicola, “Best practices: Temporal data management with DB2”, IBM Corp., April

2012.

Time Transaction A Transaction B

T1 INSERT INTO mytable(c1, c2)

VALUES(1, 15);

T2 INSERT INTO mytable(c1, c2)

VALUES(2, 30);

T3 COMMIT;

T4 UPDATE mytable SET c2 = 33

WHERE c1 = 2;

T5 COMMIT;

C1 C2

System_start System_end

Committed?

1 15

T1 9999-12-30 No

2 30

T2 9999-12-30 Yes

C1 C2

System_start System_end

MYTABLE_HISTORY

MYTABLE