DB2 统计信息与重组相关操作

--收集表和索引的统计信息，包括数据分布
runstats on table on all columns with distribution and detailed indexes all

--收集表和索引的统计信息
runstats on table for indexes all
db2 "runstats on table db2inst1.t1 on all columns and indexes all"

--使用伯努利算法抽样统计，适用于数据仓库，下面是伯努利算法10%的抽样统计
db2 "runstats on table tablespace bernoulli(10) "

--抽样统计信息
db2 "runstats on table db2inst1.table1 and indexes all tablesample system (10)"

--This command collects statistics on the table db2user.employee with distribution statistics on only 30 percent of the rows.
db2 "runstats on table db2user.employee with distribution tablesmple bernoulli(30)"

If you want to collect the same statistics repeatedly on a table and do not want to retype the command options,
you simply specify the use profile option.

db2 "runstats on table db2user.employee use profile
"
This command collects statistics on db2user.employee using the options saved in the statistics profile for that table.

db2 "runstats on table db2user.employee
with distribution on columns (empid, empname)
set profile only
"
Notice that the option will only set the profile; the runstats command will not run.
If you need to modify a previously registered statistics profile, use the update profile only
option. Similarly, this option will only update the profile without running the runstats
command. If you want to update the profile as well as update the statistics, use the update profile option instead.

db2 "runstats on table db2user.employee
with distribution default
num_freqvalues 50 num_quantiles 50
update profile
"

Starting in DB2 9, automatic statistics collection allows DB2 to automatically run the runstats
utility in the background to ensure the most current database statistics are available.
The automatic statistics collection feature is enabled by default for any new database created in DB2 9.
You can disable it by explicitly setting the AUTO_RUNSTATS database configuration parameter to OFF.
In order to minimize the performance impact of automatic statistics collection,
throttling of the runstats utility can be used to limit the amount of resources consumed by
the utility. When the database activity is low, the utility runs more aggressively.
On the other hand, when the database activity increases,
the resources allocated to executing runstats are reduced.
Here is how to specify the level of throttling.

db2 "runstats on table db2user.employee
with distribution default
num_freqvalues 50 num_quantiles 50
util_impact_priority 30
"

The acceptable priority value ranges from 1 to 100. The highest priority (meaning unthrottled) is
represented by the value 100 and the value 1 represents the lowest priority. Default priority level is 50.

--runstats 选项
allow write access 默认选项

--查看表是否收集过统计信息
db2 "select char(tabname,40) as tabname, stats_time from syscat.tables where stats_time is null"

--查看索引是否收集过统计信息

db2 "select char(indname,30) as indexname,char(tabname,30) as "TabName",nleaf,nlevels,stats_time 

 from syscat.indexes"

db2 -v "reorgchk update statistics on table all"


 --什么时候需要运行 runstats
Some situations when updating statistics would be beneficial
– After data has been loaded into a table and appropriate indexes have been created
– After creating a new index on a table
– After a table has been reorganized with the REORG utility
– After a table and its indexes have been significantly modified through update, insert, or delete operations

Improving the performance of RUNSTATS
– Collect basic statistics only for relevant columns. E.g.: columns used to join tables
– Consider specifying only those columns for which data distribution  statistics should be collected. 
– Use row-level or page-level sampling when running RUNSTATS
– Use throttling option to limit the performance impact of RUNSTATS  execution

--收集全库的统计信息脚本
#----------------------------------------------------------------------------#
#!/bin/ksh93

if [ "$#" < 3 ] ; then
  echo "Usage:$0 DB_NAME DB_USER USER_PASSWORD"
  exit 1
if

DB=$1
DB_USER=$2
DB_PWD=$3

db2 connect to $DB user $DB_USER using $DB_PWD
db2 "select rtrim('RUNSTATS ON TABLE ' || rtrim(tabschema) || '.' || tabname || ' on all columns with distribution on all columns and sampled detailed indexes all allow write access;')
from syscat.tables
where type='T' " > createRunstats.txt
grep -i runstats  createRunstats.txt > runstats_detailed.sql
db2 -tvf runstats_detailed.sql

