时光倒流

开始使用时态数据,一种来得及时的 DB2 新特性

IBM® DB2® 10 中的一项新特性为您提供了管理多个数据版本并跟踪随长期变更的能力。了解如何管理时态数据,使用 SQL 语句维护数据库变更历史,跟踪有效的业务日期,以及分析变更历史。 本文来自于 IBM Data Management magazine 中文版

Sasirekha Rameshkumar, 架构顾问, Tata Consultancy Services

Sasirekha Rameshkumar 是一名架构顾问,在大型机、IT 架构和性能管理方面拥有超过 18 年的丰富经验。她经常在 Xephon、DB2 和 ISSA 期刊上发表文章。可以在 itknowledgeexchange.techtarget.com/enterprise-IT-tech-trends 上阅读她的博客。



2011 年 9 月 15 日

免费下载:IBM® DB2® Express-C 9.7.2 免费版 或者 DB2® 9.7 for Linux®, UNIX®, and Windows® 试用版
下载更多的 IBM 软件试用版,并加入 IBM 软件下载与技术交流群组,参与在线交流。

- 阅读本文的交互式数字版!

对于大部分人,短语 “时间旅行” 会使他们联想到科幻小说:H. G. Wells、Star Trek 和某种流行的 DeLorean 汽车。但是当涉及到业务数据,穿越时间旅行的能力非常重要。要不您如何计算零部件成本增长了多少,或者自动向有价值的客户提供限时折扣,或者计算追溯费用变更的影响?

以前,在 DB2 for z/OS® 中跟踪数据长期变更需要大量手动干预,但第 10 版提供了管理多个数据版本和跟踪数据长期变更的新能力。使用简单的声明性 SQL 语句,您可以维护数据库变更的历史,跟踪有效的业务日期,以及分析变更历史。

系统时间和业务时间

时态功能的基本结构虽然简单,但很强大。DB2 跟踪两种不同类型的时态数据:系统时间业务时间。系统时间跟踪何时对表的状态进行了更改,而业务时间跟踪业务条件的有效期,比如保单或贷款利率的期限。包含时态数据的表有两个日期-时间 列,它们表明每一行有效的期限(时间间隔)。当插入新行时,DB2 将系统时间记录为开始 时间戳。当一行被删除时,删除日期记录在结束 时间戳中。

对于系统时间表,DB2 还帮助将历史数据存储在一个与时态表相关联的历史表 中。当时态表中的一行被更新时,DB2 更新该行并自动将该行的以前版本转移到一个历史表中。


创建包含系统时间的时态表

要创建包含系统时间的时态表,可以在表定义中包含 PERIOD SYSTEM_TIME 子句 (sys_start,sys_end)。还要包含另外 3 列,例如 sys_startsys_endtrans_startid,作为表定义中的 TIMESTAMP(12)(参见清单 1)。

清单 1. 创建包含系统时间的时态表
CREATE TABLE policy_info
(policy_id CHAR(10) 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,
trans_startid TIMESTAMP(12) GENERATED ALWAYS AS 
TRANSACTION START ID,
PERIOD SYSTEM_TIME(sys_start,sys_end));

指定 GENERATED ALWAYS 会告诉 DB2 在 INSERTUPDATEDELETE 操作期间自动生成时态值。使用以下选项指定 3 列中每一列的类型:

  • AS ROW BEGIN—DB2 生成一个时间戳值,它对应于与最近的事务相关联的开始时间。
  • AS ROW END—只要插入一行或该行中的任何列被更新(也就是创建了该行的一个新版本),DB2 就会分配 TIMESTAMP 的最大值。此列捕获从系统周期时态表中删除该行时的时间戳。
  • AS TRANSACTION START ID—DB2 为每个事务分配一个惟一的时间戳值,或者分配 null 值。DB2 在执行第一条更改表数据的语句时生成一个开始时间列。系统周期时态表需要一个 transaction-start-ID 列。如果定义为 NOT NULL,该值会对应于与最近的事务相关联的开始时间(AS ROW BEGIN 列的值)。如果可为空,它将包含 NULL 值。

关联历史表

尽管 DB2 将自动存储行的历史版本,但您必须创建一个历史表来包含这些行,并将它与系统周期时态表相关联。历史表必须拥有与系统维护的时态表相同的列数和列顺序。历史表不得包含时间段,必须具有与时态表相同的编码模式和编码字符集标识符 (CCSID)(参见清单 2)。

