DB2 理解系统时间和业务时间

DB2 中的时态表
英文原文：http://ibmdatamag.com/2012/04/temporal-tables-in-db2/
理解系统时间和业务时间
作者：Robert Matchett |
发布日期：2012 年 4 月 30 日
DB2 不再仅仅面向 DBA。长久以来，DB2 因杰出的核心 DBMS 功能而闻名于世，而如
今，DB2 使开发人员也能够利用它的强大特性。这方面的一个示例就是直接在模式内实现
基于时间的信息。这是通过 DB2 10 for Linux, UNIX, and Windows 中新增的时态表来实现
的。
利用时态表，您能够轻松地跟踪和分析业务中的变化，准确对比两个时间点的数据。该功能
允许您有效执行和跟踪数据更正，支持您在过去执行数据更改，也就是说，使之作为过去的
某个特定时间点而“生效”，同时记录执行更改的时间。时态表提供了显示过去任意时间点
数据的能力，也能显示相同事务中的哪些信息发生过更改，更改是在何时执行的，因此能够
促进审计与合规性。
时态表是什么？
时态表允许您将基于时间的状态信息与 DB2 管理的数据关联，并使之与应用程序逻辑保持
无关。DB2 中的时态表功能允许跟踪数据更改（版本控制），支持自定义业务数据存储和
操作（以及两者的结合）。
时态表分为两种类型：系统和应用程序。DBA 可以使用双时态表，同时利用两种类型的功
能。
时态表能简化过去、现在和未来任意给定时间点的数据报告，因而有助于加强业务洞察。这
种功能可提高开发人员的生产力（DB2 内的处理时间管理意味着更少的编码工作，更简单
的编码意味着维护成本的降低），因此可降低成本。时态表也能减少合规性工作，支持更好
地跟踪数据更改。时态表提供了基于标准的技术，能在整个企业的范围内实现一致性和出色
的数据质量。时态表深度集成所有数据库特性，包括分区、压缩和视图。
系统时态表
系统表支持以操作时间（即操作系统时间）为依据的基于时间的功能。系统时态表利用历史
表，存储数据的历史版本。
您该如何实现系统时态表？首先，使用 SYSTEM_TIME 属性创建或更改一个基础表，包含
以下三个具体生成列：
 行起始列：行数据成为当前数据的时间

行结束列：行数据不再属于当前数据的时间

事务起始 ID 列：影响该行的事务的起始执行时间。
接下来，创建一个完全相同的表，作为历史表。该表必须使用与基础表完全相同的布局。但
您可以按照自己的需求配置历史表，例如分区、压缩或存储位置。
第三步是使用 Alter 语句将历史表与基础表相关联，并为基础表添加版本控制，即：ALTER
TABLE policy_info ADD VERSIONING USE HISTORY TABLE hist_policy_info。
例如：
CREATE TABLE policy_info
(
policy_id CHAR(4) NOT NULL,
coverage INT NOT NULL,
sys_start TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
sys_end TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
ts_id TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS
TRANSACTION START ID,
PERIOD SYSTEM_TIME (sys_start, sys_end)
) IN policy_space;
CREATE TABLE hist_policy_info
(
policy_id CHAR(4) NOT NULL,
coverage INT NOT NULL,
sys_start TIMESTAMP(12) NOT NULL,
sys_end TIMESTAMP(12) NOT NULL,
ts_id TIMESTAMP(12) NOT NULL
) IN hist_space;
ALTER TABLE policy_info ADD VERSIONING USE HISTORY TABLE
hist_policy_info;
系统表函数
发生变化时，DB2 会自动将基础表中的行迁移到历史表，并根据需要更新三个时间列。基
础表和历史表的所有管理都是自动、透明的，无需任何编码。编写查询时，仅需引用基础表，
DB2 将根据日期范围，自动访问历史表。
系统时态表提供了多种收益：

对于仅访问当前数据的应用程序，DB2 仅访问基础表，因此影响将降低。

