在 InfoSphere Warehouse 中通过表分区实现在线转出

提高您的数据库数据的可用性

从 IBM® DB2® for Linux®, UNIX®, and Windows® V9.7.1 开始,表分区功能借助对在线转出的支持(使用分离功能)得到增强。有了在线转出 (online roll-out),当使用 ALTER TABLE DETACH PARTITION 命令分离一个或多个表分区时,查询就能够继续访问已分区的表格。本文讨论了在线分离如何提高数据仓库的可用性,并介绍了一些用来利用和监测新行为的最佳实践。还提供了一个已存储的流程来帮助数据库管理员为目标表格上的后分离 (post-detach) 处理编写脚本。本文还演示了如何使用同一个流程来模拟某些应用场景下的同步分离行为。我们在本文的结尾部分介绍了与分离相关的一些常见问题。

Liping Zhang, 高级软件工程师, IBM

Liping Zhang 的照片Liping Zhang 是 IBM 比弗顿实验室的一名高级软件工程师。他一直致力于自版本 9.1 以后的 DB2 for Linux, UNIX, and Windows 中的表分区工作,并领导了自版本 9.7 以后的功能设计工作。他还与客户携手合作,帮助他们了解和采用表分区功能。Liping 曾撰写过 IDUG 演讲稿,还经常撰写关于表分区功能的 DB2 论文、文章和书籍。



Steve Pearson, 高级软件工程师, IBM

Steve PearsonSteve Pearson 是 IBM 比弗顿实验室的一名高级软件工程师。他从事 DB2 for Linux, UNIX, and Windows 方面的工作已有九年的时间,主要领导关于高可用性灾难恢复 (HADR) 功能的工作。去年,他开始从事 DB2 表分区功能方面的工作。



Srinivasa Santhanam, 软件开发人员, Consultant

Srinivasa SanthanamSrinivasa Santhanam 是一位软件开发人员,过去三年一直在比弗顿实验室致力于 DB2 for Linux, UNIX, and Windows 的工作,主要从事表分区功能方面的工作。



2012 年 2 月 27 日

免费下载:IBM® InfoSphere Warehouse 9.7 Enterprise Edition 试用版
下载更多的 IBM 软件试用版,并加入 IBM 软件下载与技术交流群组,参与在线交流。

简介

DB2 通过表分区功能使用 ALTER TABLE DETACH PARTITION 支持高效的数据转出,能够将大量数据从源表以分区为单位分离出来。

图 1 展示了源表上存在全局(未分区)索引和本地(已分区)索引时的转出。被分离的源表分区成为独立的未分区表。对于本地索引,与被分离分区相关的索引对象是附带的,内部的索引会成为新的目标表格的索引。另一方面,目标表格没有继承全局索引。相反,已分离分区的索引条目是通过分离命令被隔离的。然后,DB2 自动运行一个称为 Asynchronous Index Cleanup(AIC,异步索引清理)的独立的后台任务,将孤立的密钥从全局索引删除。同时,访问源表的查询会忽略此类密钥。

图 1. 从包含全局和本地索引的表中转出
该图展示了如何将一个分区从包含全局和本地索引的表中分离出来

在 V9.7.1 之前,通过表分区转出数据是一种离线操作。需等待所有已分区表的访问结束后才能执行分离操作,同时还要阻止启动新的访问。然而,分离可能需要与长期运行的报告查询同时执行,这可能造成 DBA 执行的管理操作与表的查询访问之间产生竞争,该表可作为应用程序工作负载的一部分。

从 DB2 V9.7.1 开始,DETACH 操作对于动态 SQL 而言是一种在线操作,它允许在查询并行访问该表的其余分区时将某个分区隔离。事实上,使用 Uncommitted Read (UR) 隔离级别来报告工作负载可以读取被分离的分区,这样做甚至不会阻止并发分离操作的进行。

此操作是通过将 DETACH 分成两阶段操来实现的。第一个阶段由 ALTER TABLE DETACH PARTITION 命令执行,负责从逻辑上将分区从表分离开。第二个阶段由后台的系统管理,无需用户干预,负责确保完成所有访问已分区表的现有活动之后,异步完成分离操作。后台进程也称为 Asynchronous Partition Detach(APD,异步分区分离)进程。在不依赖物化查询表 (MQT) 的情况下,系统会在完成第一阶段的提交之后开始第二阶段。如果已分区表上存在临时表或立即刷新 MQT 等依赖关系表,那么在通过 SET INTEGRITY 刷新这些依赖关系表之后,就会自动启动第二阶段。

