内容


时间问题:DB2 10 中的时态数据管理

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

简介

如果 “时间就是金钱”,那么 DB2 时态数据管理技术就是为同时节省时间和金钱而设计的。对管理多个数据版本和跟踪有效业务日期的内置支持可为数据库管理员和应用程序开发人员节省大量的时间和精力。

在以前的版本中,数据库专业人员必需创建触发器或复杂的应用程序逻辑来管理依赖于时间的条件。现在,DB2 通过引入表设计选项及查询语法和语义(基于 ANSI/ISO SQL:2011 标准),用它们来最大程度地减少或消除这些工作。

何时需要使用时态数据管理?

在深入介绍 DB2 时态数据支持的技术方面之前,让我们考虑以下这些应用程序场景:

  1. 一位内部审计人员要求一家金融机构报告在过去 5 年内对一家客户的记录所做的更改。
  2. 一场尚未进行最终判决的诉讼要求某家医院在安排新的治疗之前重新评估它掌握的患者医疗条件的知识。
  3. 一家客户质疑一家保险公司对涉及汽车事故的索赔的解决办法。该保险公司需要确定发生事故后的有效保险条款。
  4. 一家在线旅行公司希望检测行程中的不一致性。例如,如果某个人对罗马一家酒店预定了 8 天并且对纽约的一辆汽车预定了这 8 天中的 3 天,该公司则可能标记出该情形以供审核。
  5. 一家零售商需要确保在任何时间都仅为一款给定的产品提供了不多于一种的折扣。
  6. 一项客户查询找到了一张信用卡上一个涉及 3 个月基础利率的数据输入错误。银行需要追溯以前的数据来更正此错误(并在必要时计算新的余额)。

对于上述每种情形,时间都是至关重要的。我们稍后要介绍的是,DB2 中新的时态数据管理支持可帮助公司以最少的精力实现时间感知的应用程序和查询。但是,在探索新功能之前,我将介绍一些与时间相关的概念,包括系统时间和业务时间之间的区别。

基本概念

系统时间 涉及到跟踪更改某个表单状态的时间,比如修改一份保险保单或创建一项贷款的时间。业务时间 涉及到跟踪某些业务条件的有效期,比如一份保险保单或一种贷款利率的期限。(业务时间有时称为有效时间应用时间。)一些组织的确需要在一个表中同时跟踪两种类型的时态数据;这些表被视为具有双时态

这些概念如何应用于前面的应用程序场景?场景 1 和场景 2 需要系统时间的知识,因为理解一个或多个表的历史状态很重要。场景 3-6 需要业务时间的知识,因为理解和管理各种业务条件的有效期很重要。而且,如果银行希望追溯性地更正数据输入错误, 维护更正错误的时间记录,那么场景 6 可能还需要系统时间(也就是说它可能需要双时态支持)。

时间段 适用于业务时间和系统时间。可以想象,一个时间段表示一个时间间隔的起点和终点。使用 DB2,管理员可标识一个表中的两列来只是一个时间段的开始和结束时间。对 CREATE TABLEALTER TABLE 语句语法的简单扩展就能够完成此目的,使管理员能够对新的或现有的表采用时态数据支持。

DB2 使用一种包含-排除 方法来建模时间段。简单来讲,时间段的开始时间包含在时间段内,但它的结束时间为包含在内。所以,如果一份保险保单的结束时间记录为 2007 年 12 月 31 日午夜,那么该保单将在午夜之前有效,但在午夜那一刻无效。

IBM 已与 ANSI 和 ISO SQL 标准委员会合作,将这些扩展合并到最新的 SQL:2011 标准中。IBM 是第一家支持基于这种新 SQL 标准的时态数据管理的数据库供应商。而其他数据库供应商则对时态操作和时态表的定义使用了专用的语法。

时态数据管理的优势

DB2 中对管理时态数据的内置支持减少了应用程序逻辑,并确保在针对您的数据库运行的所有应用程序(包括购买的应用程序)之间一致地处理时间相关事件。通过简单的声明性 SQL 语句,管理员可告诉 DB2 自动维护数据库更改历史或跟踪有效的业务日期,并消除将这些逻辑硬编码到触发器、存储过程或内部应用程序中的需求,进而可以帮助公司更快地遵守新的合规性计划。而且,用来管理时态数据的一致方法减少了查询复杂性,并促进了对依赖于时间的事件的增强分析。

IBM 的一项内部研究对比了两种实现,一种是使用 DB2 中内置的时态数据管理支持实现一个业务时间功能子集,另一种是将相同逻辑硬编码到两个自有的 (homegrown) 实现中。一个实现使用了触发器和存储过程,而另一个使用了触发器和 Java™ 应用程序逻辑。所有测试都是使用 DB2 for Linux, UNIX, and Windows 完成的。DB2 的内置支持提供的成本节约非常显著。

相比两个自有实现,DB2 中的内置支持使编码需求的减少可达 90% 以上。与使用新 DB2 时态功能的简单的等效 SQL 语句相比,单单在 SQL 存储过程或 Java 技术中实现核心业务时间逻辑就分别需要 16 倍或 45 倍的代码量(参见 图 1)。而且,DB2 中的内置支持只需不到一个小时即可开发和测试这些 DB2 SQL 语句。相对而言,自有的方法需要 4 到 5 个星期进行编码和测试,而且二者都只提供了 DB2 中内置的时态数据管理支持的一部分。自有逻辑确实没有提供系统时间、双时态数据、视图和许多其他 DB2 功能。因此,通过自有实现提供真正等效的时态数据支持可能需要几个月的时间。这比简单地编写合适的 DB2 SQL 语句所需的时间要长得多,我们稍后将对此进行讨论。