当前数据的联机加载、重组、索引创建和其他维护操作不会因历史表而减速。

可以分别为基础表和历史表选择物理存储选项（例如，位置、压缩、分区和集群等）。

基础表和历史表可以具有不同的索引和约束。

基础表数据和历史表数据的恢复可以分别执行。
系统时态表的 DML
系统时态表定义完成后，通过对时态表执行 DML（插入、更新、删除和选择）来填充和管
理数据将非常容易，就像使用其他表时一样。下面将具体介绍 DML 操作。
插入：DB2 10 将在时间戳字段中插入操作系统时间，无需另行提供。
更新：除了更新系统时态表各行中特定列的值之外，UPDATE 语句还会将现有行的副本插
入相关的历史表。历史行将作为更新这些行的同一个事务的一部分生成。如果某个事务对相
同的行执行多次更新，则仅生成一个历史行，该行反映了此事务做出任何更改之前的记录状
态。
删除：除了删除系统时态表的特定行之外，DELETE FROM 语句还会将现有行的副本移动
到相关历史表中，之后再从系统时态表中删除此行。
选择：在查询系统时态表时，您可以在 FROM 子句中包含 FOR SYSTEM_TIME。利用 FOR
SYSTEM_TIME 规范，您可以查询数据的当前状态和历史状态。时间段定义如下：



AS OF value1：包含时间段的起始值小于或等于 value1 且时间段的结束值大于 value1
的所有行。这使您能够查询截至特定时间点的数据。
FROM value1 TO value2：包含时间段的起始值大于或等于 value1 且时间段的结束
值小于 value2 的所有行。这意味着该时间段内包含起始时间，但不包含结束时间。
BETWEEN value1 AND value2：包含对应于与 value1 和 value2 之间的任意时间点
重叠的任意时间段的所有行。如果该时间段的起始值小于或等于 value2 且时间段的结束
值大于 value1，则返回对应的一行。
时间旅行管理：设置特殊注册表
如果您希望对一个系统时态表运行某个应用程序，以便查询业务在多个不同日期的状态，则
可在特殊注册表中设置日期。如果您需要查询截至今天、截至上一季度末、截至去年相同日
期的数据，那么您将无法更改应用程序，无法为各 SQL 语句添加 AS OF 规范。如果您在
使用打包的应用程序，那么就很有可能遇到这样的局限性。为解决此类问题，可以使用
CURRENT TEMPORAL SYSTEM_TIME 特殊注册表，在会话级别上设置日期或时间戳。
设置 CURRENT TEMPORAL SYSTEM_TIME 特殊注册表不会影响常规表。只有针对启用了
版本控制的时态表（系统时态表和双时态表）的查询才能使用特殊注册表中设置的时间。这
对 DDL 语句也不会产生任何影响。
应用程序时态表
应用程序时态表允许存储业务事件信息（应用程序的逻辑时间视图）。
例如：

定价策略的有效日期从 4 月 1 日起，到 6 月 30 日结束

利率自有效起始日期开始生效，至有效结束日期结束。

未来 — 在特定日期，利率将提高为新利率。
在这些表中，DB2 按照需求透明地添加、拆分或删除行。应用程序表可用于表示未来、过
去和现在的数据。可以自动进行约束，禁止出现重叠的有效期。
您该如何实现系统时态表？首先，使用 BUSINESS_ TIME 属性创建或更改基础表，包含
由应用程序存储的一对时间戳或日期列：
 起始列：表示该行数据开始有效的时间
 结束列：表示该行数据不再有效的时间