从 DBA 的角度来看,转出操作在出现长期运行的报告工作负载时会继续进行。从应用程序的角度来看,长期运行的报告工作负载没有被分离中断,且能并行完成。对于成功完成 DETACH 命令后的已分区表的访问,已分离的分区不再可见,异步完成分离操作不会中断新的访问。当然,新的在线转出行为遵守有效的隔离级别。例如,如果可重复读取 (RR) 扫描仪已经读取了该分区,但是尚未提交,那么分离操作将等待 RR 扫描仪完成,然后才能使该分区可见。在异步完成分离操作的第二阶段后,可对分离的目标表进行归档、丢弃它或将它附加到另一个表。

异步分离行为是 DB2 V9.7.1 中的新特性,也是其中的惟一选择。可以使用以下方法对其进行监视:

  • 发出 LIST UTILITIES SHOW DETAIL 命令。
  • 查询 SYSCAT.DATAPARTITION 目录视图的 STATUS 列,了解所涉及的分区。查看附录了解实现该方法的已存储流程。

DETACH 阶段

下表说明了与分离操作相关的活动的顺序。

表 1. 与 DETACH相关的活动的顺序
顺序描述示例
1 用户发起的 DETACH
DETACH 分区的第一阶段。请注意,您需要提交该命令,这样,第二阶段才能开始。通常,在完成任何 DDL 后立即提交该命令是一个好的做法。
ALTER TABLE purchaseOrders DETACH PARTITION jan2010 INTO obsolete_data;
COMMIT;
2 用户发起的
如果源表已经定义了 REFRESH IMMEDIATE MQT,则运行 SET INTEGRITY 来刷新它们。
SET INTEGRITY FOR q1_sales_mqt IMMEDIATE CHECKED;
COMMIT;
3 系统发起的,DETACH
异步分区分离的第二阶段;无需用户干预。
4 系统发起的
如果存在任何全局索引,那么异步索引清理 (AIC) 将由 DB2 自动启动,将索引条目从已分离的分区中删除。

图 2 说明了整个周期中允许在表上执行的两阶段分离行为和查询活动。

在第一阶段,从源表逻辑分离分区。目录和其他元数据进行了更新,以反映这一情况,但是并未从源表中对表进行物理删除。使用 UR 隔离的动态查询可能继续访问源表的所有分区,包括已分离的分区,而其他隔离级别的动态查询则可以访问源表的所有其余分区。在分离提交后,新的查询将不会再看到被分离的分区。

在所有查询开始之后、完成分离之前,且用户已经刷新了所有依赖关系 MQT 之后,第二阶段就开始了。在这个阶段,已分离的分区通过 APD 任务从源表中物理去除链接,成为单独的未分区表。

图 2. 在此期间的由两个阶段组成的分离和查询活动
该图说明了(同步逻辑和异步物理)分离的阶段

SQL waitForDetach 流程

尽管 V9.7.1 提高了转出操作过程中已分区表的可用性,但是分离的目标表直到已分离分区的 APD 完成时才可用。本文末尾的附录中提供了一个 SQL PL 流程示例 (waitForDetach),该示例中提供了为目标表编写后分离处理脚本的一种便捷方法,如下所述。该流程可以查询 SYSCAT.DATAPARTITIONS 目录视图的 STATUS 列中的分区状态,并且直到该分区的 APD 完成后才返回。

连接到 waitForDetach 的接口如下所示。

清单 1. waitForDetach
procedure waitForDetach (OUT returnCode integer,
                         OUT msg varchar(128),
                         IN schemaName varchar(128),
                         IN tableName varchar(128),
                         IN dataPartID int DEFAULT -1)

-- Output:
--   returnCode -  0 indicates detach completed successfully;
--                -1 indicates an error
--   msg        -  Error details

-- Input:
--   schemaName - Table schema
--   tableName  - Table name
--   dataPartID - (optional) dpid of partition to wait for;
--                by default, wait for all detach operations on the table
--                to complete.

本节的其余部分讨论了可以使用 waitForDetach 的场景。

访问分离的目标表

在执行分离操作后,且在访问目标表之前,必须完成 APD 操作;否则,将遇到带有原因代码 2 的错误 SQL20285N。

场景 1:将丢弃分离目标表或对其进行归档

