在多个数据源的 J2EE 环境中的 DB2 Information Integrator 性能
本文是我们关于使用 DB2 Information Integrator 进行 J2EE 开发的三部分系列中的最后一篇文章，作者在文中比较了在联邦（federation）中运行的查询与使用直接访问的查询的性能结果。
本文是探讨使用 IBM® DB2® Information Integrator™ 来实现 J2EE 组件（这些组件从多个不同的数据源检索和合并数据）的系列文章中的第三篇，也是最后一篇。我们的第一篇文章是 将 DB2 Information Integrator 用于 J2EE 开发：成本／收益分析，描述了我们的一个项目，用来比较当使用和不使用 DB2 Information Integrator 来实现这类应用程序时的开发成本。第二篇是 跨多个数据源的 J2EE 开发：细节探讨，涵盖了实现我们开发的 J2EE servlet 的详细信息：
- 一组 servlet 通过直接访问分布到 DB2 Universal Database™、Oracle 和 Microsoft® Excel 数据源的数据来评估联邦查询。
- 另一组 servlet 使用 DB2 Information Integrator 来处理同一组的业务问题，这使得全部三个数据源中的数据作为单个数据库出现。
我们发现，使用 DB2 Information Integrator 开发 servlet 远比不使用它时容易，因为我们不必担心拆分联邦查询以及管理对每个数据源的访问甚烦人的细节。但性能如何呢？使用 DB2 Information Integrator 开发的简便性是否会导致高成本呢？难道对那些多数据源查询进行手工编码不会使它们运行得更快吗？本篇文章会在我们的实验中解决那些问题，并且我们希望通过这篇文章，您将对您的环境有更深的了解。
那么哪个更快呢？从 servlet 直接访问多个数据源？还是 DB2 Information Integrator？在性能问题上，答复照例是（也当然是）“视情况而定”。更准确地说，它取决于查询。我们分别用两种方法实现五个查询。在 前一篇文章中详细地讨论过它们的实现，而且您也将在本篇文章中再次看到这些查询，但目前，我们将只注意到所有的查询会访问两个或更多的数据源中的数据，并且大部分查询连接两个或更多的表或视图。 表 1包含以秒计的所用时间的比较。
表 1. 查询的所用时间
| 查询 | 直接访问多个数据源 | 使用 DB2 Information Integrator |
| 1 | 3.4 秒 | 3.5 秒 |
| 2 | 0.18 秒 | 0.25 秒 |
| 3 | 170.1 秒 | 44.5 秒 |
| 4 | 79.9 秒 | 4.5 秒 |
| 5 | 9.9 秒 | 15.1 秒 |
如您所见，使用 DB2 Information Integrator 的联邦查询的性能与从 J2EE 组件直接访问数据的性能是相当具有可比性的。查询 1 在两种情况下耗费的时间相差无几，而在两种情况下查询 3 和 4 中 DB2 Information Integrator 比直接应用程序访问数据快了 4 到 17 倍，但在其它两个查询（查询 2 和 5）中慢了 40% - 50%。
让我们来观察这个结果。例如在查询 5 中，直接访问 servlet 能够实现对于 DB2 Information Integrator 不可用的执行策略，并从而获得性能上的优势。然而，必须将这种优势与在定制直接访问 servlet 中实现联邦查询时固有的可观的额外开发成本、潜在的错误以及缺乏扩展性进行权衡。请记住直接访问 servlet 是手工编码的应用程序，用以实现特定的查询；而 DB2 Information Integrator 是一个通用的基础结构，可以处理任何 SQL 查询而无需特殊的编程。
尽管我们只能测试五个查询，但这些查询都非常具有代表性，可以代表可能在 Web 商务或决策支持环境中执行的、具有不同的性能特性的查询。查询组的结果说明从性能的观点来看，DB2 Information Integrator 可以为 J2EE 组件（这些组件实现跨多个数据源的分布式查询）提供直接应用程序访问的合理的替代方法。
我们将从快速回顾影响联邦查询性能的因素开始。接下来，我们会描述如何安装和配置远程数据源及 DB2 Information Integrator，重点着眼于性能关键性方面（例如索引和统计信息）。最后，我们将详细地描述五个查询中的每一个，并比较它们的执行（分别使用直接应用程序访问数据和 DB2 Information Integrator）。在此过程中，我们将提供一些技巧以从 DB2 Information Integrator 获得最佳性能，同时也能理解为何它在有些情况下表现得很出色，而其它情况下不那么出色。
  |
