让你的系统充满活力：DB2 for IBM i 的7条调优建议
快些！快些！每个人都希望自己的应用程序和报表能够运行地更快！
我们都曾遇到过这种情况，用户和老板经常会问我们能否使运行在IBM
i上的应用程序或者报表跑的更快。通常，这些应用和报表程序花了大量的时间来处理存储在DB2
for i上的数据。这篇文章介绍了7条DB2 for i的性能调优建议，用来提高运行在IBM
i上应用程序或者报表程序的速度。
本文中所介绍DB2 for
i的性能调优建议主要是针对基于SQL数据库访问接口的查询。由于已经存在大量关于RPG以及COBOL程序的性能调优资料，这些是使用原生、传统记录级访问DB for i的方式，现在我们重点研究基于SQL的性能调优。
对于本文中所提到的这几条性能调优建议，我们没有详细介绍每条SQL性能调优建议的所有细节。这里有两个方法来深入学习DB2 for
i 的SQL性能调优，一是参加 DB2 for i SQL Performance
Workshop 课程培训。另一方法是与DB2 for i 杰出小组的专家一起工作，这个小组是IBM STG
Lab Services and Training的一部分。
建议1：增加对SQE的使用
IBM在DB2 V5R2中第一次发布SQL 查询引擎（SQE）。SQE的设计初衷是专门为运行在DB2 for i上的SQL查询提供性能优势。V5R2之前，所有的SQL语句都是在Classic
查询引擎（CQE）进行处理。CQE是长久以来的集成的关系数据库引擎，并随着时间不断改进以支持SQL查询。专为支持SQL语言数据库引擎SQE的发布使IBM具备了业界领先的优化技术，这些技术为长时间运行的、复杂的SQL查询提供了重要的性能改进。
这些先进的优化技术之一就是IBM
i 7.1所支持的AQP，AQP是一种自学习优化技术，能够使DB2
for i实时调整查询计划，例如，在SQL查询正在运行时改变连接顺序或者使用新的索引，这些实时调整不会打断应用程序的运行。作为SQE的超级性能优化技术，很多用户已经看到了这一技术使他们的查询从数小时降到了几十分钟，当然，访问计划发生了变化。
使你的SQL语句被SQE处理的最简单方式就是下载最新版本的IBM i 操作系统。这是由于从V5R2开始，每一版本都增加了SQE所支持的SQL 查询类型。这一发展随着IBM i 7.1的发布达到了极点，该版本第一次支持了所有的SQL语句。这些新增加的支持中最重要的是在IBM i 7.1 中SQE能够处理FROM从句中引用逻辑文件的SQL语句。在最近的一次会议中，一位升级到7.1的客户分享了一个查询从6小时降到20分钟的案例，这个复杂查询的FROM子句引用了逻辑文件，所以SQL语句从CQE处理改为了SQE处理，这一小小的改变带来了性能上巨大的变化。用户只需要安装最新版本的IBM
i 操作系统就可以获得这些支持。
IBM i 6.1也可以通过申请最新的数据库补丁包支持FROM子句引用简单的逻辑文件。
SQE还不支持Query/400以及OPNQRYF。因此，将这些查询改为SQL是增加对SQE使用以及提高系统性能的另一方法。一些用户通过使用IBM DB2
Web Query for i 完成了查询的转换。除了提供现代图形报表功能，DB2
Web Query 在它的报表引擎中使用了SQL语句，这就增加了对SQE的使用。如果你还在使用第三方报表工具，并且希望能够利用到SQE所提供的性能优势，那么你应该调查一下这些报表工具是使用SQL还是传统的查询接口，例如QQQQRY接口还是在使用CQE。
运行在IBM i 6.1之前操作系统的应用程序以及执行DDS创建SQL查询的数据库的应用，可以通过设置QAQQINI 的IGNORE_DERIVED_INDEX
增加对SQE的使用。每一个版本SQE对SQL语句类型的支持可以在SQE Overview presentation找到。
除了更快的性能，你也许还在考虑如何确定是哪个引擎处理了SQL语句。现在正是解决这个问题的最好时机，因为下面的建议包含了这些信息。
建议2：使用“新”DB2 性能工具
如图1所示，DB2 for i 提供了大量的分析以及对SQL进行性能调优的工具。这些工具大部分位于System
i Navigator客户端（以前称为iSeries Navigator 和Operations Navigator）。其实这些工具并不是新的，它们早在V5R4就已经存在了，甚至一些是在v5r4之前就存在。请注意图一虚线下面的几个工具，这些虚线下的工具被视为非战略性的，因此IBM并没有对他们进行改进增强。这些非战略性工具对于理解SQE的查询计划并没有太多作用，因为它们在V5R2 SQE发布后就没有进行改进。所以，还在依赖这些工具的用户得到的DB2
for i 的信息是不完整或者不准确的。例如，这些工具都不能看到SQE查询优化器提供的索引建议。
不幸的是，很多用户还在依赖这些传统的工具——尤其是，作业调试日志信息。图2包含了一个涉及连接两个表（目录和订单表）的查询，DB2将调试信息写入作业日志的例子。
现在，比较绿屏下的作业日志信息与图三Visual
Explain所展示出的图形化查询计划。首先，绿屏下的作业日志信息并不包含产生该信息的SQL语句，然而在Visual Explain下部会显示相应的SQL语句。根据调试信息，需要你自己找到与SQL语句相关的信息。如果用这种方法分析，对于分析执行大量SQL语句的查询，我们只能祝你好运了。第二，作业日志不能指出该查询是使用了SQE还是CQE，而对于Visual Explain, 你只需要点击“Final Select”按钮并滚动右侧的属性窗口到底部就可以看到。如果没有使用SQE,
这个属性窗口还会包含使用CQE的原因。第三，Visual Explain所包含的信息要比作业日志信息更加详细，作业日志不包含DB2所使用的任何临时数据结构，例如hash表。Hash表通常意味着对于查询优化器没有合适的索引。说到索引，作业日志信息通常只包含一个推荐索引。对于下面这个查询，SQE的Index Adviser实际上建议了三个索引。仅仅比较这两个工具的几个不同之处，就可以非常清楚地了解到继续使用作业调试信息分析SQL性能是效率很低的。
有些人也许会认为调试信息是分析传统的Query/400以及OPNQRYF接口的唯一工具，然而，这是不对的。从DB2 for i v6r1 开始，增强了SQL Performance Monitor使它能够收集Query/400 以及OPNQRYF接口的信息，从而能够进一步使用Visual Explain来分析查询计划。正如图1所示，Visual Explain只能根据详细的SQL
Performance Monitor或者Plan Cache数据生成。
为了收集生成Visual
Explain的相关数据,SQL Performance Monitor是一个trace-level的能够跟踪执行SQL 语句和查询所有相关信息的工具。也许正如你所期望的，其中一个标准就是跟踪SQE和CQE的使用，这些踪迹在图4中所示，这个分析窗口通过右击所收集的monitor
选择Analyze task产生。
下面是IBM 红皮书以及可下载优化工具列表，这些可以是你开始使用DB2
for i这些新的性能工具的开始。
如果要求用户开始使用DB2
for i的性能工具来更有效以及高效的分析和调优他们系统的SQ004性能，也许是非常苛刻的。如果你不是特别关心IBM 提供的DB2 性能调优工具，那你只需要知道Centerfield Technology 也提供了关于DB2 for i 的一系列现代、图形化的性能调优工具。
建议3：减少不必要或者昂贵的临时索引创建
所有的关系数据库管理系统都是依赖索引技术加快SQL查询请求的性能。因此，一个缺失的索引或者非最优的索引是通常引起SQL性能问题的原因之一。从这一点考虑，DB2 for i 与其他数据库一样。
然而，DB2 for i 通过自动创建临时索引提高SQL查询性能来补救上述问题。这是一个很好/不好的解决方式。好的方面是指临时索引能够提高SQL查询的性能，坏的方面是指在实际运行前会需要大量的系统资源以及时间来创建临时索引。如果这些临时索引是CQE创建的情况就会更糟了，因为CQE创建的临时索引不能在不同用户以及不同的查询之间共享。如果有50个用户运行相同的查询，这些查询会使CQE创建的临时索引作为创建查询计划的一部分，那么这50个用户就会创建各自版本的临时索引。如果这些用户中有一个用户运行了与原来查询有细微变化但是还是能从这个临时索引中获益的查询，这个由CQE创建的临时索引也不能再重用了，CQE会为第二个查询重新创建一个临时索引。
虽然由SQE创建的临时索引相对于CQE来说在重用性方面要好很多，但是他们终究还是临时对象，还是存在一些问题。所有IBM i 的临时索引在服务器关闭的那一刻都会删除。所以，一次系统IPL后 依赖SQE临时索引的SQL 查询性能会慢很多，因为他们所依赖的临时索引都不存在了。而且SQE查询优化器通常会要求多次执行这个查询直到它评估出创建一个临时索引的代价。
创建一个固定索引来取代临时索引是避免临时索引所带来问题的最简单的方式。Visual
Explain和SQL Performance Monitor是确定临时索引使用信息的最好的工具。图5包含了一个使用了临时索引作为其查询计划一部分的Visual
Explain。SQE创建的临时索引是以“MTI”作为前缀，而CQE是以“TEMP”作为前缀。
图4所示的SQL Performance Monitor分析结果窗口包含了临时索引创建的数量，本例中的monitor 收集信息结果显示了创建的一个临时索引。
建议4：减少无效的Table Scans
查询优化器在查询计划中使用Table
scan 通常意味着没有索引或者索引是次优化的。在完美的情况下，只有需要处理表中大部分数据的时候才会使用table
scan。理想情况下是需要处理的记录数占全部数据的20%到30%以上时使用table scan。
然而，有些查询优化器在没有索引可用的情况下会被迫选择使用table
scan去获得很少行的数据。显而易见，扫描一个有一百万行数据的表而只取其中的1千行数据会浪费大量的系统资源。
对使用了table
scan的查询计划比较查询表的全部行数与选择的行数识别出是否是低效的table
scan. 图5 显示了使用Visual Explain 可以方便的识别出查询优化器使用Table Scan。Visual Explain中Table
Scan标志的细节窗口显示了对选择行数的估计值以及表的全部行数。如果选择的行数占总记录数的比例很小，那么这就是一个低效的table
scan。这时，你就需要分析查询优化器中可用的Index以及DB2 产生的索引建议。
建议5：评估索引建议
DB2 for i 包含了一系列工具能够方便快捷的查看索引建议。V5R4之前，只能通过像database monitor这样的工具并在查询或者应用程序运行之前手工触发查看，即使做了这些，分析人员还需要从大量的作业日志以及database
monitor文件中抽取出关于索引建议的信息。为了结束这一繁杂的工作，DB2
V5R4增强了Index Advisor的功能，使它能够一直运行并且在任何一台IBM i系统上都可以使用索引建议。这一功能被称为系统级的Index
Advisor 。有了这一支持，只需鼠标单击就可以查看索引建议了。如图6所示，只需要右击System i Navigator 树状目录中的数据库名字并且选择Index Advisor就可以看到图7所示的信息。系统级的索引建议也可以通过过滤器提供模式级或者表级的索引建议报告，我们只需在这些模式或者表上右击并选择Index
Advisor任务即可。
图7所示的索引建议是一个定制化的索引输出窗口，这个图形化接口允许这些列重新排序或者是移除一些列。在默认的输出窗口中，你也许通常会滑动到输出窗口的最右侧来查看索引的建议次数以及最后一次索引建议时间。通常，你需要对这些索引排序来确定对系统有较大影响的索引。
这个接口同样可以方便地创建一个被推荐的索引，只需要简单的右击就可以。但最好还是不要盲目的创建这些索引，因为CQE 和SQE会对你的查询和数据做一些假设，而这些假设有可能并不准确。另外，索引建议并不能够分析整个查询。SQE提供的索引建议的一个优势是其所提供的索引建议比CQE提供的索引建议更加稳定。
查询优化器永远不能像你一样理解你的数据和查询请求。使用索引建议作为性能调优建议的一个指导，但是在执行前对索引建议进行评估。一个用来评估索引建议的优秀的资源就是DB2 for i 的Indexing Strategy white paper 。这份文件对生成索引建议的特性进行了深入分析，还提供了创建理想索引的属性细节。
建议6:评估索引的使用
Index Evaluator工具提供了查询优化器使用Index的所有信息。这些信息能够帮助你决定哪些索引是你应该继续保持的，哪些索引是可以考虑删除以减少系统维护的。这个索引列表还包含了创建索引的SQL语句，主键约束，唯一性约束，外键约束，Keyed 逻辑文件以及Keyed 物理文件。
如图8所示，通过在表上右键选择Show Indexes 产生Index Evaluator
图8窗口所示的信息将索引的使用信息这一列移到前面。通常，用户会滑动到最右侧查看索引的使用情况。前两列包含了索引的名字和类型（不管这是一个SQL索引还是Keyed 逻辑文件等），接下来的四列包含了能够帮助你从优化器角度评估一个索引的价值。Last Query Use 和Last Query Statistics Use 包含了最后一次用来执行或者统计的时间戳。相应的，Query
Use Count和Query Statistics Use列可以看到这些索引不同情况下的使用频率，这些使用参数不能通过Display
File Description (DSPFD)命令查看到。
不要匆忙的删除那些使用率比较低的索引来提高系统的性能，请先检查你的查询频率。一个用来提高季度经济报表性能的索引使用率要比一个用来执行每日报表的索引低很多。
建议7：检查优化目标
尽管没有提供允许你直接控制优化器行为的设置，但是还是有一些能够使你影响查询优化器的设置条件。这些设置允许你提供关于环境或者应用程序使用的信息。优化目标是这些你能够控制影响SQL性能的设置之一，优化目标提供给DB2 for i查询优化器结果集要被怎样处理的信息，优化目标的值为“FIRSTIO”和“ALLIO”。
例如，我们利用对一个返回1000条数据的查询应用这一属性。优化目标设置为*FIRSTIO意味着优化器会使用能够尽快返回这1000条结果集中第一个结果集（例如，前20条数据）的查询计划，这种情况适用于用户希望尽快看到结果的交互式应用中。*FIRSTIO的设置通常会导致优化器避免使用那些复制数据到临时数据结构（例如hash表）的访问计划，这是由于复制数据到临时数据结构意味着直到数据复制全部完成才能返回给应用程序。
*ALLIO的设置使优化器使用一系列能够尽快返回所有1，000行数据的查询计划。这种设置更适合于批处理作业，因为批处理作业没有响应时间的要求；相反，它会使用能够尽快返回所有结果的查询计划。使用*ALLIO意味着如果复制数据的访问方法是返回这1,000行结果集最快的访问计划，那么优化器就会选择这种访问计划。
为了使你更好的理解优化目标的设置，举一个实际的例子。你需要粉刷一座房子，这里有两个方法，油漆刷子和油漆喷雾器。很明显如果要粉刷整座房子，那么使用油漆喷雾会使最好的方法（正如
*ALLIO)。然而，如果你被告知只需要粉刷房子外面一个三尺见方的区域呢？是的，你只需要蘸几次刷子就可以完成这个区域的粉刷。使用油漆刷子（例如
*FIRSTIO）的方法会比使用油漆喷雾先完成任务，因为刷子的启动成本比喷雾的启动成本低很多。取决于总体目标，启动开销可能提高也可能降低整体性能。
此外，使用Visual
Explain和SQL Performance Monitor能够很好的帮助你查看执行SQL语句的优化目标。动态SQL接口（例如 ODBC,JDBC等）的缺省优化目标是*FIRSTIO
，静态SQL查询以及使用扩展动态连接属性的应用缺省优化目标是*ALLIO。
DB2 for i提供了设置优化目标的几种方法。一个是使用QAQQINI中的OPTIMIZATION_GOAL。另一个是使用ODBC,ADO.NET以及JDBC等这些包含在IBM i操作系统中的中间件的连接属性。还可以在SQL语句中使用OPTIMIZE FOR n ROWS这个从句设置优化目标。n的值如果比较小会导致使用*FIRSTIO,如果值比较大那就会使用*ALLIO.
让你的系统充满活力
正如本文所讲，你已经了解到IBM DB2 for i所提供的一系列提高SQL查询请求性能的方法。现在，轮到你利用它们优化你的查询或应用了，你的老板和用户将会非常感谢你所作的优化。
原文作者：Kent Milligan
译者： Wang Jia
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
下面是IBM 红皮书以及可下载优化工具列表，这些可以是你开始使用DB2
for i这些新的性能工具的开始。