清单 2. 创建历史表
CREATE TABLE hist_policy_info
(policy_id CHAR(10) NOT NULL,
coverage INT NOT NULL,
sys_start TIMESTAMP(12) NOT NULL,
sys_end TIMESTAMP(12) NOT NULL,
trans_startid TIMESTAMP(12));

您可以使用 ALTER TABLE 命令和 ADD VERSIONING 子句,并在 USE HISTORY TABLE 子句中指定历史表名称,为时态表启用版本控制(参见清单 3)。

清单 3. 使用 ALTER TABLE 启用版本控制
ALTER TABLE policy_info
ADD VERSIONING USE HISTORY TABLE
hist_policy_info;

无法使用 ALTER TABLE 语句上的其他子句来指定 ADD VERSIONING 子句。

要删除系统周期时态表与历史表之间的关系,并归档一个没有关联的历史表,具有恰当授权的人必须将行复制到归档表,然后从历史表删除旧行。您可以使用 ALTER TABLEDROP VERSIONING 子句实现相同用途,但 IBM 建议尽量少使用 DROP VERSIONING,因为它可能导致无效性,这应该尽可能避免。


系统周期时态表的数据版本控制

当将新行插入到系统周期时态表时,DB2 自动为其 SYSTEM_TIME 列生成合适的 TIMESTAMP 值和事务开始时间。在该本例中,sys_starttrans_startid 拥有当前的系统时间,并且 sys_end 值设置为 9999-12-30-00.00.00.000000000000(参见清单 4)。

清单 4. 使用 3 种受支持的周期规范
SELECT coverage FROM policy
  FOR SYSTEM_TIME AS OF TIMESTAMP('2010-12-01')
     WHERE id = 1111;
                
SELECT count(*)FROM policy
   FOR SYSTEM_TIME FROM TIMESTAMP('2011-11-30')
   TO TIMESTAMP('9999-12-31')
       WHERE id = 1111;
                
SELECT * FROM policy
    FOR SYSTEM_TIME BETWEEN TIMESTAMP('2011-11-30')
    AND TIMESTAMP('9999-12-31')
        WHERE id = 1111;

当一行更新时,DB2 在历史表中自动维护该行的旧版本,在两个表中更新系统时间。DB2 将历史表中的 sys_end 值和时态表中的 sys_start 值设置为与最新的事务相关联的开始时间。如果在相同事务范围内再次更新相同行,那么不会在历史表创建记录。因此,DB2 仅保留原始行。如果该行被删除,那么 DB2 会自动从当前表删除数据并在历史表中维护旧版本。DB2 将历史表中所删除行的结束时间设置为与 DELETE 语句的最新事务相关联的开始时间。


查询系统管理的时态表

没有任何周期规范的 SELECT 语句仅适用于系统周期时态表,为仅需要最新数据的应用程序提供了性能收益。只有在查询的 FROM 子句 包含周期规范时,DB2 才会自动且透明地访问历史数据。周期规范使用 FOR SYSTEM_TIME 表示将使用系统时间。在功能上,3 种受支持的周期规范如下所示(另请参见清单 4):

  • AS OF value查询截至某个时间点的数据。返回开始值 <= value 且结束值 > value 的行。
  • FROM value1 TO value2查询从 value1value2 的数据。返回开始值 < value2 且结束值 > value1 的行。如果 value1 大于或等于 value2,返回 0。
  • BETWEEN value1 AND value2查询开始时间和结束时间范围内的数据。返回开始值 <= value2 且结束值 > value1 的行。如果 value1 大于 value2,返回 0。

创建和使用包含业务时间的表

业务时间涉及到跟踪某些业务条件在过去、现在或未来的哪个时刻有效。与系统管理的时态表不同,没有独立的历史表。您在一个表中维护过去、现在和未来的有效期和它们的关联数据。

要创建包含业务时间的表,可以使用 PERIOD BUSINESS_TIME 子句(bus_startbus_end),包含开始 (bus_start) 和结束 (bus_end) 列。它们可以为日期或时间戳数据类型。一项隐含的 DB2 限制会强制要求 bus_start 值小于 bus_end 值,确保时态数据的完整性。要确保指定的键在特定时间段内是惟一的,可以指定主键约束 BUSINESS_TIME WITHOUT OVERLAPS 作为包含其他键的列表中的最后一项。例如,在策略表中指定 PRIMARY KEY(id, BUSINESS_TIME WITHOUT OVERLAPS),可以确保相同策略的同时有效的两个版本无法共存。

