在 DB2 中使用时态一致性管理时间

执行基于时间的引用完整性

IBM® DB2® 10 中的新时态特性为基于时间的数据管理提供了丰富的功能。例如,一个日期范围可分配给每行数据以指出您的应用程序或业务何时认为该行有效。支持跟踪此类业务有效性的表称作应用程序周期时态表,而且其业务时间周期可以是过去的、现在的或未来的。对于有外键关系的父表和子表,可扩展引用完整性的概念来考虑传统键值,以及任何给定行的业务时间周期。本文将介绍这种时态引用完整性 (temporal referential integrity),以及如何在 DB2 中强制执行基于时间的一致性。本文内容适用于 DB2 for z/OS® 和 DB2 for Linux®, UNIX®, and Windows®。所有 SQL 样例已在 DB2 10.1 for LUW® 中进行了测试。

Matthias Nicola, 高级技术人员, IBM  

作者照片:Matthias NicolaMatthias Nicola 是位于加州圣何塞的 IBM 硅谷实验室的一名高级软件工程师。他的工作重点是 DB2 性能与基线、XML、临时数据管理、数据库内分析及其他新兴技术。Matthias 与客户和业务合作伙伴紧密协作,协助他们设计、优化和实现 DB2 解决方案。之前,Matthias 曾在 Informix Software 公司负责数据仓库性能方面的工作。他从德国的亚琛科技大学获得计算机科学博士学位。


developerWorks 投稿作者

Martin Sommerlandt, DB2 专家, Consultant

Martin SommerlandtMartin Sommerlandt 是一名 IBM 认证的 DB2 应用程序开发人员和 DB2 数据库管理员。在 IBM 任职期间,他是 IBM 硅谷实验室的一个性能工程师,负责 DB2 新特性(包括时态表)的性能测试和分析。



2012 年 9 月 24 日

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

DB2 时态数据管理简介

DB2 10 for z/OS 和 DB2 10 for Linux, UNIX, and Windows 引入了基于时间的数据管理,允许您查询和操作过去、现在以及未来的数据,同时保留所有数据更改的完整历史记录。DB2 中的时态特性集合也被称之为 Time Travel Query。

DB2 支持 3 类时态表:

  • 系统周期时态表:DB2 以透明的方式不断地保留已更新或删除行的历史记录。使用 SQL 语言标准的新结构,用户可以 “回到过去” 并查询过去任一选择点的数据库。这是以内部分配的系统时间戳为基础,DB2 使用该时间戳来管理系统时间,也称之为事务处理时间
  • 应用程序周期时态表:应用程序提供日期或时间戳以描述数据的业务有效性。SQL 的新结构支持应用程序插入、查询、更新和删除过去、现在以及未来的数据。DB2 自动应用约束条件和行分割 (row-split) 来正确地维护应用程序提供的业务时间,也称之为有效时间
  • 双时态表:管理系统时间和业务时间。双时态表结合了系统周期和应用程序周期时态表的所有功能。这种结合使得应用程序能够管理数据的业务有效性,同时 DB2 可保存所有更新和删除的完整历史记录。

在本文其余部分,我们假设您熟悉 DB2 中应用程序周期时态表的基本知识。时态引用完整性 (RI) 的讨论也适用于双时态表中的业务时间。当您更新或删除业务时间中一个指定部分的数据时,应该了解如何创建和使用这些表,并了解 DB2 如何执行行分割 (row-split)。文章 “时间问题:DB2 中的时态数据管理” 提供了关于这些主题的介绍。


什么是时态引用完整性?

一个示例

在本小节中,我们首先重温了关系数据库中传统的引用完整性 (RI),然后对时态 RI 展开讨论。我们的场景涉及一个虚拟业务,该业务从供应商处接收产品,然后将这些产品卖给客户,偶尔也会进行促销活动,在一定时间段内以折扣价格销售选定的产品。为了帮助您理解如何实现和执行时态 RI,我们使用该应用程序场景和样例数据。

传统 RI

清单 1 显示两个涉及管理该业务的表。父表,product_avail,提供的每个产品在其中都有一行,并识别提供该产品的供应商。子表,promotion,每个特价产品都有一行,由一个促销 ID、各自的产品 ID 和折扣价定义。两个表可能还有其他列,但是为了简单起见,这些列目前已经足够了。

