Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

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

view.

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

View restricted

on System or

Business Time

View spanning

all System or

Business Time

Base

Temporal Table

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

settings that facilitate running existing applications and simplify new application

programming.

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

BUSINESS_TIME clause.

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

implicitly:

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.

You cannot:

򐂰 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.