图 1. 实现业务时间行为的开发成本
该条形图显示了自有解决方案、SQL 存储过程需要 16 倍的代码量,而自有的 Java 解决方案需要 45 倍的代码量
该条形图显示了自有解决方案、SQL 存储过程需要 16 倍的代码量,而自有的 Java 解决方案需要 45 倍的代码量

示例场景

为了帮助您理解 DB2 中的时态数据管理支持,我们使用了一个常见的应用程序场景和示例数据。该场景涉及到汽车保险保单,为简单起见,我们用一个表来表示它们。该表跟踪与这些保单关联的部分典型信息:保单标识符 (ID)、车辆识别码 (VIN)、估算的车辆每年行驶里程、在汽车由于索赔而需要修理时是否将提供一辆租用车,以及保单金额(包括事故财产损失、医疗开支等)。

表 1 演示了没有任何时态支持的 POLICY 表的基本结构。

表 1. 示例 POLICY 表(没有时态支持)
IDVINannual_mileagerental_carcoverage_amt
1111A111110000Y500000

让我们分析一下 DB2 时态支持如何帮助您管理这些保险保单。

使用系统时间管理数据版本

DB2 中对系统时间的支持使您能够自动跟踪和管理数据的多个版本。通过定义一个具有系统时间段的表,您可以告诉 DB2 自动捕获表状态的更改,并将 “旧” 行保存在一个历史表 中,这是一个具有与当前表相同的结构的独立的表。引用当前表的时态查询会导致 DB2 根据需要透明地访问此历史表,您稍后就会看到。此功能使您能够轻松处理历史数据,避免对具有各种时间戳和合并条件的复杂 WHERE 子句的需求。

创建具有系统时间的表

定义一个包含系统能够时间的表涉及到 3 个简单步骤:

  1. 为当前数据创建基础表,该表包含 3 个 TIMESTAMP(12) 列,两个用于系统时间的开始/结束点,一个用于事务开始时间。(DB2 使用事务开始时间列来跟踪事务何时首次执行一条更改表数据的语句。)您可以将所有 3 个 TIMESTAMP 列定义为 GENERATED ALWAYS,这样 DB2 就会自动在 INSERTUPDATEDELETE 上生成这些值。这使得您无需在写入数据库时为这些列指定值就可以确保时间戳是准确的。此外,您可以将这些列定义为 IMPLICITLY HIDDEN,这样就不会在 SELECT * 语句中显示它们。
  2. 创建历史表,将此表的结构定义为与包含当前数据的表相同。可使用 CREATE TABLE . . . LIKE 语句轻松实现此目的。
  3. 修改当前表,以实现版本控制并识别历史表

让我们逐步分析一个示例,看看告诉 DB2 使用系统时间自动维护多个数据版本有多简单。

步骤 1:创建一个具有 SYSTEM_TIME 时间段的表
—(我们的定义指出 TRANS_START 列将是隐藏列。)

清单 1. 创建一个具有 SYSTEM_TIME 时间段的表
CREATE TABLE policy ( 
   id             INT primary key not null,
   vin            VARCHAR(10),
   annual_mileage INT,
   rental_car     CHAR(1),
   coverage_amt   INT,
   sys_start      TIMESTAMP(12) GENERATED ALWAYS AS ROW BEGIN NOT NULL,
   sys_end        TIMESTAMP(12) GENERATED ALWAYS AS ROW END NOT NULL,
   trans_start    TIMESTAMP(12) GENERATED ALWAYS 
                             AS TRANSACTION START ID IMPLICITLY HIDDEN,
  PERIOD SYSTEM_TIME (sys_start, sys_end) 
 );

步骤 2:创建一个关联的历史表

清单 2.
CREATE TABLE policy_history LIKE policy;

步骤 3:启用版本控制

清单 3.
ALTER TABLE policy ADD VERSIONING USE HISTORY TABLE policy_history;

表 2表 3 演示了最终创建的两个空表。在整个这一节,我们将历史表绘制为灰色,帮助您轻松将它与当前表区分开来。

表 2. POLICY 表(包含当前数据)
POLICY
IDVINannual_mileagerental_carcoverage_amtsys_startsys_endtrans_start
表 3. POLICY_HISTORY 表(包含历史数据)
POLICY_HISTORY
IDVINannual_mileagerental_carcoverage_amtsys_startsys_endtrans_start

您也可以使用 ALTER TABLE 语句来修改现有的表,从而跟踪系统时间。为此,需要添加适当的 TIMESTAMP(12) 列并定义 PERIOD SYSTEM_TIME

DB2 for Linux, UNIX, and Windows 支持在一个用户表和关联的历史表之间自动进行模式演化。例如,如果您向 POLICY 表添加一个列,DB2 也会向 POLICY_HISTORY 表添加相同的列。

向包含系统时间的表插入数据

向包含系统时间的表插入数据与向普通表插入数据没任何差别。例如,想象一下这样的场景:在 2010 年 11 月 15 日,您需要向 POLICY 表输入两条新的汽车保险保单记录。以下语句可完成此任务:

清单 4. 向包含系统时间的表插入数据
INSERT INTO policy(id, vin, annual_mileage, rental_car, coverage_amt) 
     VALUES(1111,  'A1111',  10000, 'Y', 500000);

INSERT INTO policy(id, vin, annual_mileage, rental_car, coverage_amt) 
     VALUES(1414,  'B7777',  14000, 'N', 750000);

当将每行插入当前表时,DB2 会为系统事件列和事务开始时间生成合适的 TIMESTAMP(12) 值。请注意,所有这些值都不会在 INSERT 语句中引用;DB2 会自动记录必要的信息。表 4表 5 给出了执行此查询后 POLICY 和 POLICY_HISTORY 表的内容。(为了使我们的示例更容易理解,表 4 和表 5 仅显示了 TIMESTAMP(12) 列的日期部分。日期以年-月-日的格式显示。因为包含事务开始时间的列被定义为隐藏列,所以我们在表 4 和表 5 中也忽略了它。)

表 4. 在 2010 年 11 月 15 日执行 INSERT 后的当前表的内容
POLICY
IDVINannual_mileagerental_carcoverage_amtsys_startsys_end
1111A111110000Y5000002010-11-159999-12-30
1414B777714000N7500002010-11-159999-12-30
表 5. 在 2010 年 11 月 15 日执行 INSERT 后的历史表的内容
POLICY_HISTORY (empty)
IDVINannual_mileagerental_carcoverage_amtsys_startsys_end

POLICY 表中的 SYS_START 值反映了插入行的时间(在我们的例子中为 2010 年 11 月 15 日)。SYSTEM_END 值设置为 9999 年 12 月 30 日,以表明这些行不会过期(也就是说这些行包含最新的数据)。

更新包含系统时间的表中的数据

当更新当前数据时,DB2 会在合适的历史表中自动维护数据的旧版本。此操作是透明 ,无需任何编程或用户工作。想象一下此场景:在 2011 年 1 月 31 日执行以下语句,将保单 1111 的保额更改为 750000:

清单 5. 更新包含系统时间的表中的数据
UPDATE policy 
SET coverage_amt = 750000 
WHERE id = 1111;

让我们分析一下 DB2 如何处理此语句。如 表 6表 7 中所示,DB2 更新当前表中的行的值。此外,它将旧行的副本复制到历史表。对于两个表,DB2 正确地记录这些行的系统时间开始和结束值。具体而言,DB2 将历史表中的这一行的 SYS_END 列值设置为发出 UPDATE 语句的事务的时间。所有这些操作会自动并对用户透明地执行。(尽管表 6 和表 7 中未显示,但 DB2 还记录了两个表中的事务开始时间。)

实际上,DB2 对此 UPDATE 语句的处理会这样记录:保单 1111 在 2010 年 11 月 15 日到 2011 年 1 月 31 日期间的保额设置为 500000,在这之后保额设置为 750000。

表 6. 在 2011 年 1 月 31 日执行 UPDATE 后当前表的内容
POLICY
IDVINannual_mileagerental_carcoverage_amtsys_startsys_end
1111A111110000Y7500002011-01-319999-12-30
1414B777714000N7500002010-11-159999-12-30
表 7. 在 2011 年 1 月 31 日执行 UPDATE 后历史表的内容
POLICY_HISTORY
IDVINannual_mileagerental_carcoverage_amtsys_startsys_end
1111A111110000Y5000002010-11-152011-01-31

您可能已想到,对保单的任何后续更新都以一种类似方式处理。例如,假设在 2012 年 1 月 31 日更新了保单 1111,更改了保险保单的多处细节,比如年度里程估算、是否支持租车和总保额。以下是相应的 UPDATE 语句:

清单 6. 后续更新
UPDATE policy 
SET annual_mileage = 5000, rental_car='N', coverage_amt = 250000 
WHERE id = 1111;

执行此语句会导致 DB2 自动修改 POLICY 和 POLICY_HISTORY 表,如 表 8表 9 中所示。

表 8. 在 2012 年 1 月 31 日执行 UPDATE 后当前表的内容
POLICY
IDVINannual_mileagerental_carcoverage_amtsys_startsys_end trans_start
1111A11115000N2500002012-01-319999-12-30
1414B777714000N7500002010-11-159999-12-30
表 9. 在 2012 年 1 月 31 日执行 UPDATE 后历史表的内容
POLICY_HISTORY
IDVINannual_mileagerental_carcoverage_amtsys_startsys_end trans_start
1111A111110000Y5000002010-11-152011-01-31
1111A111110000Y7500002011-01-312012-01-31

从包含系统时间的表中删除数据

当删除当前数据时,DB2 自动从当前表中删除数据,并在合适的历史表中维护数据的旧版本。DB2 在历史表中将(已删除的)数据的结束时间设置为 DELETE 语句的事务开始时间。此操作是透明的,无需任何编程或用户工作。稍后您就会看到,用户可通过包含合适的时间段规范的查询来访问这个已删除的数据(即旧数据版本)。

想象这样的场景,在 2012 年 3 月 31 日使用以下语句删除行 Policy 1414:

清单 7.
DELETE FROM policy WHERE id = 1414;

