使用DB2look实用程序重新创建优化器访问计划(3)

核心提示：在测试系统上重新创建优化器/查询计划问题的示例 示例 1： OS：Windows 2000 DB2LEVEL：V8.2 Fixpack 8 ESE 单分区 测试并复制相同的 OS 和 db2level， 数据库： 生产数据库：SAMPLE 测试数据库：DUMMYDB 使用下列命令创建 Sample 数据库：db2sam

　　在测试系统上重新创建优化器/查询计划问题的示例　
示例 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.