清单 1. 父表和子表之间引用完整性的简单示例
CREATE TABLE product_avail(
    prodID     INTEGER NOT NULL, 
    supplier   VARCHAR(32),
  PRIMARY KEY(prodID) );

CREATE TABLE promotion(
    promoID    INTEGER NOT NULL, 
    prodID     INTEGER NOT NULL, 
    price      DECIMAL(10,2), 
  PRIMARY KEY(promoID),
  FOREIGN KEY (prodID) REFERENCES product_avail(prodID) );

因为只能对已有产品提供促销,所以 promotion 表中的每一行必须包含一个存在于 product_avail 表中的 prodID。这个条件被称为 引用完整性,由 promotion 表中的外键 (foreign key, FK) 约束声明并执行。有了该约束,任何试图使用 product_avail 表中不存在的 prodID 插入或更新促销表的操作都将被 DB2 拒绝。

引入时间

在我们的虚拟业务中,不同供应商可在每年的不同时间提供同一产品。同样,我们可以选择在不同的时间点对同一产品进行不同的促销活动。因为时间是业务中一个很重要的维度,我们创建 清单 1 所示的表作为应用程序周期时态表,这意味着我们将一个 BUSINESS_TIME 周期包含在这两个表中(参见 清单 2)。

新增的 BUSINESS_TIME 周期由一对日期或时间戳构成,另外还有一个 PERIOD 声明将这两列定义为一个周期。我们也可以选择使用可选关键字 BUSINESS_TIME WITHOUT OVERLAPS 扩展主键定义,这表明多个行可以拥有同一个 prodID 值,只要这些行的 BUSINESS_TIME 周期不重叠即可。同样,主键扩展也适用于 promotion 表。

因为 product_avail 表中的 prodID 列现在可以有重复值,在 promotion 表中将不再作为外键引用。我们需要以不同的方式执行 RI。

清单 2. 清单 1 中的表现在作为应用程序周期时态表
CREATE TABLE product_avail(
    prodID       INTEGER NOT NULL,
    supplier     VARCHAR(32),
    avail_start  DATE NOT NULL,
    avail_end    DATE NOT NULL,
    PERIOD BUSINESS_TIME (avail_start, avail_end),
    PRIMARY KEY(prodID, BUSINESS_TIME WITHOUT OVERLAPS)  );
                
CREATE TABLE promotion(
    promoID    INTEGER NOT NULL,
    prodID     INTEGER NOT NULL,
    price      DECIMAL(10,2),
    promo_start  DATE NOT NULL,
    promo_end    DATE NOT NULL,
    PERIOD BUSINESS_TIME (promo_start, promo_end),
    PRIMARY KEY(promoID, BUSINESS_TIME WITHOUT OVERLAPS)  );

图 1 显示表由 3 个产品和 2 个促销构成。例如,产品 9105 由供应商公司 A 交付,2012 年 1 月 1 日开始,2012 年 6 月 1 日结束,这意味着交付日期的最后一天是 2012 年 5 月 31 日。(注意,周期总是以 inclusive-exclusive 形式指定;指定的结束日期将不再属于有效期。)从 6 月 1 日起,产品 9015 由 B 公司提供,结束日期是 9 月 1 日。在 9 月到 10 月期间,我们不接收产品 9015 的供应,但是在 11 月到 12 月期间,该产品再次由 A 公司提供。当没有供应商提供产品时,则认为该产品已脱销。

图 1. 样例数据
该图显示了两个含样例数据的表

主键 (prodID, BUSINESS_TIME WITHOUT OVERLAPS) 确保同一时间不会出现两个供应商提供相同的产品。如果我们想要允许多个供应商同时提供相同的产品,需要将主键定义为 (prodID, supplier, BUSINESS_TIME WITHOUT OVERLAPS)

