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
230 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition
???? The record inserted at time stamp T1 (Transaction A) is uncommitted
because the COMMIT does not occur until T5.
???? The record inserted at time stamp T2 (Transaction B) is committed as of time
stamp T3.
At time T4, Transaction A performs an UPDATE on the row inserted by
Transaction B, which generates a corresponding history row. Consider the
content of the history table, which is shown in Figure 5-57 with the modified base
table.
???? The historical values of the data columns C1 an C2 are copied from the
updated row in the base table.
???? The SYSTEM_START value is the time stamp of the original row in the base
table, which is T2.
???? The SYSTEM_END value is the time stamp of the UPDATE transaction,
which is T1 in our case.
Figure 5-57 Base and history table contents as of time stamp T4
As a result of the concurrent operations and COMMIT sequence, the
SYSTEM_START time stamp (T2) in the history table is
after the SYSTEM_END
timestamp (T1). This violates the data integrity constraint on system-period
temporal tables that system start time stamps must be less than system end time
stamps in the same history row.
This violation can be resolved in one of two ways:
???? Rollback (SQL20528N)
The default behavior is to automatically roll back Transaction A and return
error code 20528.
???? System time adjustment (SQL5191W)
The DBA can choose for DB2 to automatically increase the conflicting
timestamp (T1) to T2 +
some delta that reflects the next possible time stamp
after T2. This automatically eliminates the conflict and allows the transaction
to continue. To use this option, the database configuration parameter
SYSTEM_PERIOD_ADJ must be set to YES. Whenever the conflict and
corresponding adjustment occur, warning SQL5191W is issued.
MYTABLE
MYTABLE_HISTORY
C1 C2
System_start System_end
Committed?
1
15
T1 9999-12-30 No
2
33
T1 9999-12-30 No
C1 C2
System_start System_end Committed?
2 30
T2 T1 No
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.