表 10表 11 中所示,DB2 从当前表中删除该行,并在历史表中记录旧版本,将此行的 SYS_END 列值设置为其删除日期(2012 年 3 月 31 日)。

表 10. 在 2012 年 3 月 31 日执行 DELETE 后的当前表的内容
POLICY
IDVINannual_mileagerental_carcoverage_amtsys_startsys_end
1111A11115000N2500002012-01-319999-12-30
表 11. 在 2012 年 3 月 31 日执行 DELETE 后的历史表的内容
POLICY_HISTORY
IDVINannual_mileagerental_carcoverage_amtsys_startsys_end
1111A111110000Y5000002010-11-152011-01-31
1111A111110000Y7500002011-01-312012-01-31
1414B777714000N7500002010-11-152012-03-31

查询包含系统时间的表

查询包含系统时间的表非常简单。基本 SELECT 语句的语法和语义保持不变。具体而言,您可能已想到,没有任何时间段规范的 SELECT 语句适用于当前表中的数据。因此,向现有表添加系统时间支持不会影响现有的应用程序、存储过程和数据库报告。相反,您将能够在 SELECT 语句中包含 3 个新的时间段规范,从而可以透明地访问历史数据(或当前数据和历史数据的组合)。

让我们看一些示例,让您了解编写涉及系统时间的时态查询是多么的简单。我们将从最基本的场景开始:您仅需要访问最新的信息。

假设您的当前表和历史表包含 表 10表 11 中所示的汽车保险保单数据。请考虑以下查询:

清单 8.
SELECT coverage_amt FROM policy WHERE id = 1111;

您可能已想到,DB2 返回一个包含保额 250000 的行。(这是存储在包含保单 1111 的最新信息行中的金额)。

如果您希望使用数据的较旧版本,会发生什么?您只需查询的 FROM 子句中包含 3 个受支持的时间段规范中的一个:

  • FOR SYSTEM_TIME AS OF ... 支持您查询截至某个时间点的数据。
  • FOR SYSTEM_TIME FROM ... TO ... 支持您查询从某个时间点到另一个时间点的数据。DB2 对该时间段规范使用了一个 “包含-排除” 方法。换句话说,指定的开始时间包含在时间段内,但指定的结束时间不包含在内。
  • FOR SYSTEM_TIME BETWEEN ... AND ... 支持查询开始/结束时间范围内的数据。DB2 对该时间段规范使用了一种 “包含-包含” 方法,换句话说,指定的开始和结束时间都包含在时间段内。

让我们详细分析一下一个示例。要获得数据库中记录的保单 1111 在 2010 年 12 月 1 日的保额,可以编写以下查询:

清单 9.
SELECT coverage_amt 
FROM policy FOR SYSTEM_TIME AS OF '2010-12-01' 
WHERE id = 1111;

为了解析此查询,DB2 透明地访问历史表中的数据,以获取正确的信息(为此查询返回值 500000)。请注意,您不需要在查询中引用历史表。FOR SYSTEM_TIME 时间段规范会告诉 DB2 在适当的时刻自动访问历史表。

我们来看另一个例子。要确定车辆 A1111 自 2011 年 11 月 30 日以后的保单记录总数,可以编写以下查询:

清单 10.
SELECT count(*) 
FROM policy FOR SYSTEM_TIME FROM '2011-11-30' 
                              TO '9999-12-30' 
WHERE vin = 'A1111';

根据我们的示例数据,此查询返回值 2。如 表 10表 11 所示,当前表中的一行和历史表中的一行(第二行)满足此查询条件。请注意,此查询在 FROM 子句中仅引用当前表,但根据系统时间时间段规范,DB2 会自动访问历史表。

使用业务时间跟踪有效期

前面已经提到,业务时间涉及到跟踪某些业务条件有效的时间。例如,一款给定的产品可能在某个月内被定价为 45 美元,而在另一个月内被定价为 50 美元。或者一张信用卡在第一年可能具有 16% 的利率,而在下一年则具有 18% 的利率。业务时间在这些情况下非常有用,因为它使应用程序能够轻松跟踪和管理有效期。

像系统时间一样,业务时间需要使用一个时间段(业务条件的开始和结束时间点)。但是,与系统时间不同的是,业务实践没有独立的历史表。过去、现在和未来的有效期和它们的关联业务数据都是在一个表中进行维护的。此外,用户在向数据库中写入数据时需要提供他们的业务时间段列的开始/结束值。最后,不需要使用事务开始时间列。

让我们分析一下如何在示例应用程序场景中使用这项新的 DB2 技术。

创建包含业务时间的表

创建包含业务时间的表仅涉及到包含针对时间段的开始/结束时间点的合适的列和一个 PERIOD BUSINESS_TIME 子句。业务事件开始/结束列可以是日期或时间戳数据类型。

这是一个为汽车保险保单创建表的简单例子,其中包含它们的有效业务日期。在此例子中,BUS_START and BUS_END 列被定义为 DATE 数据类型。PERIOD BUSINESS_TIME 子句要求 DB2 使用这些列来跟踪每行的业务时间值的开始和结束时间点。为了确保时态数据的完整性,DB2 自动生成一个隐式约束来强制要求 BUS_START 值小于 BUS_END 值。

