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

新客网 XKER.COM 2007-07-30 来源： 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 <schema>.org with distribution 
and indexes all 

db2 runstats on table <schema>.sales with distribution 
and indexes all 

db2 terminate

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

该文件在 \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

共4页: 上一页 [1] [2] [3] [4] 下一页

上一篇：使用DB2look 重新创建优化器访问计划(2) 下一篇：使用DB2look重新创建优化器访问计划(4)

【收藏】【评论】【推荐】【投稿】【打印】【关闭】