由于分离的目标表只有在分离操作完成后才可用,因此人们可能希望将等待分离完成后再丢弃已分离的目标表或对其归档的操作自动化。ALTER TABLE DETACH PARTITION 命令可以启动转出流程。在提交该命令后,一旦完全完成分离操作,就会返回对 waitForDetach 的后续调用。因此,它将等待进行分离时范围已分区表上的现有查询全部完成,并等待到达异步分区分离阶段。然后就可以丢弃已分离的目标表或对其进行分档。

示例: 在下面的示例中,数据仓库的 DBA 每个月分离一个分区,已分区表的可用性对于数据仓库来说非常重要。首先要对已分离的目标表进行归档,然后才能丢弃它们。

清单 2. 数据仓库的 DBA 分离了一个分区
ALTER TABLE sales DETACH PARTITION jan2009 INTO jan2009_old;
COMMIT;

CALL waitForDetach (?, ?, 'DWSCHEMA', 'SALES');
BACKUP DB DWDB TABLESPACE (jan2009_tbsp) to /archpath;
DROP TABLE jan2009_old;
COMMIT;

场景 2:分离的目标表将被重新合并到原来的分区表中

要在某个分区级(例如 TRUNCATE)执行某些操作,DBA 可能会分离一个分区,在分离的目标表上执行相应操作,然后将该分区重新合并到原来的分区表中。在这种情况下,TRUNCATE 脚本需要调用 waitForDetach 完成分离,然后才能在分离的目标表上调用 TRUNCATE。在分离的第二个阶段,会像往常一样等待现有的查询完成,尽管在没有此类查询的窗口中可能出现此类维护,并且 APD 可以快速完成。

示例: 在下面的示例中,数据仓库的 DBA 希望截断分区。由于 TRUNCATE 命令在表级运行,因此,通过分离分区、截断目标表、将该分区重新合并到原来的已分区表中,DBA 可以实现分区级 TRUNCATE。在本示例中,DBA 首先会查询目录,确定要截断的分区的分区标识符,然后将该标识符用于对 waitForDetach 的调用,以等待获得特定的分区。如果其他分区最近也被分离,那么这样使用 waitForDetach 是有利的。

清单 3. DBA 会查询目录,确定要截断的分区的分区标识符
select DATAPARTITIONID from syscat.datapartitions where tabschema 
= 'DWSCHEMA' and tabname = 'CLAIMS' and DATAPARTITIONNAME = 'YEAR1';
-- assume partition id "3" was returned

ALTER TABLE claims DETACH PARTITION year1 INTO year1_table;
COMMIT;

-- wait for the above detach to complete
CALL waitForDetach (?, ?, 'DWSCHEMA', 'CLAIMS', 3);

TRUNCATE TABLE year1_table IMMEDIATE;

ALTER TABLE claims ATTACH PARTITION year1 STARTING FROM 200101 
ENDING AT 200112 FROM year1_table;

SET INTEGRITY FOR claims IMMEDIATE CHECKED;

访问分离的源表

场景:针对已按范围进行分区的表的 DDL 访问

在执行分离操作后,应立即在独立的事务中访问已按范围进行分区的表的 DDL(包括后续的 ADDATTACH 操作),这可能会干扰异步分离的完成,它本身类似于 DDL 操作。在这些情况下,应该等待分离操作完全完成,然后在已按范围进行分区的表上尝试下一个 DDL,这样做更有利一些。

示例: 在下面的示例中,数据仓库的 DBA 分离一个旧分区,然后将一个新的空分区添加到同一个表中。

清单 4. 数据仓库的 DBA 分离一个旧分区,并添加一个空分区
ALTER TABLE claims DETACH PARTITION year1 INTO year1_table;
COMMIT;

-- wait for the above detach to complete
CALL waitForDetach (?, ?, 'DWSCHEMA', 'CLAIMS');

ALTER TABLE claims ADD PARTITION year12 STARTING 201201 ENDING 201212;

常见问题

本节讨论与使用 ALTER TABLE DETACH PARTITION 将数据从数据库转出相关的一些常见问题。

当分离分区及其索引后,它们去了哪里?
分离是一种无数据移动的操作。被分离的分区的表数据和本地索引不会发生物理移动,而是继续驻留在原来的表空间中。只是修改了 ID 信息以及已分离分区与源分区表的逻辑关系,导致系统目录和其他对象元数据发生一些变化。全局索引与源表依然保留,所有与被分离的分区相关的密钥会通过 AIC 任务从分区表中删除。

目标表上的索引将使用哪个表空间?
当分区作为源表的一部分时,指定的索引表空间会继续用作目标表上的索引的索引表空间。如果没有为该分区指定索引表空间,那么索引表空间与分区数据的表空间会是同一个。

