Unleashing DB2 10 for Linux, UNIX, and Windows

10 Unleashing DB2 10 for Linux, UNIX, and Windows

2.3.1 Time Travel Query description and usage

Time Travel Query is designed to support system time, business time, or a

combination of both system time and business time (referred to as

bitemporal).

Before we show actual examples of Time Travel Query in use, we describe its

basic components and how they work.

To begin, we first define system time and business time:

򐂰

System time is maintained by DB2 and is provided by the system clock on the

database server at the time that the database transaction occurs. DB2

captures the valid start time, valid end time, and the transaction time as each

update or delete transaction occurs in the database for those tables defined

as using system time.

򐂰

Business time is either a timestamp or a date and provides the valid start and

end times for a record in terms of the business and is unrelated to system

time (when the transaction actually occurs within the database). Examples of

business time are the start and end dates that an automobile insurance policy

is valid, the check-in and check-out dates for a hotel reservation, and the valid

booking dates for an event (such as a concert).

DB2 10 provides the following types of temporal tables:

򐂰 System-period temporal tables (STT)

Use system time to keep track of rows that are updated or deleted in a

separate history table. STT allows all modifications to a base table to be

tracked when the table exists as an STT and has the ability to query data

transparently at any point in time between the first usage of the STT and the

present time.

򐂰 Application-period temporal tables (ATT)

Use timestamps or dates to track the business time period for which each row

within the table is valid. Unlike an STT, an ATT requires that application is

aware of the ATT. Because business time is being tracked (which can be past,

present, or future), DB2 cannot provide the business-time value. The

business-time values (start and end time) must be provided by the

application. If business time is specified as part of the update or delete

statements, DB2 will automatically modify the data rows as required to

maintain business time. ATTs allow rows within the table to be based on valid

time periods for the business (independent of the system time) and can be

used for past, present, or (unique to ATT) future time periods.

򐂰 Bitemporal tables

Combine the characteristics of both STTs and ATTs. With the combination,

data can support business time, while tracking all modifications to the table