时间敏感的列由应用程序控制。不同于系统时态表，并不需要使用独立的历史表。
接下来，创建一个使用 BUSINESS_TIME 时间段的表。例如：
CREATE TABLE policy_info
(
policy_id
CHAR(4) NOT NULL,
coverage
INT NOT NULL,
bus_start
DATE NOT NULL,
bus_end
DATE NOT NULL,
PERIOD BUSINESS_TIME (bus_start, bus_end)
);
可选：创建一个惟一索引，防止相同的 policy_id 出现重叠的 BUSINESS_TIME。例如：
CREATE UNIQUE INDEX ix_policy ON policy_info (policy_id,
BUSINESS_TIME WITHOUT OVERLAPS);
应用程序时态表的 DML
应用程序时态表定义完成后，通过对此类时态表执行 DML（插入、更新、删除和选择）来
填充和管理数据会非常容易，就像使用其他表时一样。下面将具体介绍 DML 操作。
插入：将数据插入应用程序时态表时，惟一需要特殊考虑的事项就是需要包含行起始和行结
束列，从相关业务应用程序的视角捕获行的有效时间。这种有效的时间段就称为
BUSINESS_TIME 时间段。数据库管理器将自动生成隐式检查约束，确保
BUSINESS_TIME 时间段的起始列不会小于其结束列。如果使用 BUSINESS_TIME
WITHOUT OVERLAPS 为表创建了惟一约束或索引，您就必须确保不存在重叠的
BUSINESS_TIME 时间段。
更新：除了常规的 UPDATE 语句之外，应用程序时态表还支持时间范围更新，使 UPDATE
语句包含 FOR PORTION OF BUSINESS_TIME 子句。如果一个行的时间段起始列和/或
时间段结束列位于 FOR PORTION OF BUSINESS_TIME 子句指定的范围内，则该行即为
更新的备选行。在更新应用程序时态表时，可以利用 FOR PORTION OF BUSINESS_TIME
子句来防止出现时间段重叠问题。
删除：除了常规的 DELETE 语句之外，应用程序时态表还支持时间范围删除，使 DELETE
语句包含 FOR PORTION OF BUSINESS_TIME 子句。如果一个行的时间段起始列和/或
时间段结束列位于 FOR PORTION OF BUSINESS_TIME 子句指定的范围内，则该行即为
删除的备选行。从一个应用程序时态表中删除数据会将这些行从表中移除，并有可能导致为
插入应用程序时态表本身插入新行。
选择：在查询应用程序时态表时，您可以在 FROM 子句中包含 FOR BUSINESS_TIME。
利用 FOR BUSINESS_TIME 规范，您就可以查询数据的当前状态和历史状态。时间段定
义如下：
 AS OF value1：包含时间段的起始值小于或等于 value1 且时间段的结束值大于 value1
的所有行。
 FROM value1 TO value2：包含时间段的起始值大于或等于 value1 且时间段的结束
值小于 value2 的所有行。这意味着该时间段内包含起始时间，但不包含结束时间。
 BETWEEN value1 AND value2：
包含对应于与 value1 和 value2 之间的任意时间点
重叠的任意时间段的所有行。如果该时间段的起始值小于或等于 value2 且时间段的结束
值大于 value1，则返回对应的一行。
使用特殊注册表的“时间旅行”管理
利用 DB2 Time Travel Query 特性，您可以针对给定会话将时钟设置为当前或未来的特定
时间点，而无需更改应用程序。如果您希望对一个应用程序时态表运行某个应用程序，以便
查询业务在多个不同日期的状态，那么可以在特殊注册表中设置日期。如果您需要查询截至
今天、截至上一季度末的数据，或者模拟截至未来某个日期的未来事件，您将无法更改应用
程序，无法为各 SQL 语句添加 AS OF 规范。如果您正在使用打包的应用程序，那么就很
有可能遇到这种情况。此时，您可以使用 CURRENT TEMPORAL BUSINESS_TIME 特殊
注册表，在会话级别上设置日期。
设置 CURRENT TEMPORAL BUSINESS_TIME 特殊注册表不会影响常规表。仅有针对启
用了 BUSINESS_TIME 时间段的时态表（应用程序时态表和双时态表）的查询将使用特殊
注册表中设置的时间。
图 1. 系统时间与应用程序时间的时间段对比
系统时间
业务时间
捕获 DB2 内的数据发生更改的时间
捕获业务数据发生更改的时间
DB2 生成的行更新或删除历史记录
由应用程序驱动的业务数据时间维度更改
历史记录基于 DB2 系统时间戳
日期或时间戳由应用程序提供
DB2 的物理时间视图
应用程序的逻辑时间视图
过去到现在的时间
过去、现在和未来的时间
系统有效性（“事务时间”）
业务有效性（“有效时间”）
支持这样的查询：
“6 月 30 日的数据库中存储的是怎样的价
支持这样的查询：
“6 月 30 日有效的价格策略是哪种？”
格？”
双时态表
双时态表就是结合了系统时态表的历史跟踪以及应用程序时态表的特定于时间的数据存储
功能的表。您可以利用双时态表，保留基于用户的时间段信息，以及基于系统的历史信息。
适用于系统时态表和应用程序时态表的所有约束同样适用于双时态表。
考虑这样一个示例：某组织希望根据 2011 年 5 月 10 日的数据库记录，了解哪些产品是
在 2011 年 6 月 20 日上市的。双时态表结合了应用程序 (ATT) 和系统 (STT) 表的功能。
每一行都有如下一对值：