清单 11. 创建包含业务时间的表
CREATE TABLE policy ( 
  id              INT NOT NULL,
  vin             VARCHAR(10),
  annual_mileage  INT,
  rental_car      CHAR(1), 
  coverage_amt    INT, 
  bus_start       DATE NOT NULL,
  bus_end         DATE NOT NULL,
  PERIOD BUSINESS_TIME(bus_start, bus_end),
  PRIMARY KEY(id, BUSINESS_TIME WITHOUT OVERLAPS) );

CREATE TABLE 语句中的主键约束使用可选的关键字 BUSINESS_TIME WITHOUT OVERLAPS。这告诉 DB2 确保主键值在业务时间中的任何时刻都是惟一的。对于我们的保险保单示例,BUSINESS_TIME WITHOUT OVERLAPS 表示同一个策略不能同时拥有两个有效的版本或状态。

您也可以使用 ALTER TABLE 语句修改现有的表,以跟踪业务时间。为此,需要添加合适的 DATE 或 TIMESTAMP 列并定义 PERIOD BUSINESS_TIME

向包含业务时间的表中插入数据

向包含业务时间的表中插入一行非常简单:只需为所有非空列提供合适的值,包括表示业务时间开始和结束值的列。例如,要在我们包含业务时间的示例 POLICY 表中插入一些行,我们可以执行以下语句:

清单 12. 向包含业务时间的表中插入数据
INSERT INTO policy 
  VALUES(1111,'A1111',10000,'Y',500000,'2010-01-01','2011-01-01');
INSERT INTO policy 
  VALUES(1111,'A1111',10000,'Y',750000,'2011-01-01','9999-12-30');
INSERT INTO policy 
  VALUES(1414,'B7777',14000,'N',750000,'2008-05-01','2010-03-01');
INSERT INTO policy 
  VALUES(1414,'B7777',12000,'N',600000,'2010-03-01','2011-01-01');

表 12 演示了此表的最终内容。

表 12. 执行 INSERT 语句后的 POLICY 表
POLICY
IDVINannual_mileagerental_carcoverage_amtbus_startbus_end
1111A111110000Y5000002010-01-012011-01-01
1111A111110000Y7500002011-01-019999-12-30
1414B777714000N7500002008-05-012010-03-01
1414B777712000N6000002010-03-012011-01-01

使用业务词汇总结一下此表的内容可能很有帮助。该数据表明,保单 1111 拥有在 2010 年 1 月 1 日到 2011 年 1 月 1 日之间有效的保额 500000。在 2011 年 1 月 1 日以后,保额 750000 将生效。类似地,该表表明,从 2008 年 5 月 1 日到 2010 年 3 月 1 日,对于一辆年度里程估算值为 14000 的车辆,保单 1414 拥有 750000 的保额。从 2010 年 3 月 1 日到 2011 年 1 月 1 日,此保单的保额更改为 600000,被保险车辆预计每年将行驶 12000 英里。

让我们看看此表上(使用 BUSINESS_TIME WITHOUT OVERLAPS 子句)定义的时态惟一性约束的效果。假设我们执行了以下 INSERT 语句:

清单 13.
INSERT INTO policy 
  VALUES(1111,'A1111',10000,'Y',900000,'2010-06-01','2011-09-01');

DB2 将拒绝此语句并发出一条错误消息,因为该语句尝试在一个或多个其他行被视为对保单 1111 仍然有效时为保单添加一个行。这违背了时态惟一性约束。如果我们希望调整 2010 年 6 月 1 日到 2011 年 9 月 1 日期间保单 1111 的保额,可以使用合适的 UPDATE 语句来完成此操作。

更新包含业务时间的表中的数据

您仍然可以为包含业务时间段的表编写传统的 UPDATE 语句。此外,也可以使用新的 FOR PORTION OF BUSINESS_TIME 子句限制对特定业务时间段的更新。如果您的更新影响了未完全包含在指定时间段内的一行中的数据,DB2 会更新该时间段子句指定的行范围,并插入更多行来记录未包含在更新操作中的时间段的旧值。我们可以通过一个例子来了解具体的工作原理。

假设您希望更新从 2010 年 6 月 1 日到 2011 年 9 月 1 日期间的保单 1111 的信息,具体而言,您希望修改该时间段的保额。您可以编写以下 UPDATE 语句:

清单 14.
UPDATE policy 
  FOR PORTION OF BUSINESS_TIME FROM '2010-06-01' TO '2011-09-01'
SET coverage_amt = 900000
WHERE id = 1111;

请注意,查询中的时态限制 (FOR PORTION OF BUSINESS_TIME FROM . . . TO . . .) 显示在表名称之后,而不是 WHERE 子句的一部分。

图 2 中所示,最初有两行与保单 1111 相关。这两行都受到我们的 UPDATE 语句的影响,因为更新的业务时间段与每一行的业务时间段都有一部分是重叠的。这种重叠如图 2 中的上半部分所示。当 DB2 应用更新时,两个最初的行中的每一行会拆分为两行,如图 2 中的下半部分所示。DB2 自动调整各行的有效期。

图 2. UPDATE 语句导致的行拆分
该图显示了一行在更新之前和之后的图像
该图显示了一行在更新之前和之后的图像

表 13 显示了 POLICY 表的最终状态。表 12 中的第一行被拆分为两个新行,如 表 13 中所示。表 12 中的第二行也拆分为两个新航,如 表 13 中所示。