|
不论您是使用 DB2 Information Integrator 联邦服务器，还是直接访问数据的定制集成应用程序，跨越多个远程数据源的查询的性能都取决于一些相当明显的因素，包括：
- 远程数据源和联邦服务器（或定制集成组件）所在机器的速度，以及那些机器之间网络的容量。
- 在远程数据源选择的查询执行方案（用以执行 DB2 Information Integrator 或者定制集成组件提交给它们的查询）。
除了那些因素之外，决定联邦查询的性能最至关重要的因素是在联邦服务器端的查询执行方案，或者是定制集成组件用以从多个数据源检索和合并数据的处理策略。如果在远程数据源上完成同样多的远程数据处理，它通常会是最佳的，很少有例外， 因为从远程数据源检索数据以便在联邦服务器上完成本地处理的代价是很高昂的。例如，对一个谓词（它从远程数据源的远程表中过滤出许多行以将返回 DB2 Information Integrator 或定制集成组件的行数减到最少）求值就是一个不错的主意。
类似地，若要连接同一远程数据源的两张表，通常最佳的做法是在远程数据源上“叠加（push down）”连接并执行。这在连接产生的行比参与该连接的两张表的总行数少时是成立的。然而，如果连接产生的行数比涉及的两张表中符合条件的总行数多，那么可能在本地执行连接会更好。注意，我们所说的“在本地”指的是相对于远程数据源的在 DB2 Information Integrator 上进行的处理。在远程或在本地执行连接的决策（不论通过定制集成组件或是 DB2 Information Integrator）主要取决于使得从涉及的全部远程数据源返回的总行数最少的需要。DB2 Information Integrator 会根据在查询编译过程中昵称的统计信息自动作出选择；而定制集成组件的开发者必须在实现的过程中作出决策。
在此描述的对比实验中，我们尽量确保将尽可能多的处理叠加至每个查询的远程 DB2 UDB、Oracle 和 Excel 数据源。直接访问远程数据的 J2EE servlet 有机会也有责任完全控制联邦查询的分解、叠加和本地合并处理。使用 DB2 Information Integrator 的 J2EE servlet 依赖 DB2 Information Integrator 来拆分联邦查询，并向每个数据源发送相应的子查询。如果正确地配置 DB2 Information Integrator，并且如果远程对象的昵称具有精确的索引和统计信息，则 DB2 Information Integrator 可以完成具有高度叠加的查询执行方案。
DB2 Information Integrator 中有一种提高性能的功能，它能通过创建所谓的具体查询表（materialized query table，MQT）来创建远程数据的本地高速缓存副本。MQT（以前称为“自动摘要表”）不仅可以在本地表上定义，而且可以定义为包括一个或多个昵称的子查询的结果。MQT 因而可以作为远程数据的本地“时间点”的快照来使用。它的存在和使用对于向 DB2 Information Integrator 发出包括多个昵称的查询的用户来说是透明的。也就是说，优化器可以透明地决定使用正确定义的 MQT 中的本地数据，而不真正地通过昵称访问该数据。使用本地而非远程数据在性能上会有非常明显的优势，特别是如果用户在执行与其它用 DB2 Information Integrator 在本地存储的数据的连接时。本篇文章中呈现的结果并不能反映 MQT 的使用，我们将指出其中两个样本查询中使用 MQT 可能会有优势的情况。
|
|
本节描述了硬件和软件的设置，包括网络连接、跨数据源的数据的布局、表的索引以及昵称的统计信息。
每个 DB2 UDB 和 Oracle 远程数据源数据库都驻留在多处理器的 Windows® 服务器上。DB2 Information Integrator 联邦数据库驻留在桌面 Windows 工作站（它也保存了我们的项目中使用的 Excel 电子表格数据）上。所有的系统通过 100 Mb/s 的以太网连接。
在“直接访问”环境中，Websphere® Application Server 测试环境中运行的 servlet 直接访问全部三个数据源（Excel、Oracle 和 DB2 UDB）。直接访问 servlet 仅使用本地 DB2 Information Integrator 实例来主管临时工作表。在联邦环境中，servlet 只访问 DB2 Information Integrator 实例（已配置为连接到三个数据源）。
图 1. 用于我们的项目的软件体系结构
我们的测试数据由 TPC-H 基准模式中的六张表组成。我们在 第一篇文章中包含了 DDL 表。四张表（ORDERS、CUSTOMER、PART 和 PARTSUPP）分布在各个数据源（DB2 UDB、Oracle 和 Excel）上。较小的表（NATION 和 SUPPLIER）被复制在全部三个数据源上。
对于分布式表，大多数主键值都以循环方式分布在全部三个数据源上。一些键值也复制在不止一个数据源上。匹配的主键和外键（foreign key）始终保持在同一个数据源上。例如，CUSTOMER 表就是这样分布的：6/7 的 c_custkey 值只驻留在其中一个数据源上，而 1/7 的 c_custkey 值被复制在两个数据源上（来反映合并前的企业可能共享客户这样一个事实）。然而，对于给定的数据源，给定客户的所有订单都驻留在同一数据源上；也就是说，ORDERS 行通过条件 o_custkey = c_custkey 来与 CUSTOMER 行协同定位。PART 和 PARTSUPP 表有类似的分布。同样，有一些跨数据源的部件键（part key）的复制，但匹配的 p_partkey 和 ps_partkey 值位于同一数据源上。如果同一供应商在一个以上的数据源上提供同一部件，则给定的部件键和供应商的批发成本（ ps_supplycost ）在各个数据源上各不相同。也就是说，同一供应商可能以不同的价格向合并前的不同企业提供了同一部件。
ORDERS 是最大的表，它包含了大约 500 万行，分布在三个数据源上。PARTSUPP 表包含大约 270 万行，而 CUSTOMER 和 PART 表都包含 50 万行。最小的表是 SUPPLIER 和 NATION，分别有大约 10 万行和 25 行。在 DB2 UDB 和 Oracle 数据源上填充这些表后，我们在 DB2 上使用 RUNSTATS 和在 Oracle 上使用 ANALYZE 来收集它们的统计信息。由于 Excel 没有提供等效的函数，所以我们无法用类似的方式收集该数据源的统计信息。我们不久将会描述我们如何应付使用 DB2 Information Integrator 时的这个问题。
不论是否使用 DB2 Information Integrator，在远程表上定义适当的索引对获得良好的性能通常都是一个至关重要的因素。虽然无法在 Excel 电子表格上定义任何索引，但是我们的确可以在每个 DB2 UDB 和 Oracle 数据源表上定义数个索引。我们选择在执行连接和谓词（出现在我们一系列的测试查询中）求值时将会有用的索引。
DB2 UDB 和 Oracle 数据源上的 Orders 表有以下唯一索引：
- O_ORDERKEY, O_CUSTKEY
- O_ORDERDATE, O_CUSTKEY, O_TOTALPRICE
- O_CUSTKEY, O_ORDERDATE, O_ORDERPRIORITY, O_TOTALPRICE
Customer 表有两条索引：
- C_CUSTKEY
- C_CUSTKEY, C_NAME
Partsupp 表有一条多列的索引：
- PS_PARTKEY, PS_SUPPKEY, PS_SUPPLYCOST
Supplier 表有一条多列的索引：
- S_SUPPKEY, S_NATIONKEY, S_NAME
Nation 表有两条索引：
- N_NAME, N_NATIONKEY
- N_NATIONKEY, N_NAME
DB2 Information Integrator 的配置
如 上一篇文章中所述，我们为三个远程数据源创建了 DB2 Information Integrator 包装器和服务器定义，同时还为每个远程表创建了昵称。回想一下昵称的 UNION ALL 视图已定义为使分布在全部三个数据源上或复制在它们之上的表作为一张逻辑表出现。我们使用 Net8 包装器来访问 Oracle 数据源，以及 DRDA® 包装器来访问 DB2 数据源。虽然 DB2 Information Integrator 为远程 Excel 电子表格提供了专用的 Excel 包装器，但我们发现使用 ODBC 包装器来访问 Excel 提供了好得多的性能。原因是可以定制 ODBC 服务器定义来指导 DB2 Information Integrator 把重要的处理“叠加”到 Excel，而使用 Excel 包装器是不可能做到这一点的。然而，Excel 包装器提供了其它的好处，包括对允许的电子表格格式的更少限制。在 http://www.ibm.com/software/data/integration上提供了关于在 Excel 和 ODBC 包装器之间进行选择以访问 Excel 电子表格的完整指导。
DB2 Information Integrator 昵称的统计信息
当您为一张远程表创建昵称定义时，DB2 Information Integrator 会自动尝试从远程数据源检索关于索引以及表统计信息的信息。DB2 Information Integrator 依赖于由远程数据源收集和存储的索引和统计信息；它不会尝试自己生成这些信息。目前只能在远程 DB2 UDB 和 Oracle 数据源上为对象收集足够的昵称的统计信息数据。而在其它类型的数据源上最低限度地收集、或者不收集对象昵称的统计信息数据。如果只是执行非常简单的涉及 DB2 Information Integrator 昵称的查询，缺少昵称的统计信息可能不是问题。可是对于更复杂的查询，具有有效的昵称统计信息是很重要的。
在我们的案例中，没有统计信息可用于通过 ODBC 包装器访问的 Excel 电子表格。因为 DB2 Information Integrator 查询优化器需要精确的统计信息来为除最简单的查询以外的所有查询选择适当的执行方案，而在 DB2 Information Integrator 无法检索昵称的一些基本统计信息的情况下，最好将其填写到远程数据源上。
要为此类昵称填充 DB2 Information Integrator 的目录统计信息，一种方法是使用称作 get_stats 的工具（您可以 下载而获得）。如下所示调用此实用程序：
get_stats |
这个工具只是一个程序，它针对昵称本身发出一些动态的 SQL 查询（例如针对每列执行 COUNT(*)、MIN() 和 MAX() 还有 COUNT DISTINCT()）以填充 DB2 Information Integrator 的统计信息。这些查询的代价会很昂贵，但所产生的昵称统计信息通常有助于选择最佳性能的查询执行方案。我们使用 getstats 工具来“收集”与 Excel 电子表格对应的昵称的统计信息：
get_stats db2admin db2admin TEST DB2ADMIN.ODBC_PART get_stats db2admin db2admin TEST DB2ADMIN.ODBC_PARTSUPP get_stats db2admin db2admin TEST DB2ADMIN.ODBC_CUSTOMER get_stats db2admin db2admin TEST DB2ADMIN.ODBC_ORDERS get_stats db2admin db2admin TEST DB2ADMIN.ODBC_SUPPLIER get_stats db2admin db2admin TEST DB2ADMIN.ODBC_NATION |
请注意，get_stats 工具的使用是一次性操作，除非 Excel 数据源上的数据发生实质上的变化，否则无须重复操作。
如果由于某种原因运行 get_stats 不切实际，您可以选择为每个需要统计信息数据的昵称编写脚本以在 DB2 Information Integrator 目录包含的 SYSSTAT 视图中更新相关的项。最重要的项是：
- SYSSTAT.TABLES：将 CARD 列设置为表中由昵称引用的行数。
- SYSSTAT.COLUMNS：将 COLCARD 列设置为每个昵称每列的唯一值的个数。
- SYSSTAT.COLUMNS：将 HIGH2KEY 设置为每个昵称每列中的最大值（实际上，“HIGH2KEY”指的是列值中次高的值，但在实际情况下，最大值更容易找到并且非常接近）。
- SYSSTAT.COLUMNS：将 LOW2KEY 设置为每个昵称每列中的最小值。
不论是在创建昵称的基础上自动地收集统计信息，还是需要通过使用 get_stats 或编写脚本来促成其完成，您都可以通过查询 DB2 Information Integrator 的 SYSSTAT.COLUMNS 和 SYSSTAT.TABLES 目录视图来检查昵称统计信息是否存在。例如，以下是如何查看 ODBC_CUSTOMER 昵称的列统计信息：
select char(tabschema, 10) as schema, char(tabname, 15) as table, char(colname, 15) as column, int(COLCARD) as colcard, char(HIGH2KEY,20) as high2key, char(LOW2KEY,20) as low2key from sysstat.columns where tabname = 'ODBC_CUSTOMER'; SCHEMA TABLE COLUMN COLCARD HIGH2KEY LOW2KEY --------- -------------- ------------- ----------- ------------ ------------- DB2ADMIN ODBC_CUSTOMER C_ACCTBAL 1620 009998.0700 -000998.7200 DB2ADMIN ODBC_CUSTOMER C_ADDRESS -1 DB2ADMIN ODBC_CUSTOMER C_COMMENT -1 DB2ADMIN ODBC_CUSTOMER C_CUSTKEY 1620 600000 598381 DB2ADMIN ODBC_CUSTOMER C_MKTSEGMENT 5 HOUSEHOLD BUILDING DB2ADMIN ODBC_CUSTOMER C_NAME 241664 Cust#598375 Cust#00002 DB2ADMIN ODBC_CUSTOMER C_NATIONKEY 25 24 0 DB2ADMIN ODBC_CUSTOMER C_PHONE 241664 999-723-9736 100-178-1709 |
|
|
让我们来进一步分析这五个查询中的每一个。我们将重点分析使用 DB2 Information Integrator 的联邦查询的处理，并在适当的时候与直接访问应用程序实现进行比较。在理解如何处理联邦查询中至关重要的工具是 DB2 Explain，它能显示 DB2 Information Integrator 将会使用的查询执行方案。Visual Explain（可以从“控制中心”中调用）是查看执行方案最简单的方法，因为它自动管理以下全部步骤。或者，您也可以按照如下所示从命令行环境中生成并格式化 Explain 方案：
- 连接到 DB2 Information Integrator 数据库。要创建 DB2 Explain 表，请执行
db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL.
这是一次性操作，无须再次重复。在 DB2 Information Integrator 目录中创建了 Explain 表后，它们可以在该数据库的生存期内使用了。
- 要为一个给定的查询在 Explain 表中填充查询方案信息，请连接到 DB2 Information Integrator 数据库并发出以下命令来说明该查询：
db2 explain plan for
- 要将 Explain 格式化为可读文件，db2exfmt 是首选的工具，因为它给出关于执行查询的最多信息。要格式化刚说明过的查询，请使用：
db2exfmt -d -1 -o
这是组中第一个，并且是最简单的查询。查询 1 访问全部三个数据源上的 PART 表以列出具有特殊属性的部件键。该查询的“概念”表达式是：
Select distinct p_name, p_mfgr, p_type, p_partkey
from
where p_type like ? and
p_name like ?
order by p_partkey fetch first 20 rows only
? = '%BURNISHED%',
? = '%lavender%'
|
回想一下数据源上可能有重复的部件键，因此用 DISTINCT 来排除副本。现在让我们来回顾如何分别使用直接访问数据和 DB2 Information Integrator 以在 J2EE 组件中实现该查询。
在直接访问数据中，我们让 servlet 从每个数据源取出感兴趣的部件行，并将它们插入一个本地临时表，然后在临时表上完成最终的 SELECT DISTINCT 来清除重复的部件。最初，我们没有想到要在我们发送到每个远程数据源的查询中包含“order by”或“fetch first 20 rows only”子句，因此该查询耗费了很长时间。稍后，我们认识到将从每个数据源检索的行限制为前 20 个部件键是很有意义的，因为只有它们才是成为全部数据源上前 20 个部件键的候选行。
我们使用 DB2 Information Integrator 向 FED_PART（即每个数据源上 PART 表的昵称的 UNION ALL 视图）发出以下查询：
Select distinct p_name, p_mfgr, p_type, p_partkey
from fed_part
where p_type like cast(? as varchar(25)) and
p_name like cast(? as varchar(55))
order by p_partkey fetch first 20 rows only
|
该表达式唯一值得注意的特征是向参数标记（指示每个谓词中列的类型）添加了显式 CAST 函数。例如，由于 p_type 是 VARCHAR(25)，我们将对应的参数标记强制转换为 VARCHAR(25)。不使用这些 CAST 时，参数标记是“隐式类型（untyped）”，并对它们的类型进行缺省假定。例如，在这个案例中，隐式类型参数标记（untyped parameter marker）是 LIKE 谓词的模式表达式，并在缺省情况下假定其为 VARCHAR(32672) 类型。（请参阅 SQL Reference 中关于 PREPARE 的文档）。
结果是该缺省假定对性能有负面的影响。为什么呢？因为，如果 DB2 Information Integrator 假定 LIKE 的模式表达式可以是一个相当大的 VARCHAR 表达式，则它需要生成一个对每个包含同样假定的远程数据源的查询。然而，Oracle 中 LIKE 谓词的模式表达式最长可达 4000 字节。所以 DB2 Information Integrator 的确无法安全地将一条 LIKE 语句发送到可能包含过长的模式表达式的 Oracle 数据源。如果 DB2 Information Integrator 无法确认该模式表达式是足够短的而可以发送至 Oracle，相反它将从 Oracle 检索行并在本地对 LIKE 谓词求值！与在 Oracle 数据源上对 LIKE 谓词求值相比，这样做通常将从 Oracle 数据源返回更多行。
原则：为了获得良好的性能，请务必用 CAST 函数对 LIKE 谓词中包括的参数标记进行强制转换以匹配它们要比较的列的类型。这样做会增加将 LIKE 谓词叠加到远程数据源的可能性。
查询 1 的 Explain 输出（使用 db2exfmt）让我们看到哪个处理是在远程完成的，以及哪个处理是由 DB2 Information Integrator 在本地完成的。注意，Visual Explain 输出看起来非常相似。
图 2. 查询 1 的 Explain 输出
这里，我们可以看到 DB2 Information Integrator 执行了三个远程查询结果的本地 UNION。发生这种情况是因为 Q1 涉及一个昵称的 UNION ALL 视图，这些昵称在三个数据源上引用 PART 表。
SHIP 运算符显示在执行方案中处理被转移到远程数据源的点。紧靠在每个 SHIP 运算符上方的数字表示优化器对从远程数据源返回的行数的估算值。
紧靠在昵称上方的数字显示优化器对远程表中行数的估算值；例如，DB2ADMIN.ORA_PART 昵称指向具有 341,100 行的 Oracle PART 表。因为从 ORA_PART 上方的 SHIP 运算符返回的行数（13 行）远比昵称上方的估算值小，所以我们可以假定将 LIKE 谓词的求值叠加到 Oracle 数据源，且只向 DB2 Information Integrator 返回符合条件的行。实际上，我们可以通过查看 db2exfmt 输出中 SHIP 运算符的详细信息来确保完全看到哪个查询被发送到 Oracle 数据源；但是为了简便起见，不在此讨论输出中的那一部分。
LIKE 谓词也可以叠加到 DB2 数据源，即最左边的 UNION 分支。然而，ODBC 数据源的 SHIP（最右 UNION 分支）检索 ODBC_PART 的全部 4100 行。LIKE 过滤通过 DB2 Information Integrator 在本地完成，因为它保守地假定 ODBC 数据源无法对 p_type 和 p_name 上的 LIKE 谓词求值。结果是来自 ODBC 数据源的 4100 行中只有 43 行确实符合 LIKE 谓词的条件，所以能够叠加 LIKE 求值将会向 DB2 Information Integrator 返回少得多的行。启用 ODBC 服务器上的 db2_maximal_pushdown服务器选项不会启用该行为。请记住，只有在 DB2 Information Integrator 确信远程数据源可以执行需要的处理的情况下，该选项才会强制“叠加”执行方案。
DB2 Information Integrator 处理查询 1 的速度与直接访问 servlet 不相上下。微小（3%）的性能差别受两个因素的影响：
- 如我们刚刚所看到的，DB2 Information Integrator 不会将 ODBC_PART 上的 LIKE 谓词叠加到 Excel，而是从 ODBC_PART 检索全部行而不仅仅是符合条件的行。直接访问 servlet 将 LIKE 处理叠加到全部三个数据源上。
- 直接访问 servlet 只从每个数据源检索查询结果的前 20 行。DB2 Information Integrator 没有利用同一优化；只在 UNION 运算符上方的最终排序过程中将结果限制为 20 行。
相对于直接访问 servlet，两个因素都会增加远程数据源和 DB2 Information Integrator 之间传送的行数。
查询 2 访问全部三个数据源上的 SUPPLIER、PARTSUPP 以及 NATION 表来查找给定国家或地区中对给定的部件键具有最低供应成本的供应商。该查询的概念表达式是：
select ps_partkey, s_name, s_suppkey, min(ps_supplycost) as ps_supplycost from , , where ps_partkey = ? and n_name = ? and s_nationkey = n_nationkey and ps_suppkey = s_suppkey group by ps_partkey,s_name, s_suppkey ? = 28 ? = 'GERMANY' |
用直接访问数据实现该查询很简单。我们分别向每个数据源发出查询，以查找相关的部件键的最低供应成本和供应商名称，并将数据插入到一个本地临时表中。最后，我们查询此本地表以获取全部三个数据源上供应成本的最小值。
使用 DB2 Information Integrator，此查询将按如下方式表达，它使用了三个 UNION ALL 视图，分别是 FED_PARTSUPP、FED_SUPPLIER 和 FED_NATION：
select ps_partkey, s_name, s_suppkey,
min(ps_supplycost) as ps_supplycost
from fed_partsupp, fed_supplier, fed_nation
where ps_partkey = ? and
n_name = ? and
s_nationkey = n_nationkey and
ps_suppkey = s_suppkey and
ps_server =
s_server and
s_server =
n_server
group by ps_partkey, s_name, s_suppkey
|
此处需要注意的要点是对原始查询添加的“server”谓词（斜体字）。请记住每个“表”其实就是一个昵称的 UNION ALL 视图，该视图与标识数据源的“server”属性一起表示远程数据源上的表。当连接两个 UNION ALL 视图时，我们需要仔细考虑在查询结果中我们是否想要包含跨数据源连接，跨数据连接就是将一个数据源上的表中的行与另一数据源上的表中的行进行连接。在 UNION ALL 视图的“server”属性上添加“=”谓词表明我们 不希望包含跨数据源连接。
稍后我们将了解允许跨数据源连接是否会导致在涉及 UNION ALL 视图的一些查询中产生错误的结果并且还会增加计算量。结果显示此查询不是那些情况下的查询之一。然而，即使在某些错误结果不成问题的情况下，通过添加 server 谓词来避免跨数据源连接可以显著地提高性能。为什么呢？让我们看看以下查询中的一个连接，此连接建立在 FED_SUPPLIER 和 FED_PARTSUPP 之间，条件是 s_suppkey = ps_suppkey 。它实际上是下列昵称之间的连接
相对于执行 UNION 间的连接，一般我们更愿意尝试将这种连接变换为很多较小连接的 UNION，类似于在代数表达式上将和的乘积变换为乘积的和。为什么这种变换往往是很有益处的呢？不进行变换，昵称的 UNION 需要从每个昵称检索所有符合条件的数据，以在联邦服务器上处理一个跟有本地连接的本地 UNION。另一方面，如果我们能够首先在远程数据源上进行（叠加）连接，这些连接将得益于那些数据源上的任何可用的索引。而且，这些叠加连接可以在远程数据源上过滤数据，而本来这些数据将发送到联邦服务器以执行几个 UNION 的连接，因此这种变换有很大可能使得总体上在远程数据源和联邦服务器之间可以移动更少的数据。
通常，用这种方式变换的 UNION 连接首先需要对第一个 UNION 中的每个元素与第二个 UNION 中的每个元素作连接，然后所有连接的结果还需要再一起作 UNION。因此三个数据源上每个都有的 supplier 表需要和 ORA_PARTSUPP、DB2_PARTSUPP 和 ODBC_PARTSUPP 一共作九次连接。但是我们发现大部分连接并不是必需的！因为根据数据在数据源上分布的方式，我们知道在一个数据源上一个部件的所有供应商可以在同一个数据源的 supplier 表中找到；而不需要再去看其它地方了。部件键甚至可能在另两个数据源中的任何一个上都不存在；在这种情况下跨数据源的 SUPPLIER - PARTSUPP 连接将得到零行结果。额外的跨数据源连接不影响查询的答案集，但是它们会增加额外的开销。对于这个查询，删除 server 谓词将得到相同的答案，但是它将使查询时间延长四倍！
总的来说，当连接跨越两个或更多数据源的昵称的 UNION ALL 视图时，需要添加“server”谓词，该谓词禁止跨数据源连接，除非您知道它们对获得正确的结果是必不可少的。避免跨数据源连接几乎总是可以显著地提高查询的速度。另外，如果允许跨数据源的连接，一些涉及到聚合和昵称的 UNION ALL 视图的查询将产生不正确的结果；这些查询 需要额外的“server”谓词以获得正确的结果；在下一节我们将看到一个这样的查询示例。
查询 2 的 DB2 Information Integrator Explain（ 图 3）显示了避免跨数据源连接的效果。将 NATION、SUPPLIER 和 PARTSUPP 的三个 UNION ALL 视图之间的连接变换为每个数据源的这三个表之间的叠加连接的 UNION ALL。DB2 Information Integrator 只对连接结果执行最终聚合和 UNION。
直接位于 UNION 运算符下的三组 SORT、TBSCAN 和 GRPBY 运算符表明了 min ( ps_supplycost ) 聚合没有叠加至远程数据源，而是在本地执行。因为只有很少量的数据从远程数据源送回（准确地说是每个数据源返回四行），在本地检索它来计算聚合没有任何坏处。
您会发现到 ODBC 数据源的叠加连接与其它两个看上去稍有不同。对 ODBC 额外的运算符包含 SHIP（30）上方的 FILTER（29）和 NLJOIN（27），它们的作用是保证连接的输出不为空。DB2 UDB 和 Oracle 数据源是不需要这些预防措施的，因为所有的 DB2 UDB 和 Oracle 昵称列已经由 DB2 Information Integrator 声明为 NOT NULL 了。然而，ODBC/Excel 数据源的所有昵称列都标记为可为空，所以 DB2 Information Integrator 要很小心地处理可能为空的情况。
图 3. 查询 2 的 Explain 输出
此查询访问所有三个数据源上的 CUSTOMER 和 ORDERS 表以找到给定时间段内组合订单值最大的 10 位客户。回想起在数据源中客户键（customer key）有一些重复，因为一位给定的客户可能在不只一个合并前的企业中下过订单。我们希望此查询可以正确地反映一个客户在已合并企业中总的订单，而不管他们是否在一个以上的数据源上下了订单。此查询的概念表达式为：
select sum(o_totalprice) as totalordered , count(*) as
num_orders, c_custkey, c_name
from ,
where o_custkey = c_custkey
and o_orderdate >= ?
and o_orderdate < ?
group by c_custkey, c_name
order by totalordered desc fetch first 10 rows only;
? = DATE('1998-07-01')
? = DATE('1998-07-31')
|
直接访问 servlet 对每个数据源执行此查询，但不包括 order-by 子句。为什么呢？因为我们无法知道以所有数据源上的总订单值来为客户所作的排序（直到已经汇总了他们在所有数据源上的订单值），所以在访问每个单独的数据源时就进行排序显得太早了。因此，直接访问 servlet 必须计算每个数据源上每个客户的所有符合条件的订单的总价格，然后将它们插入到一张本地临时表。然后计算每个客户在所有数据源上的总的订单值，并以总订单值的降序对结果排序。
这是查询 3 提交给 DB2 Information Integrator 的版本：
select sum(o_totalprice) as totalordered , count(*) as
num_orders, c_custkey, c_name
from fed_customer , fed_orders
where o_custkey = c_custkey
and o_server = c_server
and o_orderdate >= ?
and o_orderdate < ?
group by c_custkey, c_name
order by totalordered desc fetch first 10 rows only;
|
因为这个查询连接了两个 UNION ALL 视图，请注意我们再次在每个视图的“server”（斜体字）列插入了一个连接谓词，位于不同数据源上的 CUSTOMER 和 ORDER 表之间的此种连接是排除在外的。这次我们需要这么做不仅是为了良好的性能，还有查询的准确性！
如果允许跨数据源的连接，由于在多于一个数据源上相同的客户键发出的订单计算了两次，我们将得到不正确的结果。例如，在数据源 A 上的一个客户键（在数据源 B 上还有一个副本）将在它自己的数据源（A）和另外的数据源（B）上找到相匹配的订单。同时在数据源 B 上的相同客户键将再次在两个数据源上找到匹配的订单，因此该客户下的每个订单都将计算两次！当使用 UNION ALL 视图时，请注意聚合上重复连接键的影响，这一点是非常重要的。
查询 3 的 DB2 Information Integrator Explain 方案（ 图 4）再次显示了 UNION ALL 视图的连接变换为连接的 UNION ALL。DB2 UDB 和 Oracle 昵称被完全叠加到它们各自的数据源；只有 ODBC_CUSTOMER 到 ODBC_ORDERS 的连接由 DB2 Information Integrator 在本地执行。按每个数据源的客户键分组聚合 sum(o_totalprice) 的计算也是在本地执行，虽然它本来可以（至少）被叠加至 Oracle 和 DB2 UDB。优化器不选择叠加计算和，因为它相信聚合不会减少从远程数据源返回的总行数。事实并非如此；聚合实际上按 10 的倍数减少返回的行数。这个问题还需要进一步研究。然而，可以为 Oracle 和 DB2 UDB 服务器启用 db2_maximal_pushdown服务器选项以产生一个将聚合和分组叠加到这些数据源的执行方案。
图 4. 查询 3 的 Explain 输出
尽管在此执行方案中叠加工作情况并不是很好，DB2 Information Integrator 运行此查询比起直接访问 servlet 还是要快得多。这是非常令人惊讶的，因为 DB2 Information Integrator 从每个数据源向联邦服务器返回满足 o_orderdate 值的所有 Orders 行，并在本地为每个客户键计算 sum(o_totalprice) 。另一方面，直接访问应用程序通过客户键将对订单总价格的计算叠加至每个远程数据源，然后对每个数据源的每个客户仅需要检索一行以插入到一张本地临时表中。
即使 DB2 Information Integrator 从远程数据源检索更多的数据并且选择不将连接叠加至 ODBC 数据源，为什么它还是执行得更快？这并不完全清楚，但是也有几个可能性（在这里我们没有时间研究它的详细内容）：
- 或许直接应用程序实现的到 ODBC/Excel 的叠加连接运行起来确实非常糟糕，而我们通过 DB2 Information Integrator 对从 ODBC/Excel 取装得到的数据作本地连接正好避免了这点。
- 或许叠加 CUSTOMER － ORDER 连接在 Oracle 和 DB2 UDB 数据源上有不同的执行方案，因为它们在 DB2 Information Integrator 和直接访问 servlet 中是以不同的方式表达的（后者包含按 c_custkey 分组的 o_totalprice 上的聚合，前者不具有）。
此查询在 DB2 UDB 数据源上查找一个合并前企业的客户，这些客户住在某些特定的国家或地区并且在另一个位于 Oracle 数据源上的合并前企业中下了大值订单。对于 DB2 Information Integrator，它以此种方式表达：
select c_custkey, c_name, o_totalprice, n_name
from db2_customer, ora_orders, db2_nation
where c_nationkey = n_nationkey and
c_custkey = o_custkey and
o_totalprice > ? and
n_name in (?,?,?,?)
? = 450,000
(?,?,?,?) = ('JAPAN', 'CHINA', 'VIETNAM', 'INDIA')
|
实现此查询的直接访问 servlet 创建了两个本地临时表。它将 CUSTOMER 和 NATION 之间的连接发出至远程 DB2 数据源，然后检索所有住在指定国家或地区的客户并写入第一张表。然后，再在 Oracle 数据源检索所有满足 o_totalprice 谓词的订单，并写入第二张表中。最后，在两个临时表之间执行本地连接来将符合条件的客户和订单相匹配。但这不是最佳的处理策略，这是一个可以讨论的简单而合理的开局，特别是如果实现器没有关于 o_totalprice 或 c_nationkey 分布的详细信息的时候。
提交给 DB2 Information Integrator 的查询和上面描述的完全一样。让我们看看查询 4 的 Explain 方案。
图 5. 查询 4 的 Explain 输出
- 首先，DB2 Information Integrator 从 Oracle 数据源检索满足 o_totalprice 谓词的所有 ORA_ORDERS 行。将它们插入内存中的一张本地散列表以准备散列连接。
- 然后，DB2 Information Integrator 将 DB2_NATION 和 DB2_CUSTOMER 之间的连接叠加至远程 DB2 UDB 数据源。做这样一件事情是非常明智的，因为它将符合条件的客户数从大概 250,000 降低到 40,000 左右。
- 带有符合条件的客户键（ c_custkey ）的连接的结果行返回到 DB2 Information Integrator。对于像这样的每一行，散列连接运算符探测最初从 ORA_ORDERS 构建的散列表，以找到与 o_custkey 值相匹配的行。
这是一个很好的执行方案，并且 DB2 Information Integrator 处理这个查询要比用直接访问 servlet 查询快得多。为什么呢？
首先，直接访问 servlet 必须将两组符合条件的行写到临时表中，一个是客户的，一个是订单的。DB2 Information Integrator 将符合条件的订单存储在本地散列表中。连接左边检索得到的客户行在连接之前也不需要写入磁盘。所以 DB2 Information Integrator 比直接访问 servlet 在处理查询 4 时要执行更少的 I/O 操作。
第二个可能减慢直接访问 servlet 的因素是它必须在没有收集任何统计信息的本地 DB2 数据库上连接两张临时表（一张是存放订单的，另一张是存放客户的）。由本地数据库对两张临时表作的连接结果可能得到一个很糟糕的方案。然而，我们无法验证这个理论。
这个查询计算在一个合并前企业的某些特定客户所付的在所有三个企业中所下订单价格总和的平均值。它将 DB2 UDB 数据源上具有某些特定属性的客户与所有三个数据源上的订单组合作连接。概念表达式为：
select avg(o_totalprice) as avg_order, c_name, c_custkey, c_acctbal from , , where c_custkey = o_custkey and c_nationkey = n_nationkey and n_name = ? and c_mktsegment = ? and c_acctbal >= ? and c_acctbal <= ? group by c_custkey, c_name, c_acctbal order by avg_order desc ? = 'JAPAN' ? = 'HOUSEHOLD' ? = 0 ? = 1000 |
在直接访问 servlet 中的查询 5 中首个实现是很简单的：
- 创建一张本地临时表，该表包含了 customer_key、num_orders 和 total_orders 列。servlet 按顺序访问每个数据源上的 Orders 表，然后用计算 sum(o_totalprice) 和 count(*) 并按 o_custkey 分组的查询结果来填充临时表。因此，每个 o_custkey 值在该表中可能有一到三行记录，这取决在不同的数据源上是否有重复的客户。
- 创建另一个临时表，该表存放有在 DB2 数据源上连接 DB2_CUSTOMER 和 DB2_NATION 的查询结果。该查询从符合条件的客户中检索 c_custkey、c_name 和 c_acctbal 。
- 最后，两个临时表在本地进行连接（条件是 c_custkey = customer_key ），然后对每个符合条件的 customer_key 用 sum(total_orders) 除以 sum(num_orders) 得到 o_totalprice 的平均值。
此实现方法可以运行，但是它有不好的地方，就是需要将所有三个数据源上的全部 Orders 表的内容都读取并聚合，然后将每个 o_custkey 值的聚合插入第一张临时表。性能非常糟糕，因此我们决定尝试另一种不同的方案。
第二个直接访问 servlet 用一种比较复杂的方式实现此查询，它从远程数据源上检索 ORDERS 数据，查找符合条件的 o_custkey 值。
- 首先，此 servlet 向 DB2 UDB 数据源发出以下查询，该查询为所有与符合条件的 c_custkey 值相符的订单计算 sum(o_totalprice) 和 count(*) 。
SELECT COUNT(*) AS num_order, SUM(o_totalprice) AS total_order, c_name, c_custkey, c_acctbal FROM customer, orders, nation WHERE c_custkey = o_custkey AND c_nationkey = n_nationkey AND n_name = ? AND c_mktsegment = ? AND c_acctbal >= ? AND c_acctbal <= ? GROUP BY c_custkey, c_name, c_acctbal;
该 servlet 将从连接得到的结果行插入一张本地临时表。这张临时表列出了符合条件的客户，而且对每个客户都包含单独的一行，含有所下订单数目和总价格。
INSERT INTO temp_jquery3 (c_custkey, c_name, c_acctbal, total_order, num_order) VALUES (?, ?, ?, ?, ?)");
- 接下来，它继续为其它两个数据源（Oracle 和 Excel）上的这一系列客户查找订单，并更新本地临时表使其还包含位于其它数据源上的订单数目和总的 o_totalprice 值。该 servlet 实现的是一种嵌套连接，它从 Oracle 和 Excel 数据源上检索与已经标识的客户列表相符的订单。对于在步骤 1 中找到的每个 o_custkey 值，我们向 Oracle 和 Excel 数据源发出以下查询：
SELECT COUNT(*) AS num_order, SUM(o_totalprice) AS total_order, o_custkey FROM orders WHERE o_custkey = ? GROUP BY o_custkey
按这种方式，两个数据源上的订单都已经相加并按客户键进行了分组，所以可以很容易地将它们添加到临时表中：
UPDATE temp_jquery3 SET num_order = num_order + ?, total_order = total_order + ? WHERE c_custkey = ?
- 最后，临时表上包含了每个客户下的所有订单的总价格以及在所有数据源上的订单的总数目。该 servlet 对本地表发出一个简单的查询，该查询对每个客户用合计订单价格除以订单数目以获取客户订单价格的平均值，并按降序对结果排序。
SELECT AVG(total_order/num_order) AS avg_order, c_name, c_custkey, c_acctbal FROM temp_jquery3 GROUP BY c_custkey, c_name, c_acctbal ORDER BY avg_order DESC;
该直接访问 servlet 的实现需要大量的思考和编码。实现的关键技巧是，不能将计算 avg(o_totalprice) 叠加到各个数据源，因为直到已经检索了所有数据源上的数据才能计算平均值。因而该 servlet 必须反过来从每个数据源检索 sum(o_totalprice) 和 count(*) 以在最终一个步骤中计算 avg(o_totalprice) 。
和该 DB2 Information Integrator 一起使用的查询 5 的表达式按照如下所示。它显示了将 DB2_CUSTOMER 和 DB2_NATION 表与 FED_ORDERS 作连接，其中 FED_ORDERS 即为在所有三个数据源上的 ORDERS 表的 UNION ALL 视图。
select avg(o_totalprice) as avg_order, c_name, c_custkey, c_acctbal
from db2_customer, db2_nation, fed_orders
where
c_custkey = o_custkey and
c_nationkey = n_nationkey and
n_name = ? and
c_mktsegment = ? and
c_acctbal >l= ? and
c_acctbal <= ?
group by c_custkey, c_name, c_acctbal
order by avg_order desc
|
DB2 Information Integrator 的查询执行方案如 图 6所示。将 DB2_CUSTOMER、DB2_NATION 和三个 ORDERS 表的 UNION ALL 视图之间的连接变换为三个连接的 UNION，其中每个连接都发生在 DB2_CUSTOMER、DB2_NATION 和每个数据源上的 ORDERS 表之间。
DB2_CUSTOMER、DB2_NATION 和 DB2_ORDERS 之间的连接被完全叠加而且也应该叠加至 DB2 UDB。UNION 剩余的两个分支显示了 DB2 Information Integrator 将连接 DB2_NATION 和 DB2_CUSTOMER，然后对每种情况都在远程 ORDERS 表中执行嵌套循环连接。这就意味着 DB2_NATION 和 DB2_CUSTOMER 之间的连接要执行三次，一次在 DB2 数据源上的 3 个表连接的过程中，再在剩余的两个数据源上每个各一次。在处理查询 5 中，重复执行该连接使 DB2 Information Integrator 比直接访问 servlet 运行起来更慢。
图 6. 查询 5 的 Explain 输出
一个可能提高此查询性能的方法是定义一个本地 MQT，该 MQT 在本地高速缓存 DB2_CUSTOMER 和 DB2_NATION 连接。DB2 Information Integrator 可以通过引用本地高速缓存的这两个表的“预连接”来透明地替换在 DB2_CUSTOMER 和 DB2_NATION 之间重复的远程连接。同样的 MQT 对查询 4 也能派得上用场，因为那个查询也是引用相同的连接。
查询 5 示例了这样一种情况：在这种情况下直接访问 servlet 可以实现一个对于 DB2 Information Integrator 不可行的执行策略，并且获得性能上的优势。然而，必须将这种优势与大幅增加的开发成本、潜在的错误以及缺少可扩展性（定制直接访问 servlet 中实现联邦查询所固有的）进行权衡。
|
|
我们发现编写 J2EE servlet 来实现多数据源分布式查询（无论是否使用 DB2 Information Integrator）的经历是很有趣的。不管是使用 DB2 Information Integrator 还是在定制 J2EE servlet 中通过使用直接数据访问实现查询，我们尝试在两种情况下都获得最好的性能。对于定制直接访问 servlet，我们尝试编写符合最佳实践的高效代码，但是并不是尝试实现过于复杂或专用的策略以处理查询。然而我们遇到了几种情况，发现哪怕是比较简单查询，想要编写出在产生正确结果的同时还能获得合理性能的定制直接访问 servlet 很困难，对此我们印象深刻。手工将联邦查询分解成在单独数据源上的更小的查询然后再正确地集成结果，这项作业也并不总是很简单的，而且在一些情况下我们还可能犯错误。DB2 Information Integrator 使我们不必担心，就能把每个细节都做好。
我们发现使用 DB2 Information Integrator 时要获得良好性能取决于正确的配置，在某些情况下还需要在表达联邦查询时多注意一下。根据我们在这一系列文章中描述的项目的经验，您应该考虑一下以下与性能相关的准则：
- 在定义对远程对象的昵称之前，如果可能的话请检查正在讨论的工作负载的合理索引已经在所有远程对象上定义，且已经收集了统计信息。请检查 DB2 Information Integrator 中昵称的统计信息，使用 get_stats 或是用户自己写的脚本来填写丢失的任何重要信息。
- 在 DB2 Information Integrator 中配置远程服务器定义时，请确保所记录的描述远程数据源特性的服务器选项设置是正确的。请参阅 Federated Systems Guide 以获取更多信息。
- 在多于一个远程数据源上定义昵称的 UNION ALL 视图是一种很自然的方法，它通过使用 DB2 Information Integrator 来将所有相关数据结合在一起。然而，在表达涉及昵称的 UNION ALL 视图的查询时需要特别小心。视图自身需要包含“server”列表明 UNION 中的每个元素所属的数据源。涉及一个以上的类似于 UNION ALL 视图的查询一般需要在“server”列上增加连接谓词以获得正确结果和良好性能。请参阅本文中提供的示例。
- 昵称上 UNION ALL 视图连接的良好性能取决于将此连接成功地变换为叠加连接的 UNION ALL 变换（通过 DB2 Information Integrator 优化器）。然而，仅当 UNION ALL 视图和查询涉及的数据源的数目不是太大时，此变换才可以工作。对于要连接 n 个 UNION ALL 视图的查询，而且每个视图中涉及的昵称在 m 个不同的数据源上，只要 m n≤ 36，都可以成功地进行变换。例如，一个有四个 UNION ALL 视图的连接，每个视图引用的昵称在两个数据源上，这个变换是可以成功进行的，因为 2 4=16≤ 36。涉及 UNION ALL 视图的连接若不能成功地变换为连接的 UNION，它执行起来可能非常糟糕。
- 对于涉及 LIKE 谓词和参数标记的查询要特别当心。为了使这类谓词能叠加至远程数据源，参数标记需要强制转换为要与其比较的列类型。有关详细信息，请参阅查询 1。
- 您可以指出，通过使用 db2_maximal_pushdown服务器选项，只要在功能上可能将查询操作叠加至远程数据源是令人期待的。这在 Federated Systems Guide中有详细描述。在我们的项目中并没有使用此选项，但是如果小心使用，它在实际中还是非常有用的。请记住要在联邦查询中获得良好性能，叠加查询处理至远程数据源通常是唯一的并且是最重要的因素。
- 即使没有任何本地数据存储在 DB2 Information Integrator 实例中，也还有足够多的本地数据源需要配置以处理本地排序、散列连接和建立临时表。请检查 SHEAPTHRES、SORTHEAP、临时表空间以及与临时表空间相关联的缓冲池的设置。
- DB2 Information Integrator 通过使用昵称上定义的具体查询表（MQT）来启用本地高速缓存远程数据。虽然没有在本文中专门论述，但使用那些不经常更改的小的远程数据对象（或是其中的连接）的本地副本也是最有效的提高性能的技术之一。
总体上，我们非常高兴地发现在五个查询示例中，使用 DB2 Information Integrator 的 J2EE servlet 通常可以达到和直接访问 servlet 相当的性能，前提是只要正确地配置产品并且按照上面的准则来表达联邦查询。DB2 Information Integrator 极大地简化了 J2EE 组件开发（此开发必须访问并合并多个数据源上的信息），而且同时还提供了良好的查询性能。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/374079/viewspace-130355/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/374079/viewspace-130355/
扫一扫
695
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
