226 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition
used like any other views in DB2, but they have two different usage scenarios
with distinct restrictions on how each can be used.
Figure 5-55 Two types of view on temporal tables
Shown in Figure 5-55, the two types of view usages are characterized as
described here; in both cases, the view can defined on the base temporal table of
either system-period or application-period types:
Restrict on temporal period
Define the view on either a certain point of time or a time period using the
FOR SYSTEM_TIME or FOR BUSINESS_TIME clauses to restrict on time.
Any other WHERE clause or non-temporal column restriction can be included
in the view definition.
Any query against the view of this style cannot include any additional FOR
BUSINESS_TIME or FOR SYSTEM_TIME temporal restrictions. The reason
for this restriction is that the time constraint on time in the view can conflict
with the additional time constraint in the query, which can lead to ambiguity.
No temporal restriction
Define the view in terms of the base temporal table with no time period
restriction on BUSINESS_TIME or SYSTEM_TIME. Data for all time periods
included in the base temporal table are now available to queries against the
Queries against the view can include FOR SYSTEM_TIME or FOR
BUSINESS_TIME clauses to restrict on time, and those restrictions are
applied to all temporal tables included in the definition of the view.
5.2.7 Register settings for temporal tables
In this final section describing how the new temporal table functionality in
InfoSphere Warehouse 10.1 and DB2 10.1 works, we take a look at new register
on System or
all System or
Chapter 5. Temporal data management and analytics in an operational warehouse 227
settings that facilitate running existing applications and simplify new application
Using these new register settings can allow existing queries to gain maximum
benefit from temporal features without having to be modified with the appropriate
FOR SYSTEM_TIME or FOR BUSINESS_TIME clauses. The “CURRENT” time
can be set and applied to all queries against system-period or application-period
tables that do not otherwise include a FOR SYSTEM_TIME or FOR
The statement in Example 5-34 sets the SYSTEM_TIME register to one week
prior to the current time stamp.
Example 5-34 Set the current system time to offset of current time
SET CURRENT TEMPORAL SYSTEM_TIME = CURRENT TIMESTAMP - 1 WEEK;
After this statement, all queries against system-period temporal tables that do
not have explicit FOR SYSTEM_TIME clauses have this clause applied implicitly:
FOR SYSTEM_TIME AS OF CURRENT TIMESTAMP - 1 WEEK
The statement in Example 5-35 sets the BUSINESS_TIME register to the literal
date value for March 1, 2012.
Example 5-35 Set the current business time to a date literal
SET CURRENT TEMPORAL BUSINESS_TIME = ‘2012-03-01’;
After this statement, all queries against application-period temporal tables that do
not have explicit FOR BUSINESS_TIME clauses have this clause applied
FOR BUSINESS_TIME AS OF ‘2012-03-01’
Avoid having two time constraints applied simultaneously: Similar to the
restriction on views, it is critical to avoid having two time constraints applied to
the same query simultaneously.
Set the special SYSTEM_TIME register and use the FOR SYSTEM_TIME
AS OF clause in a SQL query.
Set the special BUSINESS_TIME register and use the FOR
BUSINESS_TIME AS OF clause in a SQL query.
You must use one approach or the other, but never both at the same time.