表 13. 对保单 1111 执行 UPDATE 之后的 POLICY 表
POLICY
IDVINannual_mileagerental_carcoverage_amtbus_startbus_end
1111A111110000Y5000002010-01-012010-06-01
1111A111110000Y9000002010-06-012011-01-01
1111A111110000Y9000002011-01-012011-09-01
1111A111110000Y7500002011-09-019999-12-30
1414B777714000N7500002008-05-012010-03-01
1414B777712000N6000002010-03-012011-01-01

从包含业务时间的表中删除数据

要从包含业务时间段的表中删除数据,您可以将删除操作限制到特定的时间范围,只需指定 FOR PORTION OF BUSINESS_TIME 子句。如果要删除的一行包含未完全包含在指定时间范围内的数据,DB2 将确保保留了该行中的恰当信息。让我们通过一个示例来说明一下。

假设一位客户希望在 2010 年 6 月 1 日到 2011 年 1 月 1 日之间暂停他的汽车保险保单。此外,还假设该客户采用保单 1414,那么以下 DELETE 语句将完成此任务:

清单 15.
DELETE FROM policy
  FOR PORTION OF BUSINESS_TIME FROM '2010-06-01' TO '2011-01-01'
WHERE id = 1414;

表 14 演示了表的最终结果。请注意,DB2 修改了最后一行,以反映保单 1414 的新的 BUS_END 日期。

表 14. 执行涉及一个业务时间段的 DELETE 后的 POLICY 表
POLICY
IDVINannual_mileagerental_carcoverage_amtbus_startbus_end
1111A111110000Y5000002010-01-012010-06-01
1111A111110000Y9000002010-06-012011-01-01
1111A111110000Y9000002011-01-012011-09-01
1111A111110000Y7500002011-09-019999-12-30
1414B777714000N7500002008-05-012010-03-01
1414B777712000N6000002010-03-012010-06-01

查询包含业务时间的表

查询包含业务时间的表非常简单。有 3 个可选的子句支持您指定时态查询,从而可以评估过去、当前和未来的业务条件。当然,您仍然可以对包含业务时间段的表编写基本的 SELECT 语句(也就是非时态查询),DB2 对这些查询的处理将保持不变。

我们将分析一些示例,以便使您能够了解到编写涉及业务时间的时态查询有多简单。但是首先我们要从最基本的场景入手:您不希望考虑任何时态条件。

假设您的 POLICY 表包含 表 15 中所示的信息。(这是我们在创建 POLICY 表并插入 4 行之后的 表 12 表中所示的相同信息。)

表 15. 执行涉及业务时间段的 DELETE 之后的 POLICY 表
POLICY
IDVINannual_mileagerental_carcoverage_amtbus_startbus_end
1111A111110000Y5000002010-01-012011-01-01
1111A111110000Y7500002011-01-019999-12-30
1414B777714000N7500002008-05-012010-03-01
1414B777712000N6000002010-03-012011-01-01

要确定您拥有的针对保单 1111 的保险记录总数,可以编写以下查询:

清单 16.
SELECT COUNT(*)
FROM policy 
WHERE id = 1111;

因为此查询不包含时态谓词,所以 DB2 返回值 2。

如果您希望考虑这些保险保单的各种时态条件,该怎么办?只需在查询的 FROM 子句中紧挨表名称之后包含 3 个支持的时间段规范之一:

  • FOR BUSINESS_TIME AS OF ...
  • FOR BUSINESS_TIME FROM ... TO ...
  • FOR BUSINESS_TIME BETWEEN ... AND ...

让我们仔细分析一个示例。要获得保单 1111 在 2010 年 12 月 1 日生效的保额,可以编写以下查询:

清单 17.
SELECT coverage_amt
FROM policy FOR BUSINESS_TIME AS OF '2010-12-01'
WHERE id = 1111;

DB2 返回结果 500000。

要识别在 2009 年 1 月 1 日到 2011 年 1 月 1 日之间适用于保单 1414 的条款,可以编写以下 SQL 语句,该语句生成的结果如 表 16 所示。

清单 18.
SELECT *
FROM policy FOR BUSINESS_TIME FROM '2009-01-01' TO '2011-01-01'
WHERE id = 1414;
表 16. 查询结果
IDVINannual_mileagerental_carcoverage_amtbus_startbus_end
1414B777714000N7500002008-05-012010-03-01
1414B777712000N6000002010-03-012011-01-01

对包含业务时间的表的时态查询会内部重写为另一个查询,这个查询在为业务时间的开始和结束时刻而定义的适当日期或时间戳的列中包含适当的 WHERE 子句谓词。

高级概念

前面已经提到过,DB2 对系统和业务时间的支持非常简单。但 DB2 时态数据管理功能并不只有这些。我们提到 DB2 支持在双时态表中维护系统时间和业务时间。而且,DB2 on Linux, UNIX, and Windows 支持时态视图和一种使数据库的功能非常类似于时光机的注册表设置。这些以及其他高级时态主题的详细讨论超出了本文的范围,我们只打算简短介绍一下每个主题。

双时态表

双时态表支持同时使用系统时间和业务时间来管理数据,将二者的优势结合在一起。例如,您可以决定使用业务时间来管理应用程序的逻辑时间观念,比如保险保单的有效期,使用系统时间来跟踪事务对这些保单所做更改的历史记录和时间戳。

