脚本介绍
对整个数据库的表进行重组和收集最新统计信息的脚本
Reorg脚本的生成
db2 -x "SELECT 'REORG TABLE ' || TRIM(TABSCHEMA) ||'.' || TRIM(TABNAME)||';' FROM SYSCAT.TABLES WHERE TYPE = 'T' AND TABSCHEMA NOT LIKE 'SYS%' ORDER BY TABSCHEMA, TABNAME" > db2_reorg.sql
Runstat脚本的生成
db2 -x "SELECT 'RUNSTATS ON TABLE ' || TRIM(TABSCHEMA) || '.' || TRIM(TABNAME) || ' WITH DISTRIBUTION AND DETAILED INDEXES ALL;' FROM SYSCAT.TABLES WHERE TYPE = 'T' AND TABSCHEMA NOT LIKE 'SYS%' ORDER BY TABSCHEMA, TABNAME" > db2_runstats.sql
脚本的执行
db2 -tvf db2_reorg.sql > db2_reorg.sql.out
db2 -tvf db2_runstats.sql > db2_runstats.sql.out
脚本的解释
sql语句的执行无非是生成对全部表的reorg 和runstat
db2inst1:/dbhome/db2inst1$ db2 -x "SELECT 'REORG TABLE ' || TRIM(TABSCHEMA) ||'.' || TRIM(TABNAME)||';' FROM SYSCAT.TABLES >
REORG TABLE DB2INST1.ACT;
REORG TABLE DB2INST1.ADVISE_INDEX;
REORG TABLE DB2INST1.ADVISE_INSTANCE;
REORG TABLE DB2INST1.ADVISE_MQT;
REORG TABLE DB2INST1.ADVISE_PARTITION;
REORG TABLE DB2INST1.ADVISE_TABLE;
REORG TABLE DB2INST1.ADVISE_WORKLOAD;
REORG TABLE DB2INST1.CL_SCHED;
REORG TABLE DB2INST1.DEPARTMENT;
REORG TABLE DB2INST1.EMPLOYEE;
REORG TABLE DB2INST1.EMPMDC;
REORG TABLE DB2INST1.EMPPROJACT;
REORG TABLE DB2INST1.EMP_PHOTO;
REORG TABLE DB2INST1.EMP_RESUME;
REORG TABLE DB2INST1.EXPLAIN_ACTUALS;
REORG TABLE DB2INST1.EXPLAIN_ARGUMENT;
REORG TABLE DB2INST1.EXPLAIN_DIAGNOSTIC;
REORG TABLE DB2INST1.EXPLAIN_DIAGNOSTIC_DATA;
REORG TABLE DB2INST1.EXPLAIN_INSTANCE;
REORG TABLE DB2INST1.EXPLAIN_OBJECT;
REORG TABLE DB2INST1.EXPLAIN_OPERATOR;
REORG TABLE DB2INST1.EXPLAIN_PREDICATE;
REORG TABLE DB2INST1.EXPLAIN_STATEMENT;
REORG TABLE DB2INST1.EXPLAIN_STREAM;
REORG TABLE DB2INST1.IN_TRAY;
REORG TABLE DB2INST1.OBJECT_METRICS;
REORG TABLE DB2INST1.ORG;
REORG TABLE DB2INST1.PROJACT;
REORG TABLE DB2INST1.PROJECT;
REORG TABLE DB2INST1.SALES;
REORG TABLE DB2INST1.STAFF;
REORG TABLE DB2INST1.STAFFG;
db2inst1:/dbhome/db2inst1$
追记
怎样看统计信息相关的信息呢？其实，和统计信息相关的系统表有好几个。简单方法是通过syscat.tables来查看。用以下语句可以看到这张表上一次更新统计信息的时间和上次更新统计信息时，这张表中数据的行数：
db2 "select substr(tabname,1,50), stats_time, card from siesta.tables where tabname='<tabname>' with ur"
更新统计信息的方法也有很多讲究，上面的方法是比较常用的：
db2 "runstats on table <tabschema>.<tabname> WITH DISTRIBUTION AND DETAILED INDEXES ALL"
如果表的数据很多，则需要通过采样的方法：（如下）
db2 "runstats on table <tabschema>.<tabname> with distribution and detailed indexes all table sample system(<per>)"