在将一行插入包含业务时间的表中时,用户必须显式指定开始和结束日期列。默认情况下,UPDATE 语句以传统方式处理包含业务时段的表。包含 FOR PORTION OF BUSINESS_TIME FROM value1 TO value2 的周期子句可用于将更新应用到特定的业务时间段。包含周期子句的 UPDATE 意味着,从 value1value2 的时间段内的所有行必须更新。

通过在 FOR PORTION OF BUSINESS_TIME 子句中指定时间范围,您可以删除与特定业务时段相关的数据。如果要删除的行包含没有完全包含在指定时间范围内的数据,DB2 会自动执行隐式插入,确保合适的信息得以保留。

没有任何周期规范的 SELECT 语句可以正常使用。涉及业务时间的时态查询也很容易编写,您仅需要在 FROM 子句中包含 FOR BUSINESS_TIME。类似于系统管理的时态表,支持 3 种类型的查询,包括 AS OF、FROM... TO...BETWEEN... AND...。DB2 依据时态表的开始和结束列中使用的数据类型,使用合适的 WHERE 子句谓词自动重写查询。

例如,与相同策略相关但在不同时间段具有不同覆盖范围的多行可插入到策略表中。

表 1. 策略表中与相同策略相关的多行
IDCoverageBus_startBus_end
11115000002010-01-012011-01-01
11117500002011-01-012012-01-01
11119000002012-01-019999-12-31

为相同策略插入开始和结束日期分别为 2011-06-01 和 2012-01-01 的行将会失败,因为 BUSINESS_TIME_WITHOUT_OVERLAPS 子句可确保时态惟一性得以维持(在以前,实现此目的涉及到大量编程工作)。

包含一个周期子句的 UPDATE 意味着,value1value2 的时间段内的所有行必须更新,可用于将更新应用到特定的业务时间段。例如,如果您尝试将 2010-06-01 到 2011-09-01 时间段内的覆盖量更改为 600000,结果表将如下所示:

表 2. 使用包含周期子句的 UPDATE 之后策略表中的结果
IDCoverageBus_startBus_end
11115000002010-01-012010-06-01
11116000002010-06-012011-01-01
11116000002011-01-012011-09-01
11117500002011-09-012012-01-01
11119000002012-01-019999-12-31

时态查询变得更加简单

DB2 会自动和透明地处理这些版本控制和时态查询,对现有应用程序的影响极小。IBM 的方法有望成为数据库中的标准,SQL 标准的下一个版本将包含时态功能。现在您可以使用此技术处理数据版本管理和业务事件的有效跟踪,而无需使用应用程序级的触发器和存储过程。使用时态表可帮您节省一定的时间。

参考文章
最大限度地提高数据分析性能
合作伙伴资源
Applied Analytix, Inc DBIIBM
IBM Information On DemandInternational DB2 Users Group (IDUG)Melissa Data
NetezzaNiteo PartnersQueBIT
Quest Software

参考资料

学习

获得产品和技术

讨论

  • 通过访问 alphaWorks 获得更多 IBM 的前瞻性技术和资源。
  • 加入 developerWorks 中文社区,developerWorks 社区是一个面向全球 IT 专业人员,可以提供博客、书签、wiki、群组、联系、共享和协作等社区功能的专业社交网络社区。

条评论

developerWorks: 登录

标有星(*)号的字段是必填字段。


需要一个 IBM ID?
忘记 IBM ID?


忘记密码?
更改您的密码

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件

 


在您首次登录 developerWorks 时,会为您创建一份个人概要。您的个人概要中的信息(您的姓名、国家/地区,以及公司名称)是公开显示的,而且会随着您发布的任何内容一起显示,除非您选择隐藏您的公司名称。您可以随时更新您的 IBM 帐户。

所有提交的信息确保安全。

选择您的昵称



当您初次登录到 developerWorks 时,将会为您创建一份概要信息,您需要指定一个昵称。您的昵称将和您在 developerWorks 发布的内容显示在一起。

昵称长度在 3 至 31 个字符之间。 您的昵称在 developerWorks 社区中必须是唯一的,并且出于隐私保护的原因,不能是您的电子邮件地址。

标有星(*)号的字段是必填字段。

(昵称长度在 3 至 31 个字符之间)

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件.

 


所有提交的信息确保安全。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=757817
ArticleTitle=时光倒流
publish-date=09152011