图 1 中的 promotion 表为产品 9105 定义了两个促销活动,第一个促销活动在 2012 年 1 月 15 日到 3 月 15 日期间以 $19.95 的价格销售该产品。也就是说该折扣价格的最后一天是 2012 年 3 月 14 日。第二个促销活动从 2012 年 5 月 1 日开始,7 月 1 日结束,折扣价格是 $16.95。其他时间,产品 9015 以正常价格为准。该价格存储在别处。

时态 RI

图 1 中的数据满足两个表之间的传统外键关系,因为每个促销引用一个位于 product_avail 表中的产品 ID。然而,我们的业务也必须确保促销仅在各个产品实际可用的时间段内提供。如果我们宣传一个产品折扣,然而这个产品却没有任何库存,想象一下客户是多么的困惑和不满。

一般来说,您可能希望能在两个相关表的父行和子行的业务时间周期之间执行一个时态关系。这类关系的示例包括以下内容:

  • 周期一致:子行的业务时间周期必须与单个父行的周期一致。在我们的示例中,该条件可能会强制使促销的持续时间总是与特定供应商提供该产品的周期相匹配。产品 ID 和周期开始和结束列上的传统 FK 可执行此关系。
  • 周期包含 (period containment):子行的业务时间周期必须完全包含在一个或多个父行的周期内。
  • 周期包含,单个父行:子行的业务时间周期必须完全包含在单个父行的周期内。
  • 重叠:子行的业务时间周期必须与一个父行的周期重叠。
  • 周期内开始 (start-within):子行的业务时间周期必须在父行的周期内开始。
  • 周期后开始 (start-after):子行的业务时间周期必须在父行的周期后开始。

图 2 形象地展示了 图 1 中样例数据的业务时间周期,并允许我们检查上面列出的一些时态关系。表的每一行由一个矩形表示,该矩形的长表示该行业务时间周期的持续时间。product_avail 表的行用蓝色显示,promotion 表的行用绿色显示。

我们可在 图 2 中进行以下观察:

  • promoID 为 16 和 17 的促销不满足与单个父行周期一致的条件,因为促销周期比其在 product_avail 表中的父行周期短。
  • 促销 16 满足周期包含条件(甚至是单个父行的周期包含),因为它的报价周期包含在供应商公司 A 提供产品 9105 的一个周期内。
  • 促销 17 满足周期包含(多个父行),因为它的报价周期由 5 月和 6 月构成,在此期间内该产品是由 A 公司和 B 公司共同供应,且没有供应间隙。促销 17 不满足单个父行的周期包含。
  • 促销 16 和 17 都满足与第一个产品行重叠和周期内开始的关系。
图 2. product_avail 周期(顶部、蓝色)和 promotion 周期(底部、绿色)时间线
该图显示了两个周期的时间线

根据您自己的用例和应用程序需求,需要确定在父行和子行之间执行哪个时态关系。

对于我们的产品和促销示例,选择周期包含(一个或多个父行)作为定义时态 RI 的期望关系。因此,本文其余部分假设周期包含作为时态 RI 概念。

时态 RI 违规

我们的市场营销部门预计在 8 月和 9 月降价销售,决定在此时间段内仅以 $15.95 的价格出售产品 9105。下列 INSERT 是专为促销 18 发布的:

INSERT INTO promotion (promoID, prodID, price, promo_start, promo_end)
VALUES (18, 9105, '2012-08-01', '2012-10-01');

这个新促销并不违反传统 RI,因为外键 9105 在 product_avail 表中存在。然而,在 图 3 中,您可以看到促销 18 违反了我们选择的时态 RI 概念,即周期包含。这个新促销在整个 9 月份一直执行,但是在 9 月并没有供应商提供产品 9105(且我们也没有库存)。

如果您选择了重叠或周期内开始条件来定义时态 RI,促销 18 就不会违规了。

图 3. 促销 18 违反时态 RI
该图显示了两个周期的时间线

我们可能会在 product_avail 表中发现促销 18 的结束日期 (2012-10-01) 超出了产品 9105 的任何现有业务时间周期,从而检测并避免时态 RI 违规。要改变这种情况,促销 18 需要进行修改或从 promotion 表中删除。

现在,我们考虑另一个输入的促销,对促销 19 使用以下 INSERT 语句:

