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

核心提示： db2 get db cfg for sample > dbcfg_sample.outDatabase heap (4KB) (DBHEAP) = 600SQL statement heap (4KB) (STMTHEAP) = 2048Number of frequent val

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 表，

该文件在 sqllibmisc 目录下：

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.