管理员可轻松创建或修改一个表来同时包含系统时间和业务时间。例如,以下 CREATE TABLE 语句定义了一个双时态表,该表在 BUS_START 和 BUS_END 列上有一个 BUSINESS_TIME 时间段,在 SYS_START 和 SYS_END 列上有一个 SYSTEM_TIME 时间段。

清单 19. 创建双时态表
CREATE TABLE policy ( 
  id              INT NOT NULL,
  vin             VARCHAR(10),
  annual_mileage  INT,
  rental_car      CHAR(1), 
  coverage_amt    INT, 
  bus_start       DATE NOT NULL,
  bus_end         DATE NOT NULL,
  sys_start       TIMESTAMP(12) GENERATED ALWAYS AS ROW BEGIN NOT NULL,
  sys_end         TIMESTAMP(12) GENERATED ALWAYS AS ROW END NOT NULL,
  trans_start     TIMESTAMP(12) GENERATED ALWAYS 
                             AS TRANSACTION START ID IMPLICITLY HIDDEN,
  PERIOD SYSTEM_TIME (sys_start, sys_end),
  PERIOD BUSINESS_TIME(bus_start, bus_end),
  PRIMARY KEY(id, BUSINESS_TIME WITHOUT OVERLAPS)
);

创建这个双时态表之后,您需要创建一个兼容的历史表并启用版本控制。然后可以使用前面介绍的系统时间和业务时间语法在此表中插入、更新、删除和查询各行。我们将介绍一个简短的例子。

假设您有一个双时态 POLICY 表和关联的历史表,如 表 17表 18 所示。(为保持简单,省略了 TRANS_START 列。)

表 17. 示例双时态 POLICY 表
POLICY
IDVINannual_mileagerental_carcoverage_amtbus_startbus_endsys_startsys_end
表 18. 示例双时态 POLICY_HISTORY 表
POLICY_HISTORY(包含历史数据)
IDVINannual_mileagerental_carcoverage_amtbus_startbus_endsys_startsys_end

假设客户服务代表执行了以下活动:

  • 2011 年 11 月 15 日,某人为车辆 A1111 创建了保单 1111。该保单(保额为 500000)被设置为从 2012 年 1 月 1 日开始生效。
    清单 20.
    INSERT INTO policy(id, vin, annual_mileage, rental_car, 
                       coverage_amt, bus_start, bus_end) 
    VALUES(1111,'A1111',10000,'Y',500000,'2012-01-01','9999-12-30');
  • 2012 年 3 月 1 日,某人更改了保单 1111 的条款,使其从 2012 年 6 月 1 日开始生效。该更改降低了保额并删除了租车权益,使用了以下更新语句:
    清单 21.
    UPDATE policy 
      FOR PORTION OF BUSINESS_TIME FROM '2012-06-01' TO '9999-12-30'
    SET coverage_amt = 250000, rental_car='N'
    WHERE id = 1111;

表 19表 20 演示了执行这些操作后 POLICY 和 POLICY_HISTORY 表的内容。(为了简便起见,这里仅显示了 SYS_START 和 SYS_END 的时间戳的日期部分。)

表 19. 在双时态 POLICY 表上执行 UPDATE 的结果
POLICY
IDVINannual_mileagerental_carcoverage_amtbus_startbus_endsys_startsys_end
1111A111110000Y5000002012-01-012012-06-012012-03-019999-12-30
1111A111110000N2500002012-06-019999-12-302012-03-019999-12-30
表 20. 在双时态 POLICY_HISTORY 表上执行 UPDATE 的结果
POLICY_HISTORY
IDVINannual_mileagerental_carcoverage_amtbus_startbus_endsys_startsys_end
1111A111110000Y5000002012-01-019999-12-302011-11-152012-03-01

现在想像一下该场景:记录了因为 2012 年 6 月 20 日发生的汽车事故而针对保单 1111 进行的索赔。客户服务代表可使用以下查询确定保额资格:

清单 22.
SELECT vin, rental_car, coverage_amt
FROM policy FOR BUSINESS_TIME AS OF '2012-06-20'
WHERE id = 1111;

DB2 将返回如 表 21 中所示的信息,该信息报名该车在保险范围内,但客户没有资格租车。

表 21. 查询结果
VINrental_carcoverage_amt
A1111N250000

如果客户在 2012 年 7 月 10 日打电话投诉,要求了解过去两年对他的保单执行的所有更改,客户服务代表可发出此查询:

清单 23. 查询 bitemporal 数据
SELECT id, vin, annual_mileage, rental_car, coverage_amt, 
       bus_start, bus_end, sys_start, sys_end   
FROM policy FOR SYSTEM_TIME FROM '2010-07-10' TO '2012-07-11'
WHERE id = 1111;

DB2 将返回如 表 22 所示的结果。客户服务代表可给出更改保单的日期,以及这些更改生效(或即将生效)的时间。

表 22. 查询结果(最后一行来自历史表)
IDVINannual_mileagerental_carcoverage_amtbus_startbus_endsys_startsys_end
1111A111110000Y5000002012-01-012012-06-012012-03-019999-12-30
1111A111110000N2500002012-06-019999-12-302012-03-019999-12-30
1111A111110000Y5000002012-01-019999-12-302011-11-152012-03-01

视图