INSERT INTO promotion (promoID, prodID, price, promo_start, promo_end)
VALUES (19, 9105, '2012-08-01', '2012-12-01');

该促销活动于 2012 年 8 月 1 日开始,12 月 1 日结束。尽管该促销的开始和结束日期都在现有产品可用周期之内,还是违反了时态 RI(周期包含),因为这两个日期之间有一个供应间隙。如 图 4 所述,促销 19 在 8 月、9 月、10 月和 11 月都是有效的,但是在 9 月和 10 月这段时间没有供货。

图 4. 促销 18 违反了时态 RI
该图显示了两个周期的时间线

促销 19 示例阐述了一个重要事实:如果特定键值的父周期有间隙,仅通过检查子周期的开始日期和结束日期是否包含在现有父周期中是不能检测到包含违规的。然而,如果您确认相同键值的父行之间没有周期间隙,周期包含可能更容易执行。

促销 17 阐述了另一个重要观察:要执行拥有一个或多个父行的周期包含,检查子周期的开始日期和结束日期是否都包含在同一父周期中通常是不够的。这类检查将在单个父周期中执行周期包含,可能会拒绝促销 17。


执行时态 RI 的选项

通常有 4 个选项可用于处理时态 RI:

  1. 无强制执行。如果您知道时态 RI 与您的应用程序不相关,或决不可能违规时,不要定义任何约束。
  2. 在应用程序中强制执行。如果您的应用程序已经执行了时态 RI,这将是一个不错的选择。否则,可能会导致更多的应用程序复杂性,并且可能会增加应用程序开发人员的负担。
  3. 使用触发程序强制执行。为插入、更新和删除操作创建触发程序以强制执行时态 RI。
  4. 使用存储过程强制执行。创建存储过程,使用批量操作对多个行强制执行时态 RI。

在本文其余部分,我们将提供一些针对选项 3 和 4 (触发程序和预存程序)的示例,以在一个子行和一个或多个父行之间强制执行周期包含。代码样例已在 DB2 10 for Linux, UNIX, and Windows® 中测试过了,相同的基础概念也适用于 DB2 for z/OS®。


使用触发程序强制执行时态 RI

要强制执行时态 RI,可以对下列事件的全部或部分创建触发程序:

  • 子表中行的插入。
    • 如果新产品促销没有包含在该产品的业务周期内,就可能违反了时态 RI。触发程序可以检测到该条件,并拒绝促销的插入。
  • 子表中行的更新。
    • 如果一个子行的主键或业务时间周期发生了改变,就可能违反了时态 RI。触发程序可能会根据需要拒绝更新。
  • 删除父表中的行。
    • 如果一个产品已被删除但相关促销信息仍然存在,可能会违反时态 RI。在这种情况下,触发程序可以执行一个您选择的删除规则,比如:
      • 拒绝和回滚删除操作。
      • 级联子表中相关行的删除和移除。
      • 以其他应用程序定义的方式修改子行。
  • 父表中行的更新。
    • 如果父行的主键或业务时间周期发生了改变,就可能违反了时态 RI。在这种情况下,触发程序可能拒绝更新,也可能删除或修改子表中的相关行。

以下小节为子表行的插入和更新提供了样例触发程序。

基础时态 RI 触发程序(没有间隙)

在本小节中,我们假设在父表(product_avail)中任何给定产品的周期决不可能出现间隙。这种假设意味着 图 4 中促销 19 的时态 RI 违规不可能发生,因此对于这一特殊案例触发程序不需要检查。这支持一个简单的触发程序实现。稍后我们将讨论如何扩展触发程序来处理周期间隙。

清单 3 显示当新行插入到 promotion 表时执行的触发程序。触发程序检查新行的时态 RI,也就是说,它将验证新促销行的周期是否包含在同一产品 ID 的一个或多个父行周期中。

触发程序执行两个检查。首先,使用一个匹配的 prodID 值检查插入的促销开始日期是否包含在 product_avail 行的业务周期中。然后,对插入促销的结束日期检查类似条件。如果这两个测试中有一个失败(例如,如果匹配的父行数为 0),那么违反了时态 RI,且出现一个错误。如果开始日期和结束日期都包含在对应父行周期中,则维持时态 RI,插入完成。否则,触发程序将拒绝插入、发出一个自定义 SQL 状态信号,并发出一条违反时态 RI 的消息。

