时间: 2021-07-31 作者:daque
在尝试体例上从新创造优化器/查问安置题目的示例 示例 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.