DB2 pureXML 技术在公共安全、医疗卫生等应用领域中有着的独特优势,并得到了广泛应用。而这些行业的应用往往拥有海量的数据,对 XML 数据存储与处理的并行能力和可伸缩性提出了特别要求。IBM 契合这种需求,在 DB2 V9.7 中提供了 DPF 环境对 pureXML 的完全支持。
简介
DB2 V9.7 中对 pureXML 特性有很多增强,其中一个非常重要的特性是拓展了 DB2 数据库分区功能(Database Partitioning Feature,DPF),使得 XML 数据类型在 DPF 得到完全支持。这一特性简化了 XML 数据管理并提供出色的可伸缩性,让 DB2 V9.7 支持跨多个数据库分区分布 XML 数据,允许自动的并行执行增删改查等操作。
本文将介绍 DB2 V9.7 的 DPF 环境中对 pureXML 的支持,包括如何配置支持 XML 的 DPF 环境,展示对 DDL 原有限制的突破,XML 数据如何分布存储,如何用 XQuery 和 SQL/XML 语言查询 XML 数据,如何用全局临时表来提高 DPF 环境下的 XML 数据转换的性能,以及一些常见的陷阱和解决办法。通过阅读本文读者可以较为深入的理解这一新特性,并能够在实际应用中使用。
支持 DPF 和 pureXML 的体系结构
DPF 是 DB2 支持大型数据库(特别是数据仓库)的一个重要特征。它使得你能将数据分散的存储到多个数据库分区,而每个分区都可以是独立的物理机器,能够作为一个相对独立的逻辑节点来存储和处理数据,从而使得整个数据库实例能够并行处理数据存储和查询。因此,DPF 使得系统能够充分利用多个处理器和存储设备的并发 CPU、I/O 等资源,并提供出色的可伸缩性。文章“DB2 9.5 数据库分区管理及应用实践”对了解 DPF 是一个很好的参考。
而 pureXML 在 DB2 内部以原有的层次化格式存储 XML 数据,并通过符合行业标准的 XQuery 和 SQL/XML 语言直接访问 XML。
DB2 V9.7 提供了 DPF 对 pureXML 的支持,在 DPF 中使用 XML 的体系结构如图 1,对含有 XML 列的表,根据表的分区键的 Hash 值,将表的关系数据和 XML 数据均匀的分布到各数据库分区。当查询到来的时候,各分区能够并行的执行 XML 的查询和处理。
图 1.基于 Hash 的 DPF 和 pureXML 的体系结构
下面,让我们通过实例一步一步来探讨 DB2 V9.7 DPF 环境对 pureXML 的完全支持吧。
准备工作
首先需要搭建数据库分区环境,本文中,测试平台是 LinuxAMD,在同一个物理机器上设置 4 个分区节点的 DPF 环境,DB2 节点的配置文件 db2nodes.cfg 如下:
清单 1. DPF 分区节点配置
0 crookes 0
1 crookes 2
3 crookes 3
4 crookes 4
读者可以参考 DB2 信息中心“Setting up partitioned database environments”部分了解 DPF 环境的配置,在实际应用中,可将分区节点配置在不同的物理机器上,以达到更好的并发性能。
然后创建数据库 XMLPDF,设置数据库分区 2 作为连接分区。这样在整个实验过程,都连接分区 2 作为协调程序分区,而不是连接目录(Catalog)分区,以此来说明在 DB2 V9.7 的 DPF 环境中,我们可以连接任何分区来处理 XML。
清单 2. 创建数据库和设置连接分区
CREATE DATABASE XMLDPF
DB20000I The CTEATE DATABASE command completed successfully.
SET CLIENT CONNECT_DBPARTITIONNUM 2
DB20000I The CTEATE DATABASE command completed successfully.
然后在数据库上创建数据库分区群 group4n,它包含 4 个全部的分区,并在 group4n 上创建表空间 xml_tbsp4n。于是,创建在 xml_tbsp4n 这个表空间的表的数据将分布到这四个分区上。另外,在 group4n 上创建了一个用户临时表空间 tmp_tbsp4n,这是使用全局临时表时必需的表空间,我们将在后面阐述如何用全局临时表来提高 XML 转换的性能时用到这个表空间。
清单 3. 创建分区群和表空间
CREATE DATABASE PARTITION GROUP group4n on DBPARITIONNUM(0,1,2,3)
DB20000I The SQL command completed successfully.
CREATE TABLESPACE xml_tbsp4n IN group4n
DB20000I The SQL command completed successfully.
CREATE USER TEMPORARY TABLESPACE tmp_tbsp4n in group4n;
DB20000I The SQL command completed successfully.
突破 DDL 对 DPF 中 pureXML 的限制
在开始介绍 DB2 V9.7 的 DPF 对 pureXML 的完全支持之前,有必要先简单的介绍一下 DB2 V9.7 以前 DPF 环境对 pureXML 的限制。
在 DB2 9.1 中,DPF 环境是不支持 pureXML 的,也就是说如果你部署了数据库分区环境,那么你就不能用 pureXML。DB2 9.5 的 DPF 对 pureXML 提供有限的支持,DB2 V9.7 突破了原有限制,在 DPF 环境中提供对 pureXML 的完全支持。 DB2 9.5 与 DB2 V9.7 对 DPF 下 pureXML 支持的比较如下表 1 所示:
表 1.DB2 9.5与 DB2 V9.7对 DPF下 pureXML支持的比较
特征 DB2 V9.5 DB2 V9.7 pureXML 在 DPF 中是否默认支持 默认是不支持的,须打开数据库实例级别的变量 DB2_ALLOW_PUREXML_IN_DPF 默认支持,不用再设置注册变量 XML 表结构的修改
XML 列的表结构不能修改,如执行修改操作会返回一个 SQL1242N 的错误
支持增加列或删除列,修改列的数据类型、是否能为空 (nullability) 的属性等 XML 数据的存储 所有的 XML 数据(即所有包含 XML 列的表)都必须存储在目录分区 XML 数据能分布到任意分区,包括目录分区和非目录分区 XML 数据的查询和处理
协调程序分区 (coordinator) 必须是目录分区,所有 XML 数据的查询和处理都必须在目录分区上进行 任意分区都可以做协调程序分区,XML 数据的查询和处理可以在任意分区上进行
下面通过一些例子来阐述 DB2 V9.7 的 DPF 环境对支持 pureXML 的 DDL 的突破。
在前面创建的表空间 xml_tbsp4n 上创建一个包含 XML 列的表 xmltb,并按整型关系数据列 id 的 hash 值将数据分散到表空间所在的四个分区上,然后对表 xmltb 做各种 DDL 操作。从清单中可以看到,对表结构的各种修改都是支持的,如删除表中的列,修改列的数据类型,修改列是否为空的属性,而且能够增加 XML 列。这些操作在 DB2 9.5 中都不支持,会返回一个 SQL1242N 的错误。也可以看到,在 XML 列上可以正确的创建基于 XML 模式的索引。
顺便解释一下示例代码中的多个重组 (reorg) 操作:在一个表上,最多能只能连续执行3个修改表结构的操作,然后就需要重组整个表使得表中的数据行与表的结构相匹配。
清单 4. 支持的 DDL
create table xmltb(id int, name char(10), doc xml) distribute by hash(id) in xml_tbsp4n
DB20000I The SQL command completed successfully.
alter table xmltb drop column name
DB20000I The SQL command completed successfully.
alter table xmltb alter column id set data type bigint
DB20000I The SQL command completed successfully
reorg table xmltb
DB20000I The REORG command completed successfully.
alter table xmltb alter column id set not null
DB20000I The SQL command completed successfully.
alter table xmltb alter column doc set not null
DB20000I The SQL command completed successfully.
reorg table xmltb
DB20000I The REORG command completed successfully.
alter table xmltb add column info xml
DB20000I The SQL command completed successfully.
create index idx1 on xmltb(info) GENERATE KEY USING XMLPATTERN
'/customer/name' as SQL varchar(20)
DB20000I The SQL command completed successfully.
然而,与 XML 相关的 DDL 还是有一些限制,尽管在实际应用中很少会触及这些限制,当然也应该避免这些限制。从下面清单 5 中可以清楚的看到这些限制,包括:
不允许将 XML 列修改为其他数据类型,如 CLOB。
不允许删除含有两个或多个 XML 列表结构中的一个 XML 列。要删除 XML 列的话必须将表结构中所有的 XML 列一起删除。
不支持在 XML 列上定义主键、唯一性索引和唯一性约束。由于分区键 (partition key) 有全局唯一性要求,因此在 XML 列或者定义在 XML 列上的 XML 模式不能做为分区键。示例中的只含有 XML 列的表 onlyxml 是不能在 xml_tbsp4n 上创建,因为它找不到合适的分区键。DPF 环境对唯一性有特殊的要求,关于这点,有兴趣的读者可以进行进一步的探讨。
清单 5. 不支持的 DDL
alter table xmltb alter column info set data type CLOB
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0190N ALTER TABLE "YMLI.XMLTB" specified attributes for column "INFO" that
are not compatible with the existing column. SQLSTATE=42837
alter table xmltb drop column info
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1242N An XML feature is not supported in the context where it is used.
Reason code = "7". SQLSTATE=42997
alter table xmltb drop column doc drop column info
DB20000I The SQL command completed successfully.
drop table xmltb
DB20000I The SQL command completed successfully.
create table onlyxml(doc xml) in xml_tbsp4n
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0262N Table "ONLYXML" cannot be created in the database partition group
"NODESGROUP1" because no column exists that can be used as the partitioning
key. SQLSTATE=428A2
DPF 中 XML 数据的分布和存储
如前所述,DPF 中是根据分区键的 Hash 值,将 XML 数据均匀的分布到各个分区存储的。
下面的示例先在 DPF 下含有 XML 列的表 books,定义分区键为整型关系列 bid,按 bid 的 hash 值将数据分布到各个分区。在 XML 列上建立索引 idx2,将 XML 文档中书的名字建立为 SQL varchar 类型的索引。然后插入 XML 的样本数据,最后通过一个函数 DBPARTITIONNUM 来查看 XML 数据到底分布到那个分区上。
清单 6. XML 的数据插入和分布
create table books(bid int, info xml)distribute by hash(bid) in xml_tbsp4n
DB20000I The SQL command completed successfully.
create index idx2 on books(info) GENERATE KEY USING XMLPATTERN
'/book/name' as SQL varchar(20)
DB20000I The SQL command completed successfully.
insert into books values(201, 'J2EE Programming23.5
180RichardJim')
DB20000I The SQL command completed successfully.
insert into books values(202, 'The Road45.6
120McCarthy')
DB20000I The SQL command completed successfully.
insert into books values(203, 'Chinese Cooking10.17
140Teresa')
DB20000I The SQL command completed successfully.
insert into books values(204, 'Flying30.28
190Jim')
DB20000I The SQL command completed successfully.
insert into books values(205, 'Network Programming27.5
150RichardTeresa')
DB20000I The SQL command completed successfully.
select bid, dbpartitionnum(info) partition from books
BID PARTITION
----------- -----------
205 0
201 3
202 3
203 1
204 2
5 record(s) selected.
可以看到,各行的数据是根据 bid 的 hash 值比较均匀的分布到各个分区。值得一提的是,在 XML 列上的索引也分布在各个数据库分区上,对那个分区的 XML 数据建立索引。
SQL/XML 和 XQUERY 支持
XML 的数据是通过 SQL/XML 和 XQUERY 来查询和处理的。
在 DB2 V9.7 中 ,以前在单分区模式下支持的 SQL/XML 发布函数在 DPF 环境中得到了完全的支持,因此你可以随心所欲在 XML 表上用 SQL/XML 的函数来处理 XML 数据。下面用两个例子来演示一下 SQL/XML 的几个重要函数 XMLTABLE、XMLQUERY 和 XMLEXISTS 的使用。前一条 SQL 抽取 XML 列中的书的名字,库存数量和第一作者以 XMLTABLE 构建关系数据,后一条 SQL 语句以 XMLEXISTS 为条件将价格大于 30 的书的书名通过一个 XMLQUERY 查询返回。
清单 7. SQL/XML 查询示例
select t.* from books, xmltable('$INFO/book' columns
name varchar(20) path './name',
quantity integer path './quantity',
first_author varchar(20) path './author[1]' ) as t
NAME QUANTITY FIRST_AUTHOR
-------------------- ----------- --------------------
Network Programming 150 Richard
Flying 190 Jim
J2EE Programming 180 Richard
The Road 120 McCarthy
Chinese Cooking 140 Teresa
5 record(s) selected.
select bid, xmlcast(xmlquery('$INFO/book/name') as varchar(20)) as bookname
from books where xmlexists('$INFO/book[price > 30]')
BID BOOKNAME
----------- --------------------
204 Flying
202 The Road
2 record(s) selected.
同样,在 DB2 V9.7 中,以前在单分区模式下支持的 XQuery 的功能(包括 XQuery Update 的功能 ) 在 DPF 中也得到完全支持。如下清单 7 所示,第一条 XQuery 查询将价格大于 30 的书的书名通过一个 XQuery 查询返回,第二条语句以一个转换 (transform) 语句演示了对 XML 的更新操作,将 bid 为 201 的书的数量更新为 280,最后一条查询验证了更新的结果。
清单 8. XQuery 查询和转换
xquery for $book in db2-fn:xmlcolumn('BOOKS.INFO')/book
where $book/price>30 return $book/name
1
------------------------------------------------------------
The Road
Flying
2 record(s) selected.
update books set info = xmlquery(
'transform copy $new := $INFO
modify do replace value of $new/book/quantity with 280
return $new ') where bid =201
DB20000I The SQL command completed successfully.
select bid, xmlquery('$INFO/book/quantity/data(.)') as quantity
from books where bid =201
BID QUNATITY
----------- -----------------------------------------------
201 280
执行计划中的新操作符 XTQ
先介绍一下为支持 DPF 环境下 XML 处理而引入的新概念全局序列 (Global sequence) 和引用模型(reference model)。全局序列是指包含 XML 节点引用的序列,而这些被引用的 XML 节点可能分布在其他分区上。引用模型是指,只有原子值、节点引用和序列可以通过表队列(Table Queue, TQ) 来传输,而如果要获得节点引用的 XML 数据,则需调用 RPC 来解引用。
在 DB2 V9.7 中,执行计划引入了一个新的操作符 XTQ (XML AGG TQ) 支持 DPF 下的 XML 处理。XTQ 是一个特殊的 TQ,它的作用有:1)将全局序列的每个项 (item) 路由到它原来所属的分区上做 XML 导航( navigation);2) 将导航的结果聚集返回到输出序列中。
下面通过一个例子来阐述 XTQ。前面定义的表 books 是定义在多个分区上的表,info 是它的 XML 列。现在考虑这样一个查询,当书的总数量小于 1000 的时候,返回所有书的信息。如下所示,打印出这个查询的执行计划。我们用说明工具(Explain Tool)db2expln 来看一下 DB2 优化器为这个语句生成的执行计划,使用命令以及部分结果如清单 9 所示。
清单 9. 用 db2expln 查看 DPF 下处理 XML 的执行计划
db2expln -d xmldb -statement "xquery let \$books := db2-fn:xmlcolumn('BOOKS.INFO')
where sum(\$books//quantity)
在 expln.out 的执行计划中,可以看到有一个 XTQ(8) 操作符,我们结合 XQuery 查询语句来仔细读这个查询计划: 所有对 BOOKS.DETAIL 上 XML 文档的引用,都通过一个 DTQ(6)传递到协调程序分区,并聚集成一个全局序列 $books。然后,全局序列中的每一项(即每个 XML 节点引用)通过 XTQ(8)路由到它原来的分区上,并在各分区上根据 XPath 路径表达式 $books//quantity 做 XML 导航 XSCAN(9),然后将 导航的结果再通过 XTQ(8)返回到协调程序分区,聚集成一个输出结果集 $books/book/quantity。在 FILTER(2)通过 fn:sum(...) --setup the EXPLAIN tables.
CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN','C',NULL,CURRENT SCHEMA);
explain plan for xquery 'let $books := db2-fn:xmlcolumn(''BOOKS.INFO'')
where sum($books/book/quantity)
提高 DPF 下 XML 转换的性能
XML 转换是 pureXML 技术的一个重要方面,在实际应用中也几乎必不可少。在前面的例子中可以看到,XML 转换 (TRANSFORM. ) 的语法在 DPF 中得到了支持,这确实很方便。然而,并不是所有的 XML 转换语句在 DPF 都能很好的并行。有些复杂的转换语句尽管可以工作,但是 DB2 会返回一个 SQL0473W 的警告信息,告诉用户这个查询可能是次优的,包含不能在 DPF 环境中并行执行的 XQuery 转换表达式。DB2 V9.7 中,全局临时表 DGTT(Declare Global Temporary Table) 在 DPF 环境下已经完全能够支持 pureXML,这为提高在 DPF 中 XML 转换的性能提供了一个很好的选项。
在这一节中,我们围绕一个简单却典型的 ETL 场景,来演示如何用全局临时表为 DPF 下的 XML 转换提供最好的性能。
在 DB2 V9.7 中,一般建议用户在 DPF 下部署支持 pureXML 的 ETL 任务时将它分成 3 步来执行 ( 如图 2):
建立一个 中间表,这个表与目标表有相同的分区键,并分布在同样的节点群上。
从源表中提取相关信息,并插入到中间表中。这一步的操作 XML 或关系数据的连接、谓词过滤,XML 导航,XML 文档构建等。注意,这一步的操作是并行的。
从中间表向目标表转换,包含 XML 文档的转换。这一步同样是可以并行的。
图 2. DPF 中 XML 转换的一般流程
考虑这样一个 ETL 场景,一个大型网上书店 (bookstore) 部署在 DPF 中,系统每天白天不断的接受客户的大量订单,晚上从订单中提取相关的信息,并将销售相关的信息保存起来,为以后分析或统计之用。
我们创建下面这些包含 XML 列的表:
表 books 存储书相关的信息,前面清单 6 中创建表 books 并导入样本数据。
表 Sales 存储销售信息包括 oid(order id),cid(customer id),bid(book id),amount 销售金额,以及 XML 列 details 存储销售的详细信息。这样,我们以后可以很方便的在这个表上统计某客户或某书籍的销售报表。
表 Orders4Today 存储每天收到的订单信息,oid 唯一标识这个订单,details 包含订单的详细信息。清单 11 中插入的样本数据给出了订单详细信息的大概内容。
客户表 customer,XML 列 info 存储客户的详细资料。由于客户表在例子中关系不大,没有为它填充数据。
清单 11. 创建表 Sales、Order4Today 和插入样本数据
create table customer(cid int, info xml);
create table Sales(oid int, cid int, bid int, amount float, details xml)
distribute by hash(oid) in xml_tbsp4n;
create table Orders4Today(oid int, details xml) distribute by hash(oid) in xml_tbsp4n;
insert into Orders4Today values(1001, ' 1001 101
2015 normal ');
insert into Orders4Today values(1002, ' 1002 109
2038 2043
speedy ');
insert into Orders4Today values(1005, ' 1005 112
2023
normal ');
insert into Orders4Today values(1009, ' 1009 175
2056
speedy ');
现在设想 ETL 应用程序需要每天晚上从 Orders4Today 提取订单相关的信息,并进行转换后存储到 Sales 表中。同样,分三步来完成这样一个任务,如清单 12 所示:
创建一个全局临时表 session.Sales4Today,它与目标表 Sales 有相同的结构,而且它创建在表空间 tmp_tbsp4n,在清单 2 中可以看到, tmp_tbsp4n 与 xml_tbsp4n 是在同样的节点群上。
从源表 Orders4Today 中的 XML 列提取 cid,bid,qty 并转换成关系列,bid 与 books 表进行连接并提取书的价格 price,然后与 qty 相乘得到相应的销售金额。而销售的详细信息 details 是一个重新构建的 XML 文档。
将 DGTT session.Sales4Today 的数据经过适当的转换并插入到目标表 Sales。比如,这些转换包括删除不再需要的 cid,插入当前的时间等。
清单 12.用 DGTT来实现 ETL任务
--Step 1: declare the DGTT the same as the target table Sales.
declare global temporary table session.Sales4Today like Sales
on commit preserve rows in tmp_tbsp4n;
--Step 2: select the needed information and insert into the DGTT:
insert into session.Sales4Today
select O.oid, OX.cid, OX.bid,
xmlcast(xmlquery('$INFO/book/price') as float) * OX.qty, OX.details
from Books B,
Orders4Today O,
XMLTABLE('for $i in $o/order/book
return {$o/order/cid} {$i} {$o/order/delivery} '
passing O.details as "o"
columns cid int path './cid',
bid int path './book/bid',
qty int path './book/qty',
details xml path 'document{.}') as OX
where B.bid = OX.bid;
--Step 3: Select from the DGTT and insert into the target table
with xml info transformed to the required format
insert into Sales
select T.oid, T.cid, T.bid, T.amount,
XMLQUERY('transform
copy $new := $temp
modify (do delete $new/info/cid,
do insert {fn:current-date()}
as last into $new/info)
return $new' passing T.details as "temp")
from session.Sales4Today T;
--The final transformed result.
select dbpartitionnum(oid) as PARTITION, oid, cid, bid, amount, details from Sales
PARTITION OID CID BID AMOUNT DETAILS
----------- ----------- ----------- ----------- ------------------------
--------------------------------------------------------
-------------------------------------
1 1009 175 205 +1.65000000000000E+002
2056speedy
2009-10-28Z
3 1005 112 202 +1.36800000000000E+002
2023normal
2009-10-28Z
0 1001 101 201 +1.17500000000000E+002
2015normal
2009-10-28Z
2 1002 109 203 +8.13600000000000E+001
2038speedy
2009-10-28Z
2 1002 109 204 +9.08400000000000E+001
2043speedy
2009-10-28Z
5 record(s) selected.
这样,由于 ETL 过程中的步骤 2 和步骤 3 的 XML 转换在 DPF 中都是并行的,因此能得到较好的性能。
总结
本文可以使读者较为深入的了解 DB2 V9.7 的在 DPF 下支持 XML 数据的特性,包括如何在 DPF 下创建带有 XML 列的表,了解 XML 数据在各分区如何分布存储,如何方便的进行 XML 数据的查询,如何用全局临时表来提高 DPF 下 XML 转换的性能。
转载于:http://blog.itpub.net/15082138/viewspace-662150/