DB2 设置的时间戳（SYSTEM_TIME 时间段）
应用程序设置的业务时间戳或日期列（BUSINESS_TIME 时间段）
例如：
CREATE TABLE policy_info
(
policy_id
CHAR(4) NOT NULL,
coverage
INT NOT NULL,
bus_start
DATE NOT NULL,
bus_end
DATE NOT NULL,
sys_start TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
sys_end TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
ts_id TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START
ID,
PERIOD BUSINESS_TIME (bus_start, bus_end),
PERIOD SYSTEM_TIME (sys_start, sys_end)
) in policy_space;
CREATE TABLE hist_policy_info
(
policy_id
CHAR(4) NOT NULL,
coverage
INT NOT NULL,
bus_start
DATE NOT NULL,
bus_end
DATE NOT NULL,
sys_start TIMESTAMP(12) NOT NULL,
sys_end TIMESTAMP(12) NOT NULL,
ts_id TIMESTAMP(12)
) in hist_space;
ALTER TABLE policy_info ADD VERSIONING USE HISTORY TABLE
hist_policy_info;
您可以选择创建包含 BUSINESS_TIME 时间段的惟一索引。例如：
CREATE UNIQUE INDEX ix_policy
ON policy_info (policy_id, BUSINESS_TIME WITHOUT OVERLAPS);
双时态表的 DML
双时态表定义完成后，通过对时态表执行 DML（插入、更新、删除和选择）来填充和管理
数据会非常容易，就像使用其他表时一样。下面将具体介绍 DML 操作。




插入：将数据插入双时态表与将数据插入应用程序时态表的操作极为相似。
更新：更新双时态表中的数据将导致为相关历史表添加行，也可能会向双时态表本身添
加行。
删除：删除双时态表中的数据会导致从表中删除行、为相关历史表添加行，也可能会为
双时态表本身插入新行。
选择：查询双向表可返回特定时间段的结果。这些结果中可包含当前值、之前的历史值
和未来值。
许多 IT 组织都有一些希望转为系统时态表和/或应用程序时态表的现有表。这些表可转为
时态表，从而获得以下收益：

降低运营成本，因为逻辑将在 DB2 引擎内执行

减少应用程序逻辑，缩短应用程序开发时间

减少手动编码的触发器和存储过程

简化查询

透明部署

更简单、更快捷地实现遵从性
时态表视图
可以在系统时态表（基础表和历史表）、应用程序时态表或双时态表中定义视图。视图同样
支持所有语法（例如 FOR PORTION OF、AS OF 和 FROM… TO 等）。可为时态表定义
两种类型的视图：FOR BUSINESS_TIME 或 FOR SYSTEM_TIME。
结束语
时态表提供了一种面向数据库的方法，支持管理基于时间的数据。时态表能确保所有应用程
序均对按时间存储的数据使用相同的方法。这就带来了常见的“时间旅行”模块，一经实现，
即可显著简化应用程序中面向时间的数据的管理。