目标表将继承哪些索引?
目标表会继承所有在源表上定义的已分区(本地)索引。这包括用于主要或特殊密钥限制(如 XML 支持)的索引,以及用于 MDC 表支持的系统生成的索引。在无分区(全局)索引中,目标表只会继承 MDC 块索引(如果 MDC 表在 V97 FP1 之前创建,则是未分区的索引)和 XML 列路径索引,不会继承其他未分区的索引。

目标表会继承源表上的限制吗?
不。源表上的限制在 DETACH 后不会被目标表继承。人们应该能够在必要时使用 ALTER TABLE 命令添加回类似的限制。如果已分区索引在分离之前进行了某些限制,那么 ALTER TABLE 命令将重用已继承的索引,避免花费时间创造新索引。

我需要在执行 DETACH 之后在源表或目标表上运行 SET INTEGRITY 吗?
不,您不需要在 DETACH 之后在源表或目标表上运行 SET INTEGRITY。这是一个常见误解。如果表上有已定义的立即刷新的 MQT,那么在分离后,这些 MQT 会处于已设置好的完整性暂挂状态。您需要使用 SET INTEGRITY 来刷新这些 MQT,使其处于完全访问状态。

我如何知道是否已成功地分离了分区?
有多个选项:

  1. 查询目录视图 SYSCAT.DATAPARTITIONS,检查该分区是否仍是源表的一部分。
  2. 使用 LIST UTILITIES SHOW DETAIL 查看异步分区分离任务活动是否已经完成。
  3. 使用本文提供的已存储流程,它实际上基于第一种方法。

我能将多个分区从同一事务中的同一个表中分离吗?
可以,但是最佳实践是一次只分离一个分区,并立即提交,然后等待 APD 任务完成再发出下一个 DETACH 命令。这样做是为了避免多个 APD 引起的潜在冲突,每次只执行一个 DETACH 命令,该操作可能会争夺系统目录锁定和其他资源。此类冲突的结果是只有一个获胜者可以继续,其他的都将被放回等待队列,直到下一个尝试周期,这样做所花费的时间最终会比以下做法长。

在执行 DETACH 命令后会继承分区上的状态吗?
来自源表的某些表和索引统计在分离操作过程中将被继承,但并非全部都会被继承。我们建议用户在执行分离操作后对源表执行 RUNSTATS。如果分离的目标表在未来某个时间将被保留和查询,那么我们建议对该表也运行 RUNSTATS

当所涉及的表上存在参考限制关系时,对于 DETACH 是否有任何限制?
是的。不允许在 RI 关系中将分区从母表中分离。人们可以丢弃特定的限制或将该限制从执行转换成信息限制,以绕开此限制。然而,在执行此类变通方法时需谨慎,切勿损坏数据的完整性。

DETACH 过程中是否会触发删除条件?
不会。

是否有选项强制执行 DETACH 行为(也就是说,执行与 DB2 V9.7 FP1 之前的版本相同的完全同步分离行为)?
没有。DB2 不支持此类选项。然而,人们可以使用本文提供的已存储流程 (waitForDetach) 或编写自己的脚本来序列化 DETACH 和涉及目标表的后续操作。

在分区被分离多长时间之后我才能重用该分区名称?
在返回 DETACH 命令之后,便可在同一个工作单元中立即重用该分区名称,这会在内部根据当前的时间 (SQLyymmddhhmmssxxx) 将分区重命名为系统生成的名称。如果来自不同工作单元的 SQL 命令需要重用分区名称,那么该命令必须等待提交在其中发送 DETACH 的工作单元。

我在数据库中使用已分区 MQT。我可以从 MQT 分离分区吗?
可以。目标表会是一个普通表。否则,DETACH 行为将与非 MQT 表相同。


结束语

本文介绍了 ALTER TABLE DETACH PARTITION 语句的并发改进。自 DB2 V9.7.1 起,DETACH 允许通过表分区功能并行地运行报告查询与转出操作,从而在实现业务数据保留策略时无需停机。我们还讨论了可能需要进行同步的异步分离的场景示例,还提供了用于处理此类同步的代码示例。


附录:waitForDetach 流程

下面是用于存储流程的代码示例以及相关的辅助函数,可用该函数来确定 DETACH 操作何时全部完成,以及何时可以访问目标表。请注意,此代码仅用于说明目的。

在下面的代码示例中,CLP 的语句终止字符被假定为 %

