[摘要]简介 在作为 DB2 UDB 支持分析员（Support Analyst）工作时，我经常从处理优化器或查询计划问题的客户那里听到下列问题： “我如何在接手生产环境的测试环境中重新创建相同的查询访问计...
简介
在作为 DB2 UDB 支持分析员（Support Analyst）工作时，我经常从处理优化器或查询计划问题的客户那里听到下列问题：
“我如何在接手生产环境的测试环境中重新创建相同的查询访问计划呢？”
我们常常需要将生产环境复制到测试环境中，包括为查询分析目的重新创建相同的访问计划。
例如，在生产中，您可能会遇到使用糟糕访问计划的查询所导致的性能问题，并且需要在测试系统上复制该访问计划以尝试一些不同的策略，例如操作统计数据，修改优化级别，对 DB2 注册表变量尝试不同的设置等等，以便提高性能。
在理想的世界中，您需要让测试环境尽可能接近地匹配生产。也就是说，您需要在两个环境中使用完全相同的硬件、操作系统维护级别和配置、DB2 级别和配置，以及在测试中使用与生产中相同的数据。然而，并非总是可以达到这种理想情况。如果生产环境具有极其大量的数据，您或许就没有容量来保存生产系统的测试副本。
db2look 实用程序可以用于达到该目标，即使您无法复制所有的生产细节。
本文将解释如何可以在测试系统上模拟生产系统，而无需真正的数据来重新创建查询计划问题。该功能将帮助您调试查询和理解访问计划问题，且不打断生产环境中的工作。但是请注意，如果需要测试结果访问计划的执行，则仍然需要将尽可能多的数据从生产环境装入测试环境。测试系统和生产系统之间的差别仍然总是可能足以导致测试上的执行特性不匹配生产上的。这部分的分析（性能调优）既是一门科学，又是一门艺术。
优化器或查询编译器领域中的其他问题，例如 SQL0901N 错误或实例崩溃，也可以使用本文中所解释的方法来重新创建。您可以尝试各种策略，如测试最新的补丁包（如果系统是处于更老的补丁级别），尝试不同的优化级别、不同的注册表变量等等，以便查看这些修改是否将解决问题。
让我们看一看 db2look 中用于达到该目标的选项。
db2look 命令及其选项
下面是用于从生产系统捕获所需信息的命令：
清单 1. 重新创建优化器问题的命令
db2look -d <dbname> -l -o storage.out
db2look -d <dbname> -f -fd -o config.out
db2look -d <dbname> -e -a -m -o db2look.out
db2look -d <dbname> -e -a -m -t table1 table2 .... tableX -o table.ddl
现在，让我们更详细地看一看这些 db2look 命令选项。
生成缓冲池、表空间和数据库分区组信息
db2look -d <dbname> -l -o storage.out
下面是对以上 db2look 命令中所用选项的描述：
-d：数据库名 —— 该选项必须指定。
-l：生成数据库布局。这是用于数据库分区组、缓冲池和表空间的布局。
-o：将输出重新定向到给定的文件名。如果未指定 -o 选项，然么输出将为标准输出（stdout），通常是输出到屏幕。
-l 选项对于模拟生产环境十分重要。理想情况下，您需要具有相同的缓冲池、数据库分区组（如果处于多分区环境中）和表空间信息（包括临时表空间）。但是，如果您受到了内存约束，无法分配生产中所具有的大型缓冲池，那么就使用 db2fopt 命令。我稍后将在本小节中更详细地讨论该命令。
并非总是可以在测试中设置与生产中相同的表空间。例如，可能设置了大型设备，却无法灵活地在测试中创建相同的设备大小。或者，可能根本无法在测试环境中获得单独的表空间设备。此外，或许无法在测试中设置与生产中相同的路径。需要适当地更改路径、设备和文件以适应测试环境。
下面是优化器为表空间所使用的重要信息。这就是您需要确保在测试和生产中相同的信息。（注意：这里所展示的数字是一个例子。您应在测试中使用与您生产中相同的设置。）
PREFETCHSIZE 16
EXTENTSIZE 16
OVERHEAD 12.670000
TRANSFERRATE 0.180000
如果生产中表空间是“由数据库管理的”，那么在测试中也应该是“由数据库管理的”。如果它在生产中是“由系统管理的”，那在测试中也应该是这样的方式。
注意：如果这是具有多个物理分区（MPP）的系统，那么测试中数据库分区组中的分区数目就必须相同。然而，物理机器的数目不必相同。测试和生产中整个 MPP 环境中逻辑分区的数目必须相同。
生成配置参数和注册表变量
db2look -d <dbname> -f -fd -o config.out
这里，我将使用下列参数：
-f：提取配置参数和注册表变量。如果指定了该选项，就会忽略 -wrapper 和 -server 选项。
-fd：为 opt_buffpage 和 opt_sortheap 生成 db2fopt 语句，以及其他配置和注册表设置。
该命令的输出如下所示：
清单 2. db2look 命令的示例输出
$ db2look -d sample -f -fd
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: SKAPOOR
-- This CLP file was created using DB2LOOK Version 8.2
-- Timestamp: Sat Mar 26 00:13:36 EST 2005
-- Database Name: SAMPLE
-- Database Manager Version: DB2/6000 Version 8.2.2
-- Database Codepage: 819
-- Database Collating Sequence is: UNIQUE
CONNECT TO SAMPLE;
--------------------------------------------------------
-- Database and Database Manager configuration parameters
--------------------------------------------------------
UPDATE DBM CFG USING cpuspeed 6.523521e-07;
UPDATE DBM CFG USING intra_parallel NO;
UPDATE DBM CFG USING federated NO;
UPDATE DBM CFG USING fed_noauth NO;
!db2fopt SAMPLE update opt_buffpage 50000;
!db2fopt SAMPLE update opt_sortheap 10000;
UPDATE DB CFG FOR SAMPLE USING locklist 1000;
UPDATE DB CFG FOR SAMPLE USING dft_degree 1;
UPDATE DB CFG FOR SAMPLE USING maxlocks 10;
UPDATE DB CFG FOR SAMPLE USING avg_appls 1;
UPDATE DB CFG FOR SAMPLE USING stmtheap 2048;
UPDATE DB CFG FOR SAMPLE USING dft_queryopt 5;
---------------------------------
-- Environment Variables settings
---------------------------------
!db2set DB2_ANTIJOIN=yes;
!db2set DB2_INLIST_TO_NLJN=yes;
COMMIT WORK;
CONNECT RESET;
TERMINATE;
-f 和 -fd 选项是用于提取配置参数和注册表变量的关键选项，而优化器将在访问计划阶段使用这些配置参数和环境。在上面的 清单 2 中，请注意下列 -fd 选项所产生的输出：
!db2fopt SAMPLE update opt_buffpage 50000;
!db2fopt SAMPLE update opt_sortheap 10000;
db2fopt 命令告诉优化器为“缓冲池大小（Buffer pool size）”使用指定的值，而非将可用缓冲池变量的页面加起来。（db2exfmt 输出中的缓冲池大小将在下面的 缓冲池大小 一节中进行进一步的讨论。）例如，假设由于测试系统上的内存约束，您无法获得大型的缓冲池，并且希望将大小配置得相同，实际上却不是真正有这么大。使用将生成必要的 db2fopt 命令的 -fd 选项来告诉优化器使用指定大小，而非基于对该数据库可用的缓冲池进行计算。
[page_break]正如将在下面的 排序堆 一节中所看到的，它的工作方式与排序堆的相同。
如果您是一名 DBA，就可能会使用 DB2 SQL Explain Tool（db2exfmt）来获得对于 SQL 访问计划的理解。db2exfmt 工具用于格式化解释表的内容。如果您在生产中使用 db2exfmt 查看一个访问计划的输出，就会注意到计划顶部的下列内容。（注意：这些参数通常是由 db2look 输出中的 -f 和 -fd 选项所选择的，除了 dbheap 设置之外）。
清单 3. db2exfmt 的示例输出
Database Context:
----------------
Parallelism: None
CPU Speed: 6.523521e-07
Comm Speed: 100
Buffer Pool size: 50000
Sort Heap size: 10000
Database Heap size: 5120
Lock List size: 1000
Maximum Lock List: 10
Average Applications: 1
Locks Available: 7849
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 1
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
如果您稍稍深入查看 db2exfmt 的输出，就在访问计划之后，您将看到是否具有影响优化器计划的注册表设置。
注意：另外，遗憾的是，db2look -f 并非列出了所有相关的注册表变量。您将需要添加那些遗漏的。一般来说，您测试系统上的注册表变量设置应与生产系统上的相同，或者尽可能接近。
清单 4. 影响访问计划的注册表设置
1) RETURN: (Return Result)
Cumulative Total Cost: 57.6764
Cumulative CPU Cost: 191909
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 5.37264
Cumulative Re-CPU Cost: 134316
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 26.9726
Estimated Buffer pool Buffers: 2
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v8.1.0.80 : s041221
ENVVAR : (Environment Variable)
DB2_ANTIJOIN=yes
DB2_INLIST_TO_NLJN = yes
STMTHEAP: (Statement heap size)
2048
创建数据定义语言（DDL）
下列 db2look 命令创建了 DDL 以复制所有数据库对象，以及配置和统计信息。
db2look -d <dbname> -e -a -m -o db2look.out
这里，我们使用了下列参数：
-a：为所有的创建器（creator）生成统计数据。如果指定了该选项，那么将忽略 -u 选项。
-e：提取复制数据库所需的 DDL 文件。该选项生成包含了 DDL 语句的脚本。该脚本可以在另一数据库上运行以重新创建数据库对象。
-m：以模拟模式运行 db2look 实用程序。该选项生成包含了 SQL UPDATE 语句的脚本。这些 SQL UPDATE 语句捕获所有的统计数据。该脚本可以在另一数据库上运行以复制原来的那一个数据库。当指定 -m 选项时，将忽略 -p、-g 和 -s 选项。
收集数据库子集的统计数据和 DDL
为了仅仅收集某些表和相关对象的统计数据和 ddl，可使用下列命令：
db2look -d <dbname> -e -a -m -t <table1> <table2> .. <tableX> -o table.ddl
这里，我使用了下列附加参数：
-t：为特定的表生成统计数据。可以将表的最大数目指定为 30。
此外，如果您不使用 -a 选项，就可以使用 -z 选项：
-z：模式名。如果同时指定了 -z 和 -a，那么将忽略 -z。联邦区域将忽略模式名。
注意：-m 选项极其重要。该选项将从系统表收集所有统计数据。测试中的统计数据必须与生产中的相同，这些统计数据是可以在测试环境中模拟生产环境的关键。
db2exfmt 输出的更多细节
数据库管理器级的配置参数
注意：使用命令 db2 "get dbm cfg" 查看这些参数，并使用 db2 "update dbm cfg using <parameter> <value>" 更新数据库管理器的配置参数。
并行性（Parallelism）：
该参数表明是启用分区间并行性（inter-partition parallelism），还是启用内部分区并行性（intra-partition parallelism）。如果这是具有多个分区的 DPF，那么您将看到 Inter Partition Parallelism。如果这只是 SMP（启用 intra_parallel）单个节点环境，那么您将看到 Intra Partition Parallelism。如果启用了 intra_parallel，并且是多个分区的环境，您将看到该参数为 Inter and Intra partitions parallelism。最后，如果没有分区间或分区内并行性，该参数将显示 NONE。
CPU 速度（cpuspeed）：
SQL 优化器使用 CPU 速度（每条指令几微秒）来评估某些操作的执行成本。
通信速度（comm_bandwidth）：
SQL 优化器使用为通信带宽所指定的值（每秒几兆字节）来评估在分区数据库系统中的分区服务器之间执行某些操作的成本。
数据库级的配置参数
注意：使用命令 db2 "get db cfg for <dbname>" 来查看这些参数，以及使用 db2 "update db cfg for <dbname> using <parameter> <value>") 来更新数据库配置参数。
缓冲池大小（buffer pool size）：
如果使用 buffpage 作为一个缓冲池的默认值，那么 db2exfmt 输出中显示的缓冲池大小就是由 buffpage 参数决定的，或者基于 syscat.bufferpools 的内容进行计算。所显示的数目就是分配给数据库的缓冲池页面的总数目。例如，假设我们具有下列缓冲池：
表 1. 缓冲池设置
缓冲池名称大小
IBMDEFAULTBP1000
BP11000
BP24000
BPIND11000
BPIND21000
BPLONG1000
BPTEMP1000
总数：10,000
db2exfmt 输出将显示所有缓冲池中的页面总数为总的大小。在上面的例子中，就是 10,000。 注意：页面大小（Pagesize）无关紧要，仅仅是页面的数目。
如果您无法在测试中分配到与生产中相同数量的缓冲池，那么可以在 db2look 中使用 -fd 选项来使用 db2fopt 备选命令。
在 MPP 中，优化器为运行查询的节点使用总的缓冲池信息时，要按每个节点来计算 opt_buffpage。因此，该修改将仅仅应用到运行该工具的那个节点上。
排序堆大小（SORTHEAP）
该参数定义用于私有排序的私有内存页面的最大数目，或用于共享排序的共享内存页面的最大数目。
您应将之设置为与生产中相同的值。同样，通过在 db2look 中使用 -fd 选项，您将注意到：
!db2fopt SAMPLE update opt_sortheap 256;
这将重写 sortheap 配置参数，优化器也将之用作 sortheap 值。同样，在运行时真正分配的排序堆（sortheap）实际上将由数据库配置中的 sortheap 设置来决定。与 opt_buffpage 相同，如果您无法在测试系统上分配与生产系统上相同大小的排序堆（sortheap），那么可以使用 opt_sortheap。
数据库堆大小（DBHEAP）：
每个数据库都有一个数据库堆，数据库管理器使用它来代表连接到数据库上的所有应用程序。 它包含表、索引、表空间和缓冲池的控制块信息。
锁列表大小（LOCKLIST）：
该参数表示分配给锁列表的存储器大小。
最大锁列表（MAXLOCKS）：
该参数定义数据库管理器执行升级之前必须填入的应用程序所占有锁列表的百分比。
locklist 和 maxlocks 将帮助确定某扫描（索引扫描或表扫描）期间将持有的锁类型，以及隔离级别。例如，您将在计划中注意到（比如说）索引扫描操作：
IXSCAN: (Index Scan)
TABLOCK : (Table Lock intent)
INTENT SHARE
注意：如果测试系统的 db2exfmt 输出中的可用锁（Locks Available）与生产系统不同，就不要进行连接 —— 该差异不影响查询计划。
平均应用程序（AVG_APPLS）：
SQL 优化器使用该参数来帮助评估在运行时有多少缓冲池可用于所选择的访问计划中（因为连接到数据库的所有活动应用程序共享缓冲池）。
优化级别（DFT_QUERYOPT）：
查询优化类用于在编译 SQL 查询时指导优化器使用不同的优化级别。
查询深度（DFT_DEGREE）：
用于 SQL 语句的分区内部并行程度。如果设置为 ANY，优化器就对联机的实际 CPU 数目敏感。如果您使用 ANY，那么就应该将测试和生产系统上的 CPU 数目配置得相同，除非禁用分区内并行（intra_parallel）。
除了以上修改之外，还必须确保其他一些参数都相同。
保留的高频值数目（NUM_FREQVALUES）：
该参数允许您指定“高频值（most frequent values）”的数目，当在 RUNSTATS 命令上指定 WITH DISTRIBUTION 选项时，将收集该值。
保留的分位数数目（NUM_QUANTILES）：
该参数控制在 RUNSTATS 命令上指定 WITH DISTRIBUTION 选项时将收集的分位数（quantile）数目。
测试系统上的上述两个参数 NUM_FREQVALUES 和 NUM_QUANTILES 必须与生产系统中的相同，以便确保在测试系统上收集与生产中相同数目的频值数目和分位数值。
SQL 语句堆（4KB）（STMTHEAP）：
在 SQL 语句的编译期间，语句堆（statement heap）用作 SQL 编译器的工作空间。该参数指定该工作空间的大小。如果测试中的该参数小于生产中的，您就可能会开始看到 SQL0101N 消息，因为缺乏编译查询所需要的语句堆空间。如果没有足够的语句堆用于动态连接枚举，您也可能看到 SQL0437W RC=1，下降为贪婪连接枚举。
[page_break]在测试系统上重新创建优化器/查询计划问题的示例
示例 1：
OS：Windows 2000
DB2LEVEL：V8.2 Fixpack 8 ESE 单分区
测试并复制相同的 OS 和 db2level。
数据库：
生产数据库：SAMPLE
测试数据库：DUMMYDB
使用下列命令创建 Sample 数据库：db2sampl
使用下列命令创建 Dummy 数据库：
db2 create db DUMMYDB
注意：用与生产中相同的代码页、地区和排序序列创建 TEST 数据库。
生产环境：
--------------------------------------------------------
-- Database SAMPLE and Database Manager configuration parameters
--------------------------------------------------------
UPDATE DBM CFG USING cpuspeed 9.446886e-007;
UPDATE DBM CFG USING intra_parallel NO;
UPDATE DBM CFG USING federated NO;
UPDATE DBM CFG USING fed_noauth NO;
!db2fopt SAMPLE update opt_buffpage 250;
!db2fopt SAMPLE update opt_sortheap 256;
UPDATE DB CFG FOR SAMPLE USING locklist 50;
UPDATE DB CFG FOR SAMPLE USING dft_degree 1;
UPDATE DB CFG FOR SAMPLE USING maxlocks 22;
UPDATE DB CFG FOR SAMPLE USING avg_appls 1;
UPDATE DB CFG FOR SAMPLE USING stmtheap 2048;
UPDATE DB CFG FOR SAMPLE USING dft_queryopt 5;
---------------------------------
-- Environment Variables settings
---------------------------------
!db2set DB2_INLIST_TO_NLJN=yes;
!db2set DB2_HASH_JOIN=yes;
除了以上设置，还应在数据库配置中注意下列配置：
db2 get db cfg for sample > dbcfg_sample.out
Database heap (4KB) (DBHEAP) = 600
SQL statement heap (4KB) (STMTHEAP) = 2048
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20
确保在修改数据库管理器配置（dbm cfg）之后停止并启动该实例。对于 sample 数据库，按下列方式对 ORG 和 SALES 表运行 runstats：
db2 connect to sample
db2 runstats on table <schema>.org with distribution and indexes all
db2 runstats on table <schema>.sales with distribution and indexes all
db2 terminate
现在，通过执行 EXPLAIN.DDL 文件生成 EXPLAIN 表，该文件在 <install directory>\sqllib\misc 目录下：
db2 connect to sample
db2 -tvf <intall path>\EXPLAIN.DDL
db2 terminate
在名为 query.sql 的文件中保存下列命令：
connect to sample
set current explain mode explain
select * from org a, staff b where a.deptnumb=b.dept and b.dept=15
set current explain mode no
terminate
现在，按下列方式执行该文件：
db2 -tvf query.sql
上面将仅仅以解释模式编译查询。您将在屏幕上看到：
C:\>db2 -tvf query.sql
connect to sample
Database Connection Information
Database server = DB2/NT 8.2.1
SQL authorization ID = SKAPOOR
Local database alias = SAMPLE
set current explain mode explain
DB20000I The SQL command completed successfully.
select * from org a, staff b where a.deptnumb=b.dept and b.dept=15
SQL0217W The statement was not executed as only Explain information requests
are being processed. SQLSTATE=01604
set current explain mode no
DB20000I The SQL command completed successfully.
C:\>db2 terminate
DB20000I The TERMINATE command completed successfully.
使用 db2exfmt 生成访问计划，如下：
db2exfmt -d SAMPLE -g TIC -w -1 -n % -s % -# 0 -o prod_sample_exfmt.txt
检查 prod_sample_exfmt.txt 文件的内容。您将看到生成了下面的访问计划：
Access Plan:
-----------
Total Cost: 25.8823
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
4
HSJOIN
( 2)
25.8823
2
/-----+-----\
4 1
TBSCAN TBSCAN
( 3) ( 4)
12.9682 12.913
1 1
35 8
TABLE: SKAPOOR TABLE: SKAPOOR
STAFF ORG
现在，这就是您要在生产 sample 数据库上继续的计划。您需要在测试环境中模拟该计划。
从生产 sample 数据库中收集下列信息：
db2look -d SAMPLE -l -o storage.out
db2look -d SAMPLE -f -fd -o config.out
db2look -d SAMPLE -e -a -m -t ORG SALES -o table.ddl
测试环境：
修改上面将数据库从 SAMPLE 连接到 DUMMYDB 时所收集的每个文件中的数据库名。
例如，如果您查看了 3 个文件的内容，就会注意到：
CONNECT TO SAMPLE;
将它修改为：
CONNECT TO DUMMYDB;
在测试环境中接管这些文件。本例中，所有的表都是在默认的表空间 USERSPACE1 中创建的。因此，它们也应在测试系统上相同的 SMS 表空间中用 storage.out 中转储的相同配置（包括 PREFETCHSIZE、EXTENTSIZE 等）进行创建。
在 config.out 文件中进行少量修改。将下列内容：
UPDATE DB CFG FOR SAMPLE USING dft_queryopt 5;
修改为
UPDATE DB CFG FOR SAMPLE USING dft_queryopt 3;
并保存 config.out 文件。
现在，执行 storage.out、config.out 和 table.ddl，如下：
db2 -tvf storage.out > storage_output.out
db2 -tvf config.out > config_output.out
db2 -tvf table.ddl > table.out
检查输出文件以确保所有命令都成功运行了。并且按照生产环境设置中所显示的用于 SAMPLE DB 的设置来修改 DBHEAP、STMTHEAP、NUM_FREQVALUES、NUM_QUANTILES，使它们适用于 DUMMYDB。同时，检查注册表变量设置是否尽可能地相同。
使用 db2stop 和 db2start 停止并启动该实例。重新为 DUMMYDB 数据库创建解释表：
db2 connect to dummydb;
<install path>\sqllib\misc\db2 -tvf EXPLAIN.DDL
db2 terminate;
现在，对 DUMMYDB 数据库运行查询，在前面对 SAMPLE 数据库运行查询时所生成的 query.sql 文件中将数据库名从 SAMPLE 修改为 DUMMYDB。
C:\>db2 -tvf query.sql
connect to dummydb
Database Connection Information
Database server = DB2/NT 8.2.1
SQL authorization ID = SKAPOOR
Local database alias = DUMMYDB
set current explain mode explain
DB20000I The SQL command completed successfully.
select * from org a, staff b where a.deptnumb=b.dept and b.dept=15
SQL0217W The statement was not executed as only Explain information requests
are being processed. SQLSTATE=01604
set current explain mode no
DB20000I The SQL command completed successfully.
C:\>db2 terminate
DB20000I The TERMINATE command completed successfully.
[page_break]生成 db2exfmt 输出：
db2exfmt -d DUMMYDB -g TIC -w -1 -n % -s % -# 0 -o test_dummydb_exfmt.txt
检查 test_dummydb_exfmt.txt 的内容并查看访问计划：
Access Plan:
-----------
Total Cost: 25.8843
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
4
MSJOIN
( 2)
25.8843
2
/-----+-----\
1 4
TBSCAN TBSCAN
( 3) ( 5)
12.913 12.9682
1 1
8 35
TABLE: SKAPOOR TABLE: SKAPOOR
ORG STAFF
您在测试中获得了一个不同于生产中的访问计划。本例中，显然我们在测试系统上已经将 DFT_QUERYOPT（默认的查询优化）从 5 修改为 3。因此，您看到的是 Merge Join 计划，而非 Hash Join 计划，以及有一点点区别的总成本（Total Cost）。
因为这些计划不匹配（假设您不确定为什么），所以要检查 db2exfmt 输出中的配置。见 表 2。
正如您可以看到的，测试（TEST）和生产（PRODUCTION）之间的惟一区别就是优化级别（Optimization Level），我们特意将之从 5 修改为 3，只是为了显示在测试环境中复制生产访问计划为何会不成功。
本例中，您将使用下列 UPDATE 语句将 DFT_QUERYOPT 更新为 5：
UPDATE DB CFG FOR SAMPLE USING dft_queryopt 5
然后，停止并重新连接数据库。再次对 DUMMYDB 发出 query.sql，并使用 db2exfmt 命令生成访问计划。这次，您将看到相同的访问计划。否则，就进一步确保本文中所讨论的所有优化器相关的参数都是相同的。
示例 2：
该示例显示了 db2look 命令中 -m 选项的重要性。前面用 -m 选项收集的统计数据在测试和生产中应该相同。本例中，我们将看到没有正确更新统计数据时计划是如何变化的。
数据库管理器配置、数据库配置和 db2set 注册表变量与上面 示例 1 中的相同。这里的模式名是 SKAPOOR。用您的表的模式替换它。数据库是相同的，与 示例 1 中一样是 SAMPLE 和 DUMMY。这里所使用的平台和 db2level 是 AIX 5.1 和 DB2 UDB ESE V8.2，Fix pack 8，单分区。
在 sample 数据库上执行下列命令：
db2 "connect to sample"
db2 "create index name_ind on staff (name,id)"
db2 "runstats on table skapoor.staff with distribution and indexes all"
db2 "set current explain mode explain"
db2 "select name from staff where id=10 order by name"
db2 "set current explain mode no"
db2 "terminate"
使用 db2exfmt 生成访问计划。您将看到下面的访问计划：
Access Plan:
-----------
Total Cost: 0.111065
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
1
IXSCAN
( 2)
0.111065
0
35
INDEX: SKAPOOR
NAME_IND
从 sample 数据库中收集 db2look 信息：
db2look -d sample -l -o storage.out
db2look -d sample -e -a -m -t STAFF -o db2look.out
db2look -d sample -f -fd -o config.out
修改这些文件以使您连接 dummy 数据库，而非之前在上面 示例 1 中所连接的 sample 数据库。
手工修改统计数据之一。在 db2look.out 文件中搜索下列语句（请注意，模式名、TABSCHEMA 和 INDSCHEMA 可能与您的具体情况不同）：
UPDATE SYSSTAT.INDEXES
SET NLEAF=1,
NLEVELS=1,
FIRSTKEYCARD=35,
FIRST2KEYCARD=35,
FIRST3KEYCARD=-1,
FIRST4KEYCARD=-1,
FULLKEYCARD=35,
CLUSTERFACTOR=-1.000000,
CLUSTERRATIO=100,
SEQUENTIAL_PAGES=0,
DENSITY=0,
AVERAGE_SEQUENCE_GAP=0.000000,
AVERAGE_SEQUENCE_FETCH_GAP=0.000000,
AVERAGE_SEQUENCE_PAGES=0.000000,
AVERAGE_SEQUENCE_FETCH_PAGES=0.000000,
AVERAGE_RANDOM_PAGES=1.000000,
AVERAGE_RANDOM_FETCH_PAGES=0.000000,
NUMRIDS=35,
NUMRIDS_DELETED=0,
NUM_EMPTY_LEAFS=0
WHERE INDNAME = ’NAME_IND’ AND INDSCHEMA = ’SKAPOOR ’
AND TABNAME = ’STAFF’ AND TABSCHEMA = ’SKAPOOR ’;
现在，将 FIRSTKEYCARD、FIRST2KEYCARD、FULLKEYCARD 和 NUMRIDS 从 35 修改为 37。现在保存 db2look.out 文件并运行这 3 个文件：
db2 -tvf config.out > config_output.out
db2 -tvf storage.out > storage_output.out
db2 terminate
db2stop
db2start
db2 -tvf db2look.out > db2look_output.out
检查前两个文件 config_output.out 和 storage_output.out 的内容，以确保它们运行成功。现在，检查 db2look_output.out 文件的内容。您将看到下列更新语句失败了：
UPDATE SYSSTAT.INDEXES SET NLEAF=1, NLEVELS=1, FIRSTKEYCARD=37, FIRST2KEYCARD=37
, FIRST3KEYCARD=-1, FIRST4KEYCARD=-1, FULLKEYCARD=37, CLUSTERFACTOR=-1.000000, C
LUSTERRATIO=100, SEQUENTIAL_PAGES=0, DENSITY=0, AVERAGE_SEQUENCE_GAP=0.000000, A
VERAGE_SEQUENCE_FETCH_GAP=0.000000, AVERAGE_SEQUENCE_PAGES=0.000000, AVERAGE_SEQ
UENCE_FETCH_PAGES=0.000000, AVERAGE_RANDOM_PAGES=1.000000, AVERAGE_RANDOM_FETCH_
PAGES=0.000000, NUMRIDS=37, NUMRIDS_DELETED=0, NUM_EMPTY_LEAFS=0 WHERE INDNAME =
’NAME_IND’ AND INDSCHEMA = ’SKAPOOR ’ AND TABNAME = ’STAFF’ AND TABSCHEMA = ’SK
APOOR ’
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1227N The catalog statistic "37" for column "FULLKEYCARD" is out of range
for its target column, has an invalid format, or is inconsistent in relation
to some other statistic. Reason Code = "8". SQLSTATE=23521
正如您可以看到的，上面用于索引 NAME_IND 的 UPDATE 语句失败了，因为 FULLKEYCARD 大于表的基数（CARD）。正如通过 db2look.out 文件中的下列更新语句可以看到的，CARD 是 35：
UPDATE SYSSTAT.TABLES
SET CARD=35,
NPAGES=1,
FPAGES=1,
OVERFLOW=0,
ACTIVE_BLOCKS=0
WHERE TABNAME = ’STAFF’ AND TABSCHEMA = ’SKAPOOR ’;
现在，再次以解释模式运行相同的查询：
db2 "select name from staff where id=10 order by name"
并生成访问计划。您将看到它是不同的：
Access Plan:
-----------
Total Cost: 12.972
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
1
TBSCAN
( 2)
12.972
1
1
SORT
( 3)
12.9708
1
1
TBSCAN
( 4)
12.9682
1
35
TABLE: SKAPOOR
STAFF
该示例显示，如果在表上发生 WRITE 活动时运行 RUNSTATS，统计数据就可能与本示例中的不一致。因此，用于更新统计数据的 UPDATE 语句可能失败并产生 SQL1227N 错误消息。所有的 UPDATE 语句都运行成功十分重要，如果存在不一致性，就应该进行修理并重新运行。本例中，解决方案是将 KEYCARDS 和 NUMRIDS 从 37 重新修改为 35。
[page_break]示例 3：
您需要在单分区的环境中模拟生产中的整个数据库以进行测试。
注意：如果测试中的数据库名与生产中的不同，那么可能需要修改每个 db2look 输出中的数据库名。
步骤 1：使用 -l 选项收集 db2look，以收集表空间/缓冲池/数据库节点组信息。
db2look -d <dbname> -l -o storage.out
修改表空间信息以适应您的测试环境。例如，在生产中，您具有下列表空间：
------------------------------------
-- DDL Statements for TABLESPACES --
------------------------------------
CREATE REGULAR TABLESPACE DMS1 IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
PAGESIZE 4096 MANAGED BY DATABASE
USING ( FILE ’/data/dms1’20000,
FILE ’/data/dms2’20000,
FILE ’/data/dms3’20000)
EXTENTSIZE 32
PREFETCHSIZE 32
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 12.670000
TRANSFERRATE 0.180000
DROPPED TABLE RECOVERY ON;
如果测试上没有设置相同的路径，那么就要修改上面的位置。如果您仅仅计划模拟环境，而不要复制整个数据，那么就减小文件的大小，并在必要时使用较少容器。如果没有创建相同的缓冲池，那么您还可能修改缓冲池名称。缓冲池必须具有相同的页面大小（pagesize）。不要修改表空间的页面大小。一旦处理了这些并创建了数据库，就运行 storage.out 文件：
db2 -tvf storage.out
如果需要，就重新定向输出以确保都成功运行了。例如：
db2 -tvf storage.out > storage_results.out
步骤 2：从生产中收集配置和环境变量信息，并在测试系统上运行它：
db2look -d sample -f -fd -o config.out
请记住，在 MPP 环境中，这将为运行该命令的节点收集该信息。如果不同的数据库分区上的 DB2 注册表和数据库以及数据库管理器配置不同，您将需要为每个节点分别收集该信息。然而，如果测试中无法具有与生产中相同的分区，那么就从生产中执行该查询的节点中收集该信息，然后在测试中使用该信息。
请注意，如果测试中具有不同的分区数目，那么您的模拟将有所欠缺。
在测试系统上，运行 config.out 文件，如下：
db2 -tvf config.out
上面考虑到优化器将使用 db2fopt 信息来查看所分配的总的缓冲池和排序堆，现在将成为测试环境中的设置。而且，这也是在测试中由于内存约束而不具有与生产中相同的缓冲池以及排序堆时所使用的技术。同时，本文前面所讨论的配置参数以及环境变量也将进行更新。
步骤 3：当模拟整个数据库时，从生产中收集所有对象的 DDL 信息，并在测试中运行 db2look。
在生产中：
db2look -d sample -e -a -m -o db2look.out
在测试中：
db2 -tvf db2look.out
为了看到输出结果，可发出：
db2look -tvf db2look.out > db2look.results
一旦完成了以上步骤，就请确保在测试中将 dbheap 数据库配置参数设置为与生产中相同的值。
步骤 4：使用 db2exfmt 从测试和生产中获得访问计划，并确保下列内容与生产中的相同：
Database Context:
----------------
Parallelism: None
CPU Speed: 4.762804e-07
Comm Speed: 100
Buffer Pool size: 128500
Sort Heap size: 128
Database Heap size: 5120
Lock List size: 12250
Maximum Lock List: 10
Average Applications: 4
Locks Available: 78400
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 3
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 1
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
现在，查看访问计划。如果它们是相同的，那么您就成功地重新创建了访问计划。还请注意，您还应查看 db2exfmt 输出结尾以验证表空间配置是匹配的。
示例 4：
生产：MPP，4 个逻辑分区/ 16 个物理分区。
测试：MPP，4 个逻辑分区，每个逻辑分区中只有 4 台可用的物理机器。
查询中所涉及的表、视图/MQT。
本示例中，该模拟可能不会准确工作。测试和生产中的分区数目必须相同。然而，您仍可以尝试重新创建，只是它不会正确。
因此，您必须向测试环境添加 16*4=64 个分区，以便重新创建正确。测试环境中不需要 16 台物理机器；即您可以具有 4 台物理机器，每台物理机器具有 16 个逻辑分区。这由您来决定，但总共必须有 64 个逻辑分区，与生产中相同。
因此现在在进行修改向测试环境添加相同数目的逻辑分区之后，测试环境看上去将像原始的生产设置了，如下表所示。
表 3. 生产设置
数据库分区（DBPARTITION）
ALLNODES（在节点 1 到 64 上）
NODE1（节点 1 上所定义的 db 分区）
NODE2（节点 5 上所定义的 db 分区）
表空间（TABLESPACE）
TABSPACE1（DMS 使用数据库分区 ALLNODES 中定义的设备）
TABSPACE2（DMS 使用数据库分区 NODE1 中定义的 SMS）
TABSPACE3（DMS 使用数据库分区 NODE2 中定义的 DMS）
表
TABSPACE1 中的 TAB1
TABSPACE2 中的 TAB2
TABSPACE3 中的 TAB3
MQT：
TAB3 上定义的 MQT
视图：
定义的 VIEW1，包含两个表 TAB1 和 TAB2
请确保在发出查询的节点上使用 -f 和 -fd 收集 db2look，以确保从该节点和注册表设置中获取前面所讨论的缓冲池信息，以及从运行查询的节点获取 db cfg 和 dbm cfg。以我的经验，客户的所有节点通常具有相同的配置，除了缓冲池这个极其重要的设置之外。
所遵循的步骤：
步骤 1：从生产中收集存储器信息：
db2look -d <dbname> -l -o storage.out
步骤 2：修改表空间/缓冲池信息以适应这些环境。如果您没有可用的设备，那么就使用 DMS 文件容器。同样，如果您不希望在测试中使用与生产中相同数目的容器，就缩短列表并使用较少容器。但是，您同样必须确保如果生产中的表空间是 DMS 或 SMS 类型的，那么在测试中要保留相同的类型。
步骤 3：使用下列命令收集配置信息：
db2look -d <dbname> -f -fd -o config.out
步骤 4：现在，仅仅为我们感兴趣的对象收集 db2look 信息。本例中，我们需要所有相关信息，包括表 DLL、视图以与表相关的 MQT：
db2look -d <dbname> -e -a -m -t TAB1 TAB2 TAB3 -o db2look.out
一旦收集了所有这些信息并修改了表空间/缓冲池信息，就在测试环境中执行 db2look 输出文件，并且重新从生产和测试中获取 db2exfmt 输出并进行比较。
示例 5：
这是一关于在表上进行活动时在哪里收集 RUNSTATS 信息的经典示例。您将获得 SQL1227N 错误消息，并且将无法重新创建该问题，除非手工修改统计数据。
例如，该表具有一百万行记录，一个整型列上定义了主键。您运行带有分布和索引所有选项的 RUNSTATS，从而允许对表进行写访问。在获得表统计数据的时候，有 100,000 条附加记录插入了该表。因此对于表统计数据，CARD 将显示为 1,100,000。但是，在我们开始收集索引统计数据时，例如，对于整型列上所定义的主键，就插入了 10,000 条附加记录，因此，该表中的行数是 1,110,000，而主索引 FIRSTKEYCARD 将是 1,110,000。因此，您可以看到不一致性。表统计数据的 CARD 显示表中应该是 1,100,000 条记录，而主索引统计数据的 FIRSTKEYCARD 显示表中应该是 1,110,000 条记录。对于索引统计数据的更新将失败，并发出 SQL1227N rc=8 错误消息（本例中），因为索引的 FIRSTKEYCARD 大于表的 CARD。您必须手工修复这种不一致性，对于本例，就是使 FIRSTKEYCARD 等于 CARD，均等于 1,100,000，或者反过来 —— 即增加 CARD 到等于 FIRSTKEYCARD，均等于 1,110,000。
您还可能碰到许多其他的不一致性。请确保在将输出保存为文件的测试中运行带有 -m 选项的 db2look 时，检查所有的不一致性，并进行修复。这里仅仅给出了一个不一致性的例子；您可能会碰到很多其他的不一致性，这将留给用户去修复所有这些不一致性，然后重新运行 db2look，将输出重定向到文件中以确保所有更新的统计数据都运行得很好，没有任何问题。
示例 6：
在该示例中，您在生产中获得 SQL0437W rc=1 警告消息，但在测试中没有看到它。本例中，按照上面的示例重新创建该问题。请确保 STMTHEAP 是相同的。如果它是不同的（例如出于某种原因，测试中高于生产中），那么您可能就不会看到相同的警告。同样，我们所讨论的其他参数也很重要。
SQL0437W rc=2 和其他返回代码也可以按照相同的方法重新进行创建。
其他错误消息，例如 SQL0101N 和 SQL0901N 也可以使用相同的方法重新进行创建。甚至可以重新创建编译器/优化器领域中的中断。当您处于更老的补丁包级别，并需要尝试最新补丁包级别以查看是否可以避免该问题时，或者当您需要尝试不同的优化级别以查看是否将暂时克服该问题时，这就极其有用。
结束语
db2look 是一个功能极其强大的实用程序，可以用于重新创建访问计划问题以及编译器问题，如本文中所讨论的那些。一旦重新创建了该问题，您就可以测试许多可以影响性能的变量，如修改优化级别，尝试注册表变量和更新不影响生产的统计数据，以及测试新的补丁包级别。您将发现这个方便的实用程序可用于调试问题和提高查询性能。
全新的路由器不仅让你更稳定快速地连接无线网络，更可以让家中的智能设备连接在一起。
关键词：运用db2look 重新创建优化器访问计划