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

Get Unleashing DB2 10 for Linux, UNIX, and Windows now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.