清单 3. 在子行插入上强制执行时态 RI 的触发程序
CREATE TRIGGER promotion_insert_RI
BEFORE INSERT ON promotion
REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    IF (
        -- Is promotion.promo_start part of an existing product_avail period?
        (SELECT COUNT(*) count
        FROM product_avail p
        WHERE p.ProdID = new.ProdID
        AND p.avail_start >= new.promo_start 
        AND p.avail_end < new.promo_start)=0
    OR
        -- Is promotion.promo_end part of an existing product_avail period?
        (SELECT COUNT(*) count
        FROM product_avail p
        WHERE p.ProdID = new.ProdID
        AND p.avail_start > new.promo_end
        AND p.avail_end <= new.promo_end)=0
     ) -- promotion.promo_start AND promotion.promo_end must BOTH be 
    -- part of ANY period
   THEN SIGNAL SQLSTATE 'RI999' 
     SET MESSAGE_TEXT='PROMOTION PERIOD IS NOT FULLY CONTAINED IN EXISTING
                    PRODUCT PERIODS!';
    END IF;
END@

如果我们期望促销的 prodIDpromo_startpromo_end 列得到更新,拥有一个类似的触发程序是很有必要的。例如,promo_startpromo_end 值的更新可能移动或增加促销周期,而且可能会违反时态 RI。触发程序的逻辑与 清单 4 一致,除此之外,触发程序只有在这 3 列中的任何一列受更新语句的影响时才触发。

清单 4. 在子行更新中强制执行时态 RI 的触发程序
CREATE TRIGGER promotion_update_RI
BEFORE UPDATE OF prodID, promo_start, promo_end ON promotion
REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
        -- same trigger body as in Listing 3
END IF;
END@

接下来,我们将讨论如何增强这些触发程序以正确处理父周期中的间隙。


时态 RI 触发程序检查间隙

如果一个给定键值的父行在其业务周期之间有间隙,就像我们的产品可用性示例中那样,那么触发程序中的时态 RI 验证需要更为精细。触发程序也需要被增强来检测 “间隙违规”,比如 图 4 中的促销 19。清单 5清单 6 显示了 2 个编写触发程序的备选方法,编写的触发程序将使用必要的间隙检查来验证周期包含。

清单 5 中的触发程序从 product_avail 表中选择周期与插入促销表的新行的周期重叠的父行。对于其中每个父行,product_avail 表上的一个左外部自连接 (left-outer self-join) 将检索任何 “下一个” 其周期开始于前一个周期结束时的父行 (current.avail_end = next.avail_start)(即,没有间隙)。该左外部连接的结果行在 next.avail_start 列有一个 NULL 值,表示该父行的周期中有一个间隙。触发程序中的其他条件确保新促销的开始日期和结束日期都包含在现有父周期中。

如果 product_avail 表中没有 prodID 值与新插入促销的 prodID 匹配的父行,那么外部连接将生成一个空结果集。在这种情况下,顶层 SELECT 子句中的 SUM(start_check)+SUM(end_and_gap_check) 将产生 NULL。该 NULL 值会导致 COALESCE 函数返回 -1,并在必要时允许完整性测试失败。