清单 5. waitForDetach 流程代码示例
-- Command to run as a script, assuming code is in a file named 'waitForDetach.clp'
-- would be: db2 -td% -vf waitForDetach.clp

-- Function to check if a table exists in the database.
--
-- Input:
--   schemaName - Table schema
--   tableName  - Table name
--
-- Returns:       1 if table is found, 0 if table not found
--
create function tableExists (schemaName varchar(128),
                             tableName varchar(128))
RETURNS INT
RETURN
  case
    when exists (
      select DATAPARTITIONID from syscat.datapartitions
        where tabschema = schemaName and tabname = tableName
        WITH UR
       )
          then 1
          else 0
END%



-- Procedure to wait for detach of a single partition or all
-- partitions to complete
--
-- Input:
--   schemaName - Table schema
--   tableName  - Table name
--   dataPartID - (optional) dpid of partition to wait for;
--                by default, wait for all detach operations on the table
--                to complete.
--
-- Output:
--   returnCode -  0 indicates detach completed successfully;
--                -1 indicates an error
--   msg        -  Error details
--
create procedure waitForDetach (OUT returnCode integer,
                                OUT msg varchar(128),
                                IN schemaName varchar(128),
                                IN tableName varchar(128),
                                IN dataPartID int DEFAULT -1)
  specific waitForDetach
  language SQL
BEGIN

  declare dpid int;
  declare dpstate char;
  declare done boolean default FALSE;
  declare tabNotFound boolean default FALSE;
  declare curClosed boolean default FALSE;
  declare oldLockTimeout INTEGER;

  declare allDetachCheck cursor for
      select DATAPARTITIONID, STATUS from syscat.datapartitions
        where tabschema = schemaName and tabname = tableName and
        (status = 'L' OR status = 'D') WITH CS;

  declare oneDetachCheck cursor for
      select DATAPARTITIONID, STATUS from syscat.datapartitions
        where tabschema = schemaName and tabname = tableName and
        DATAPARTITIONID = dataPartID WITH CS;

  -- Add signal handlers for errors that you consider non-fatal here
  -- example for no row found and lock timeout shown below
  declare continue handler for NOT FOUND
  BEGIN
    -- If no record was found there is nothing to wait for, so we are done
    set done = TRUE;
  END;

  declare continue handler for SQLSTATE '40001'
  BEGIN
    -- on a lock timeout we are not done, we want to loop again
    set done = FALSE;
    -- the cursor will be closed as part of the rollback during the lock timeout
    set curClosed = TRUE;
  END;

  -- initialize return code to 0
  set returnCode = 0;

  -- save current lock timeout value
  values current lock timeout into oldLockTimeout;
  set current lock timeout -1;

  -- if table does not exist in syscat.datapartitions, return error
  IF tableExists (schemaName, tableName) = 0
  THEN
    set msg = 'Table ' || schemaName || '.' || tableName || ' not found';
    set returnCode = -1;
    GOTO exit;
  END IF;

  WHILE done = FALSE DO
    set curClosed = FALSE;

    IF dataPartID <> -1
    THEN
      open oneDetachCheck;
      fetch oneDetachCheck into dpid, dpstate;

      -- two cases here:
      --  (i) detach has already completed hence partition entry not
      --      found in catalogs (indicated by done = TRUE, handled later)
      -- (ii) detach in progress, partition state should not be visible
      IF done <> TRUE AND (dpstate = '' OR dpstate = 'A')
      THEN
        set msg = 'Cannot waitForDetach if DETACH was not issued on ' ||
                  'DATAPARTITIONID ' || dataPartID;
        set returnCode = -1;
        GOTO exit;
      END IF;

      IF curClosed <> TRUE
      THEN
        close oneDetachCheck;
      END IF;
    ELSE
      open allDetachCheck;
      fetch allDetachCheck into dpid, dpstate;
      IF curClosed <> TRUE
      THEN
        close allDetachCheck;
      END IF;
    END IF;

  END WHILE;

  IF dataPartID <> -1
  THEN
    set msg = 'DETACH completed on DATAPARTITIONID ' || dataPartID;
  ELSE
    set msg = 'All detach operations completed on table ' ||
               schemaName || '.' || tableName;
  END IF;

exit:
  -- restore lock timeout value
  set current lock timeout oldLockTimeout;

  RETURN returnCode;

END%

参考资料

学习

获得产品和技术

讨论

条评论

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=795809
ArticleTitle=在 InfoSphere Warehouse 中通过表分区实现在线转出
publish-date=02272012