DB2 for Linux, UNIX, and Windows 支持为时态表使用两种类型的视图。这些视图为应用程序设计带来了灵活性,支持向不同用户公开不同时刻或时间段的数据。

首先,可以在时态表上定义视图,其中视图定义包含一个 FOR SYSTEM_TIMEFOR BUSINESS_TIME 子句,用于将视图限制到某个时刻或时间段。然后可以对这些视图执行常规 SQL 查询。对这些视图的查询不能包含 FOR SYSTEM_TIMEFOR BUSINESS_TIME 子句,因为查询中的时间约束可能与视图中的时间约束相冲突,或者产生歧义。

其次,可以在时态表上定义视图,而视图定义中没有 FOR SYSTEM_TIMEFOR BUSINESS_TIME 约束。这些视图公开所有时刻的数据,可使用包含 FOR SYSTEM_TIMEFOR BUSINESS_TIME 子句的查询进行访问。这样的子句然后会自动应用到包含 SYSTEM_TIMEBUSINESS_TIME 时间段的视图定义中的每个表。

总之,DB2 在处理视图和时态数据时为您提供了很高的灵活性。您可以在视图定义或针对时态表上的视图的查询中使用时态约束。

针对 “时间旅行” 的注册表设置

新的注册表设置支持对某个时刻的数据运行现有的应用程序,而无需更改应用程序本身。例如,假设您的一个现有的应用程序包含您需要不时运行的许多 SQL 查询或一组报告查询。在使用 DB2 中新的时态功能时,您可能希望对过去的某个系统时刻或者过去与未来的某个业务时刻运行这些查询。但是,使用 FOR SYSTEM_TIMEFOR BUSINESS_TIME 子句增强所有现有的 SQL 语句可能需要花很大精力。类似地,为所有受影响的时态表创建视图也可能非常乏味。因此,DB2 for Linux, UNIX, and Windows 提供了特殊的注册表,将一个数据库会话设置为特定的时刻。

例如,您可以使用以下 SET 命令将会话的系统时间设置为 2008 年 1 月 1 日上午 10 点:

清单 24.
SET CURRENT TEMPORAL SYSTEM_TIME = '2008-01-01 10:00:00';

随后,在相同会话中在系统时间段时态表(双时态表)上执行的任何查询都会看到自 2008 年 1 月 1 日上午 10 点之后的数据。也就是说,会话中的所有查询都会在内部进行重写,以便使用您之前看到的子句 FOR SYSTEM_TIME AS OF '2008-01-01 10:00:00'。DB2 会自动为您执行此重写操作;您无需以任何方式更改应用程序和 SQL 语句。

类似地,可以设置以下特殊的注册表来查看一个月前的数据:

清单 25.
SET CURRENT TEMPORAL SYSTEM_TIME = current timestamp – 1 MONTH;

在将 CURRENT TEMPORAL SYSTEM_TIME 特殊注册表设置为 NULL 以外的值时,对系统时间段时态表所执行的任何 DML 操作(插入、更新、删除、合并)都没有意义并且不受支持。

对于业务时间,您可以设置 CURRENT TEMPORAL BUSINESS_TIME 特殊注册表,对过去或未来的业务时刻应用查询、更新、删除等操作。例如,可以考虑以下注册表设置:

清单 26.
SET CURRENT TEMPORAL BUSINESS_TIME = '2012-06-01';

它表明对包含业务时间的表(或双时态表)的查询和 DML 语句是经过重写的,以便使用子句 FOR BUSINESS_TIME AS OF '2012-06-01'。再次申明,DB2 会自动为您执行此重写操作。

类似于视图的使用指南,您必需避免同时应用两个时间约束。也就是说,您要么设置一个系统时间(业务时间)特殊注册表,要么使用系统时间(业务时间)来约束您的 SQL 语句,但不能同时执行这两项操作。这个时光机一次仅接受一个目的地(这不是双关语)。

迁移到 DB2 时态表

DB2 中的时态支持旨在简化现有数据库表向新时态功能的迁移。有两种常见的场景需要考虑:

  1. 如果您已有的表没有时间戳列,而您希望将这些表转换为包含系统时间或业务时间段的时态表,那么可以使用 ALTER TABLE 语句向该表添加必需的时间戳列和时间段定义。对于系统时间段时态表,随后还需要创建一个类似原始表的历史表,使用另一个 ALTER TABLE 语句启用版本控制。
  2. 第二个场景涉及到迁移已包含时间戳列的表。例如,您可能已经在使用触发器设置时间戳列和填充历史表。在这种情况下,可以重用现有的时间戳列和历史表。只需执行 ALTER TABLE 语句声明:现有的时间戳列现在解释为一个 SYSTEM_TIME 时间段。然后可以丢弃触发器,执行另一个 ALTER TABLE 语句在基础表和历史表之间启用版本控制。业务时间也具有类似的迁移选项。

结束语

新的 DB2 时态数据支持提供了简单但成熟的功能来管理多个数据版本和跟踪有效业务日期。基于 SQL:2011 标准中的时态功能,DB2 使数据库专业人员能够以有效的方式处理时态数据,与将时态逻辑硬编码到触发器、存储过程或自有应用程序中相比,这节省了大量时间和精力。


相关主题


评论

添加或订阅评论,请先登录注册

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=816642
ArticleTitle=时间问题:DB2 10 中的时态数据管理
publish-date=05182012