清单 5. 在子行插入上强制执行时态 RI,考虑父周期间隙
CREATE OR REPLACE TRIGGER promotion_insert_RI_withGaps
BEFORE INSERT ON promotion
REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    IF ( SELECT COALESCE(SUM(start_check)+SUM(end_and_gap_check), -1) check 
        FROM (
        SELECT CASE WHEN new.promo_start < current.avail_start
                AND current.avail_start = 
                        (SELECT MIN(avail_start) 
                        FROM product_avail
                        WHERE prodID = new.prodID 
                        AND avail_start < new.promo_end
                        AND avail_end > new.promo_start)
                THEN -1-- PROMOTION.PROMO_START is out of product_avail range!
                ELSE  0-- PROMOTION.PROMO_START is in range of current or previous 
                        -- product_avail period
            END start_check,
            CASE WHEN current.avail_end < new.promo_end
                THEN CASE WHEN next.avail_start IS NULL
                          THEN -1-- PROMOTION is out of product_avail range, 
                                -- or product_avail contains gaps!
                          ELSE  0-- PROMOTION.PROMO_END is out of current 
                                -- product_avail period, but a connecting 
                                -- product_avail period exists
                        END ELSE 0 -- PROMOTION.PROMO_END is in a product_avail period
                END end_and_gap_check
            FROM product_avail AS current
            LEFT OUTER JOIN product_avail AS next
                        ON current.avail_end = next.avail_start
                        AND current.prodID = next.prodID
            WHERE current.prodID = new.prodID 
            AND current.avail_start < new.promo_end 
            AND current.avail_end > new.promo_start
        ) )<0 THEN SIGNAL SQLSTATE 'RI999' 
            SET MESSAGE_TEXT='PROMOTION PERIOD VIOLATES TEMPORAL RI!';
    END IF;
END @

理解该触发程序逻辑的最佳方法是绘制一个如 图 4 所示的图像,然后遍历条件和 CASE 表达式。

清单 5 仅仅是一个可能的解决方案,您可以在 SQL 中发现编写相同条件的其他方法。另一个可选方案如 清单 6 所示。清单 6 中的触发程序主体检查 3 个条件。第一个 SELECT 语句使用相同的键值验证插入促销的开始日期是否位于产品行的业务周期之内。第 2 个 SELECT 语句验证新促销的结束日期是否位于匹配产品行的周期内。第 3 个 SELECT 语句检查 product_avail 表中相关父行周期之间的间隙。

清单 6. 在子行插入上强制执行时态 RI,考虑父周期间隙
CREATE OR REPLACE TRIGGER promotion_insert_RI_withGaps
BEFORE INSERT ON promotion
REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    IF ( -- verify that a product row exists with prodID = promtion.prodID
        -- and whose period contains promo_start
        (SELECT COUNT(*)
        FROM product_avail prod
        WHERE new.prodID = prod.prodID
        AND prod.avail_start <= new.promo_start
        AND new.promo_start < prod.avail_end)   < 0 )
    OR ( -- verify that a product row exists with prodID = promotion.prodID 
        -- and whose period contains promo_end
        (SELECT COUNT(*)
        FROM product_avail prod
        WHERE new.prodID = prod.prodID
        AND prod.avail_start < new.promo_end
        AND new.promo_end <= prod.avail_end)   < 0 )
    OR ( -- check for any gaps between the relevant product rows that
        -- have prodID = promotion.prodID
        (SELECT COUNT(*)
        FROM product_avail prod
        WHERE new.prodID = prod.prodID
        AND new.promo_start < prod.avail_end
        AND prod.avail_end < new.promo_end
        AND NOT EXISTS (
                SELECT *
                FROM product_avail prod2
                WHERE prod2.prodID = prod.prodID
                    AND prod2.avail_start <= prod.avail_end
                    AND prod.avail_end < prod2.avail_end) )   > 0 )
    THEN SIGNAL SQLSTATE 'RI999' 
        SET MESSAGE_TEXT='PROMOTION PERIOD VIOLATES TEMPORAL RI!';
    END IF;
END @

使用存储过程强制执行时态 RI

如果您正在一个表中添加、更改或删除许多行,在一个存储过程中使用基于集合的操作来检查时态 RI 可能更为有效,而不是使用一个触发程序,该触发程序对于每个受影响的行只能激活一次。例如,如果执行加载或导入操作,或批量更新或删除时,您可能想要使用一个存储过程来验证时态 RI。

例如,清单 6 中的程序验证 product_avail 表和 promotion 表中的时态 RI。假设 product_avail 表中给定产品的周期不包含间隙。该程序中的核心 RI 逻辑与 清单 3 中触发程序的逻辑非常相似。

创建这类程序时,您可以选择您想要处理时态 RI 的方法。清单 6 中的违规处理采用一个简单方法:如果至少有一个促销行违反时态 RI,该程序将发出一个自定义 SQL 状态信号,并发出一条违反时态 RI 的消息。