#----------------------------------------------------------------------------#

runstats 最佳实践：
1）当表数据量发生了很大变化， 如 load 或 reorg，新增索引后，需要收集统计信息
2）空闲时运行 runstats
3）对大表的某些字段做统计信息收集，并可以做抽样统计
4）系统表也需要做 runstats
5）表和索引要同时收集统计信息，并重新 rebind

以下情况 reorg 表，可能会提高性能：
1）表中许多行被删除，reorg后占用空间会降低
2）发生行溢出的的表，主要是 varchar 变长字段的表
3）按照某个索引重新组织表数据的物理顺序，如聚集索引
4）启用表数据压缩时，可通过 reorg 建立字典表，并对表数据进行压缩

以下情况 reorg 索引，可能会提高性能：
1）表数据删除后，导致很多索引页变为空
2）减少索引层次
3）去除伪删除的行和页


reorg 最佳实践：


--判断表或索引是否需要重组？
1）reorgchk
F1，F2，F3有*标记，则表需要重组
F4-F8有*标记，则索引需要重组

db2 reorgchk on schema db2inst1

2）sysibmadm.snaptab 视图 针对表多
overflow_accesses/rows_read > %3，则表需要重组
实例监控器开关： update dbm cfg using dft_mon_table on
db2 "select  substr(tabname,1,18) tabname,rows_read, overflow_accesses
  from sysibmadm.snaptab
  where (rows_read>999) and ((overflow_accesses*100)/(rows_read+1) > 3)
"

--离线reorg表
支持 allow read access(默认),allow no access
use 选项指定临时表空间，进行重组

*4个阶段
1)scan-sort 根据reorg指定的索引对表进行扫描，排序
2)build 表数据构建
3)replace(copy):替换原有数据
4)index rebuild:基于新数据，重建索引

离线reorg,根据index index-name 选项指定根据哪个索引进行重组，如果没有指定索引，表重组时不关心数据在物理存储的顺序。
如果表上有cluster index ,即使没有指定索引，默认也按聚集索引顺序重组表。

--离线reorg example
db2 reorg table db2inst1.t1
b2 create temporary tablespace tempts1
db2 "create index i1 on db2inst1.t1(id)"
db2 reorg table db2inst1.t1 index i1 allow read access use tempts1


--查看 reorg 的过程
1）sysibmadm.snaptab
db2 "select
       substr(tabname, 1, 15) as tab_name,
       substr(tabschema, 1, 15) as tab_schema,
       reorg_phase,
       substr(reorg_type, 1, 20) as reorg_type,
       reorg_status,
       reorg_completion,
       dbpartitionnum
     from sysibmadm.snaptab_reorg
     order by dbpartitionnum"
2）通过快照
db2 get snapshot for tables on testdb
current counter / max counter 来预测完成的百分比
3）db2pd -d testdb -reorg
4）db2 list history reorg all for testdb


--查看 reorg 进程 
db2 list applications show detail | grep -i db2reorg

--在线reorg表,只是维护索引，不会重建索引（聚集索引除外）
db2 reorg table db2inst1.t1 inplace allow write access

--在线重组索引
db2 reorg indexes all for table db2inst1.t1

db2 list history reorg all for testdb
或
查看db2diag.log


--查看包
db2 list packages for schema db2inst1

--对单个包重绑定
db2 rebind package package-name

--对所有包重绑定
db2rbind testdb  -l db2rbind.log all

--删除程序包缓冲区 

db2 flush package cache dynamic 

--查看模式 db2inst1 下无效的包
db2 "select pkgname,valid from syscat.packages where pkgschema='DB2INST1' and valid != 'Y' "

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22661144/viewspace-1477207/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22661144/viewspace-1477207/