DB2开启db2advis

原创

wx62e28ac222a20 2022-07-29 17:23:41 ©著作权

文章标签 python 数据库 mysql 大数据 linux 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者wx62e28ac222a20的原创作品，请联系作者获取转载授权，否则将追究法律责任

cd /home/db2inst1/sqllib/misc/
db2 connect to testdb
db2 -tvf EXPLAIN.DDL

如果不是schema用户，需要schema用户，
db2advis -d -i 1.sql -t 5 -a db2inst1/password -n db2inst1 -q db2inst1

如果是schema用户
db2advis -d -i 1.sql -t 5

db2expln
1) method 1db2expln -d -f top1.sql -t -z @ -g

2) method 2
db2expln -d -t -g -q “statement “

db2 “explain plan for select …”

db2exfmt -d -l -o /tmp/exfmt.txt
—————or——————
db2 “set current explain mode explain”
db2 “select …”
db2 “set current explain mode no”
db2exfmt -d -l -o /tmp/exfmt.txt

db2expln -d -f top1.sql(语句末不带;) -t -z @ -g
db2expln -d -t -g -q “sql statement”
db2expln -d -statement “sql statement” -g -o /tmp/expln.txt

db2 “select r.routineschema,r.routinename,d.bname,d.bschema from syscat.routines r,syscat.routinedep d where r.routineschema=’DB2INST1’
and r.routinename=’SP’ and r.specificname=d.specificname and d.btype=’K’”
db2expln -d -c -p -g -o /tmp/sp.expln

reorg table
db2 “select ‘reorg table ‘||rtrim(tabschema)||’.’||tabname||’;’ from syscat.tables where type=’T’”

reorg indexes
db2 “select ‘reorg indexes all for table ‘||rtrim(tabschema)||’.’||tabname|| ‘;’ from syscat.tables where type=’T’”

表离线reorg
db2 reorg table db2inst1.test
在线reorg
db2 reorg table db2inst1.test inplace allow write access

通过下列命令获得表或者索引的重组信息
db2 list history reorg all for dbname

其他临时表空间离线reorg
db2 "reorg table schema.tabname using <SysTMPTbs>"

只清理脏页的reorg
db2 "reorg indexes all for table schema.tabname cleanup only all"

db2 “select ‘runstats on table ‘||rtrim(tabschema)||’.’||tabname||’ with distribution and detailed indexes all;’ from syscat.tables where type=’T’”

runstats常用命令
db2 “runstats on table db2inst1.test with distribution and detailed indexes all”
收集列组统计信息
db2 “runstats on table db2inst1.test on columns((col_1,col_2)) with distribution and indexes all”
收集所有列统计分布信息，同时收集部分列列组统计信息
db2 “runstats on table db2inst1.test on all columns and columns((col_1,col_2)) with distribution and sampled detailed indexes all”
收集部分列统计分布信息，同时收集部分列列组统计信息
db2 “runstats on table db2inst1.test on columns(col_1,col_2,(col_3,col_4)) with distribution and sampled detailed indexes all”

对索引进行runstats
db2 “runstats on table schema.tabname for index schema.indname”

db2 “select ‘rebind package ‘||rtrim(pkgschema)||’.’||pkgname||’;’ from syscat.packages”
db2rbind -l db2rbind.log all

Linux,oracle