清单 7. 存储过程批量检查时态 RI
CREATE PROCEDURE check_temporal_integrity()
BEGIN
    IF (SELECT COUNT(*) FROM (
        SELECT *  -- This SELECT obtains all promotion rows that violate temporal RI 
        FROM (
            SELECT prodID, promo_start, promo_end,
                -- check whether a product row contains promotion.promo_start
                (SELECT COUNT(*) FROM product_avail p
                  WHERE p.ProdID = promotion.ProdID
                    AND p.avail_start <= promotion.promo_start
                    AND p.avail_end > promotion.promo_start) c1,
                -- check whether a product_avail row contains promotion.promo_end
                (SELECT COUNT(*) FROM product_avail p
                   WHERE p.ProdID = promotion.ProdID
                      AND p.avail_start < promotion.promo_end
                      AND p.avail_end >= promotion.promo_end) c2
            FROM promotion) S
    -- count all promotion rows, for which either the first or the second check fails
        WHERE c1 = 0 OR c2 = 0
    ) T) > 0
    THEN SIGNAL SQLSTATE 'RI999' 
        SET MESSAGE_TEXT='AT LEAST ONE PROMOTION VIOLATES TEMPORAL RI!';
    ELSE SIGNAL SQLSTATE 'RI000' 
        SET MESSAGE_TEXT='TEMPORAL RI HAS BEEN SUCCESSFULLY VERIFIED!';
    END IF;
END@

不仅仅是计算违规行数,您也可以返回其主键值,将它们插入到一个异常表中,或者执行任意自定义操作来满足应用程序需求。例如,清单 8 中的程序将违规促销行复制到一个单独的表中,然后将它们从 promotion 表中删除。

清单 8. 存储过程复制和删除违反时态 RI 的行
CREATE PROCEDURE check_temporal_integrity()
BEGIN
    INSERT INTO promo_exception
       SELECT promoID, prodID, price, promo_start, promo_end
       FROM (
            SELECT promoID, prodID, price, promo_start, promo_end,
                -- check whether a product row contains promotion.promo_start
                (SELECT COUNT(*) FROM product_avail p
                  WHERE p.ProdID = promotion.ProdID
                    AND p.avail_start <= promotion.promo_start
                    AND p.avail_end > promotion.promo_start) c1,
                -- check whether a product_avail row contains promotion.promo_end
                (SELECT COUNT(*) FROM product_avail p
                  WHERE p.ProdID = promotion.ProdID
                    AND p.avail_start < promotion.promo_end
                    AND p.avail_end >= promotion.promo_end) c2
                FROM promotion
            ) S
            -- select all promotion rows, for which either the first
            -- or the second check fails
            WHERE c1 = 0 OR c2 = 0;
                
    DELETE FROM promotion
    WHERE promoID IN (SELECT promoID FROM promo_exception);
END@

结束语

本文介绍了时态 RI 的概念,并描述了如何使用触发程序和存储过程来对其进行强制执行。两个应用程序周期时态表之间的时态 RI 由 2 个条件构成。首先,对于子表中的每一行,在父表中有一个或多个拥有相同键值的行与之匹配。其次,任何子行的业务时间周期与相关父行的业务时间周期必须满足一个时态关系。例如,您可能想要强制使子周期等同于父周期,或者子周期包含在一个或多个父周期之内。不同的应用程序场景可能需要执行不同的时态关系。

在应用程序级强制执行时态 RI 并不简单。可以在数据库级别使用触发程序或存储过程来完成。如果您想要编写自己的触发程序或程序进行时态完整性检查的话,本文样例可以作为您的起点。

参考资料

学习

获得产品和技术

  • 使用 IBM 产品评估试用版软件 构建您的下一个开发项目,可直接从 developerWorks 下载。
  • 现在您可以免费使用 DB2。下载 IBM 软件下载:IBM DB2 Express-C 10.1,一个为社区提供的 DB2 Express Edition 免费版本,提供了与 DB2 Express Edition 相同的核心数据功能,并且为构建和部署应用程序提供了坚实基础。

讨论

条评论

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=836928
ArticleTitle=在 DB2 中使用时态一致性管理时间
publish-date=09242012