使用DB2look 重新创建优化器访问计划3-2

   来源：赛迪网    作者：huawuque

除了以上设置，还应在数据库配置中注意下列配置：

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 .org with distribution 
and indexes all 

db2 runstats on table .sales with distribution 
and indexes all 

db2 terminate

现在，通过执行 EXPLAIN.DDL 文件生成 EXPLAIN 表，

该文件在 \sqllib\misc 目录下：

db2 connect to sample 

db2 -tvf \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.