自动收集各个生产库的日常信息。自己提需求，设计，开发，测试，文档

字数 15047阅读 5935评论 39赞 1

作为DBA，日常的工作肯定少不了，日巡检，周巡检等等，主要需要收集的信息有各个库上表空间增长率，缓冲池使用率，表大小的增长率，锁的多少，REORG信息等等。<div>每周都要手动做，有点小头疼，虽然学习收集这些数据的方法固然重要，但是如果掌握了后就希望能把时间节省下来，用在分析这些数据上。加上自己有点小开发经验，于是使用SHELL,AWK各种咚咚，现用现学，狠狠整了3天终于搞定了，通过联邦，把所有的需要分析的数据都拿到本地入库。有点小成就感哦~</div><div>下面把主要部分拿出来和大家分享</div><div><br></div><div><div>总体思路</div><div>把远程数据库里的信息拿到本地数据库有两种方式：</div><div>1。把数据写入文件，再导入本地库</div><div>2。通过数据库联邦，直接在本地访问远程数据库</div><div>由于第一个步骤比较复杂，考虑到开发应尽量简单，避免多余的步骤带来的无法预料的问题。所以使用联邦的方式。</div><div>通过联邦的方式，访问远程数据库上的系统视图，来收集周巡检需要的数据</div><div><br></div><div><br></div><div>一共使用到的脚本</div><div>init.sql------------包含了所有库的编目语句，创建联邦的语句，以及建表语句</div><div>reorgchk_97.awk--------考虑到reorgchk输出的结果不易直接入库处理，并需要挑选出*的，需要用这个脚本进行结构化数据处理</div><div>check_weekly.sh-----自动周巡检的主程序</div></div><div><br></div><div><br></div><div><br></div><div><div>以下分别描述这三个部分</div><div>*********************************************check_weekly.sh***********************************************************************************</div><div><div>#author:wangpeng</div><div>#date:2012-04-10</div><div>#purpose:the main program of weekly check</div><div>#取得当前日期，格式为20120411</div><div>date=`date +%Y%m%d`</div><div>printf "n--------------------Begin Collection $date--------------------n">>"check_weekly.log"</div><div>#定义一个数组，存放需要收集信息的库在本地编目的数据库别名，用空格分开</div><div>a=(SAMPLE)</div><div>#相应的库上的用户名和密码，都用空格分开</div><div>b=(db2inst1 db2inst1)</div><div>db2 connect to check</div><div>#根据数据库的个数，进行循环收集信息</div><div>printf "n第一阶段，收集各个数据库的视图信息n">>"check_weekly.log"</div><div>num=${#a[*]}</div><div>for((i=0;i<num;i++)){</div><div><span class="Apple-tab-span" style="white-space:pre">	</span>printf "n${a[i]}n">>"check_weekly.log";</div><div><span class="Apple-tab-span" style="white-space:pre">	</span>db2 "INSERT INTO WEEKLY.DBM_CFG SELECT $date,'${a[i]}',NAME,VALUE,VALUE_FLAGS FROM ${a[i]}_DBMCFG">>"check_weekly.log";</div><div><span class="Apple-tab-span" style="white-space:pre">	</span>db2 "INSERT INTO WEEKLY.DB_CFG SELECT $date,'${a[i]}',NAME,VALUE,VALUE_FLAGS FROM ${a[i]}_DBCFG">>"check_weekly.log";</div><div><span class="Apple-tab-span" style="white-space:pre">	</span>db2 "INSERT INTO WEEKLY.TBSP_SIZE SELECT $date,'${a[i]}',A.TBSP_ID,A.TBSP_NAME,A.TBSP_TYPE,A.TBSP_CONTENT_TYPE,A.TBSP_PAGE_SIZE,B.TBSP_TOTAL_PAGES,B.TBSP_USABLE_PAGES,B.TBSP_USED_PAGES,B.TBSP_FREE_PAGES FROM ${a[i]}_TBSP A,${a[i]}_TBSP_PART B WHERE A.TBSP_ID=B.TBSP_ID">>"check_weekly.log";</div><div><span class="Apple-tab-span" style="white-space:pre">	</span>db2 "INSERT INTO WEEKLY.TB_SIZE select $date,'${a[i]}',TBSPACEID,TBSPACE,TABLEID,TABNAME,NPAGES from ${a[i]}_TB where TBSPACE not in('SYSCATSPACE','SYSTOOLSPACE') or (tbspace is null and npages<> -1)  order by TBSPACEID,TABLEID">>"check_weekly.log";</div><div><span class="Apple-tab-span" style="white-space:pre">	</span>db2 "INSERT INTO WEEKLY.BP_SIZE SELECT $date,'${a[i]}',BP_NAME,DATA_LOGICAL_READS,DATA_PHYSICAL_READS,INDEX_LOGICAL_READS,INDEX_PHYSICAL_READS,CASE WHEN (DATA_LOGICAL_READS+DATA_PHYSICAL_READS+INDEX_LOGICAL_READS+INDEX_PHYSICAL_READS)=0 THEN 0 ELSE (DATA_LOGICAL_READS+INDEX_LOGICAL_READS)*1.0/(DATA_LOGICAL_READS+DATA_PHYSICAL_READS+INDEX_LOGICAL_READS+INDEX_PHYSICAL_READS)*100 END FROM ${a[i]}_BP">>"check_weekly.log";</div><div><span class="Apple-tab-span" style="white-space:pre">	</span>db2 "INSERT INTO WEEKLY.LOCKS SELECT $date,'${a[i]}',DEADLOCKS,LOCK_WAITS,LOCK_WAIT_TIME,LOCK_TIMEOUTS,TOTAL_SORTS,SORT_OVERFLOWS FROM ${a[i]}_SNAPDB">>"check_weekly.log";</div><div>}</div><div>db2 terminate</div><div>#第二部分，收集REORGCHK信息，因为REORGCHK只能通过REORGCHK命令和一个FUNCTION得到，联邦无法实现，所以采用落地文件的形式收集信息</div><div>#遍历连接所有的库</div><div>for((i=0,j=0;i<num;i++,j+=2)){</div><div>db2 connect to ${a[i]} user ${b[j]} using ${b[j+1]}</div><div>#把REORGCHK的结果集进行AWK处理，结果分成两股流，分别进入文件reorgchk_tab.del，reorgchk_ind.del</div><div>printf "n开始${a[i]}的REORGCHK，并修改数据格式">>"check_weekly.log"</div><div>db2 reorgchk current statistics|awk -f reorgchk_97.awk</div><div>db2 terminate</div><div>#文件入库</div><div>db2 connect to check</div><div>printf "n开始${a[i]}的REORGCHK结果集入库n">>"check_weekly.log"</div><div>if[! -f "reorgchk_tab.del"] then</div><div>db2 "import from reorgchk_tab.del of del messages import_tab.msg insert into WEEKLY.REORGCHK_TAB(TABNAME,CARD,OV,NP,FP,ACTBLK,TSIZE,F1,F2,F3,REORG)">>"check_weekly.log"</div><div>#对刚入库的数据还要进行进一步的处理，把"schema.table"分开放入两个字段</div><div>db2 "update WEEKLY.REORGCHK_TAB set DATE_KEY=$date,DB_NAME='${a[i]}',TABLE_SCHEMA=substr(TABNAME,1,posstr(TABNAME,'.')-1),TABNAME=substr(TABNAME,posstr(TABNAME,'.')+1,length(TABNAME)-posstr(TABNAME,'.')) WHERE DATE_KEY IS NULL">>"check_weekly.log"</div><div>else </div><div>printf "nreorgchk_tab.del为空">>"check_weekly.log"</div><div>fi</div><div>if[! -f "reorgchk_tab.del"] then</div><div>db2 "import from reorgchk_ind.del of del messages import_ind.msg insert into WEEKLY.REORGCHK_IND(TABNAME,INDNAME,INDCARD,LEAF,ELEAF,LVLS,NDEL,KEYS,LEAF_RECSIZE,NLEAF_RECSIZE,LEAF_PAGE_OVERHEAD,NLEAF_PAGE_OVERHEAD,PCT_PAGES_SAVED,F4,F5,F6,F7,F8,REORG)">>"check_weekly.log"</div><div>db2 "update WEEKLY.REORGCHK_IND set DATE_KEY=$date,DB_NAME='${a[i]}',TABLE_SCHEMA=substr(TABNAME,1,posstr(TABNAME,'.')-1),TABNAME=substr(TABNAME,posstr(TABNAME,'.')+1,length(TABNAME)-posstr(TABNAME,'.')),INDEX_SCHEMA=substr(TABNAME,1,posstr(TABNAME,'.')-1),INDNAME=substr(TABNAME,posstr(TABNAME,'.')+1,length(TABNAME)-posstr(TABNAME,'.')) WHERE DATE_KEY IS NULL">>"check_weekly.log"</div><div>else </div><div>printf "nreorgchk_tab.del为空">>"check_weekly.log"</div><div>fi</div><div>#清空reorgchk_tab.del and reorgchk_ind.del</div><div>>reorgchk_tab.del</div><div>>reorgchk_ind.del</div><div>db2 terminate</div><div>}</div><div>printf "n--------------------END Collection $date--------------------nnnn">>"check_weekly.log"</div><div>*************************************************check_weekly.sh*******************************************************************************</div><div><br></div><div><br></div><div><br></div><div><br></div><div>***********************************************reorgchk_97.awk*********************************************************************************</div><div><br></div><div>#author:wangpeng</div><div>#date:2012-04-10</div><div>#purpose:filter reorgchk's result</div><div>#REORGCHK的结果集分为两部分，表和索引</div><div>BEGIN{RS="Table:"}</div><div>{</div><div>#第一部分，处理带*的表</div><div>if ( NF == 11 && $NF ~ /*/)</div><div>        printf $1","$2","$3","$4","$5","$6","$7","$8","$9","$10","$11"n">"reorgchk_tab.del";</div><div>#第二部分，处理带*的索引，并且同时取出这个索引对应的表名</div><div>else if ( NF >= 20){</div><div>        for(i=20;i<=NF;i+=19){</div><div>                if($i ~ /*/){</div><div><span class="Apple-tab-span" style="white-space:pre">												</span>#不考虑分区表的分区索引</div><div>                        if($(i-18)!~"Partition"){</div><div>                        #输出带*的索引对应的表名</div><div>                        printf $1",">"reorgchk_ind.del";</div><div>                        for(j=i-17;j<=i;j++){</div><div>                        <span class="Apple-tab-span" style="white-space:pre">				</span>#循环打印各个字段，用,分割，以便入库，最后一个字段后面不要,</div><div>                                printf $j>"reorgchk_ind.del";</div><div>                                if(j!=i)printf ",">"reorgchk_ind.del";</div><div>                        }</div><div>                        printf "n">"reorgchk_ind.del";</div><div>                        }</div><div>                }</div><div>        }</div><div>}</div><div>}</div><div>*************************************************reorgchk_97.awk*******************************************************************************</div></div></div><div><br></div><div><br></div><div><div><br class="Apple-interchange-newline">*************************************************init.sql*******************************************************************************</div><div>------编目</div><div>db2 catalog tcpip node db2inst1 remote 10.19.21.12 server 60000</div><div>db2 catalog db sample as sample at node db2inst1</div><div><br></div><div><br></div><div><br></div><div><br></div><div>--创建联邦</div><div>create wrapper drda</div><div>create server SAMPLE type db2/udb version '9.7' wrapper drda authid "db2inst1" password "db2inst1" options(add dbname ' SAMPLE  ')；</div><div>create user mapping for db2inst1 server ubtdb options(add remote_authid 'db2inst1',add remote_password 'db2inst1')；</div><div>create nickname SAMPLE_DBMCFG for SAMPLE.SYSIBMADM.DBMCFG;</div><div>create nickname SAMPLE_DBCFG for SAMPLE.SYSIBMADM.DBCFG;</div><div>CREATE NICKNAME SAMPLE_TBSP FOR SAMPLE.SYSIBMADM.SNAPTBSP;</div><div>CREATE NICKNAME SAMPLE_TBSP_PART FOR SAMPLE.SYSIBMADM.SNAPTBSP_PART;</div><div>CREATE NICKNAME SAMPLE_TB FOR SAMPLE.SYSCAT.TABLES;</div><div>CREATE NICKNAME SAMPLE_BP FOR SAMPLE.SYSIBMADM.BP_HITRATIO;</div><div>CREATE NICKNAME SAMPLE_SNAPDB FOR SAMPLE.SYSIBMADM.SNAPDB;</div><div><br></div><div><br></div><div>DROP TABLE WEEKLY.DBM_CFG;</div><div>CREATE TABLE WEEKLY.DBM_CFG(</div><div>DATE_KEY  INT,</div><div>DB_NAME  VARCHAR(20),</div><div>NAME  VARCHAR(30),</div><div>VALUE VARCHAR(50),</div><div>VALUE_FLAGS  VARCHAR(10)</div><div>)IN USERSPACE1 INDEX IN IXSPACE;</div><div><br></div><div>DROP TABLE WEEKLY.DB_CFG ;</div><div>CREATE TABLE WEEKLY.DB_CFG (</div><div>DATE_KEY  INT,</div><div>DB_NAME  VARCHAR(20),</div><div>NAME  VARCHAR(30),</div><div>VALUE VARCHAR(50),</div><div>VALUE_FLAGS  VARCHAR(10)</div><div>)IN USERSPACE1 INDEX IN IXSPACE;</div><div><br></div><div>DROP TABLE WEEKLY.TBSP_SIZE;</div><div>CREATE TABLE WEEKLY.TBSP_SIZE(</div><div>DATE_KEY  INT,</div><div>DB_NAME  VARCHAR(20),</div><div>ID SMALLINT,</div><div>NAME  VARCHAR(30),</div><div>TYPE  VARCHAR(10),</div><div>CONTENT VAR VARCHAR(10),</div><div>PAGE_SIZE INT,</div><div>TOTAL INT,</div><div>USABLE INT,</div><div>USED INT,</div><div>FREE INT</div><div>)IN USERSPACE1 INDEX IN IXSPACE;</div><div><br></div><div>DROP TABLE WEEKLY.TB_SIZE;</div><div>CREATE TABLE WEEKLY.TB_SIZE(</div><div>DATE_KEY  INT,</div><div>DB_NAME  VARCHAR(20),</div><div>TBSPACEID SMALLINT,</div><div>TBSPACE  VARCHAR(30),</div><div>TABID SMALLINT,</div><div>TABNAME  VARCHAR(50),</div><div>NPAGES INT</div><div>)IN USERSPACE1 INDEX IN IXSPACE;</div><div><br></div><div>DROP TABLE WEEKLY.BP_SIZE;</div><div>CREATE TABLE WEEKLY.BP_SIZE(</div><div>DATE_KEY  INT,</div><div>DB_NAME  VARCHAR(20),</div><div>BP_NAME  VARCHAR(20),</div><div>POOL_DATA_L_READS BIGINT,</div><div>POOL_DATA_P_READS BIGINT,</div><div>POOL_INDEX_L_READS BIGINT,</div><div>POOL_INDEX_P_READS BIGINT,</div><div>HITRATIO decimal(25,11)</div><div>)IN USERSPACE1 INDEX IN IXSPACE;</div><div><br></div><div>DROP TABLE WEEKLY.LOCKS;</div><div>CREATE TABLE WEEKLY.LOCKS(</div><div>DATE_KEY  INT,</div><div>DB_NAME  VARCHAR(20),</div><div>DEADLOCKS INT,</div><div>LOCK_WAITS BIGINT,</div><div>LOCK_WAIT_TIME BIGINT,</div><div>LOCK_TIMEOUTS INT,</div><div>TOTAL_SORTS BIGINT,</div><div>SORT_OVERFLOWS INT</div><div>)IN USERSPACE1 INDEX IN IXSPACE;</div><div><br></div><div>DROP TABLE WEEKLY.REORGCHK_TAB;</div><div>CREATE TABLE WEEKLY.REORGCHK_TAB(</div><div>DATE_KEY  INT,</div><div>DB_NAME  VARCHAR(20),</div><div>TABLE_SCHEMA  VARCHAR(30),</div><div>TABNAME VARCHAR(50),</div><div>CARD VARCHAR(20),</div><div>OV VARCHAR(20),</div><div>NP VARCHAR(20),</div><div>FP VARCHAR(20),</div><div>ACTBLK VARCHAR(20),</div><div>TSIZE VARCHAR(20),</div><div>F1 VARCHAR(20),</div><div>F2 VARCHAR(20),</div><div>F3 VARCHAR(20),</div><div>REORG VARCHAR(10)</div><div>)IN USERSPACE1 INDEX IN IXSPACE;</div><div><br></div><div>DROP TABLE WEEKLY.REORGCHK_IND;</div><div>CREATE TABLE WEEKLY.REORGCHK_IND(</div><div>DATE_KEY  INT,</div><div>DB_NAME  VARCHAR(20),</div><div>TABLE_SCHEMA  VARCHAR(30),</div><div>TABNAME VARCHAR(50),</div><div>INDEX_SCHEMA  VARCHAR(30),</div><div>INDNAME VARCHAR(50),</div><div>INDCARD VARCHAR(20),</div><div>LEAF VARCHAR(20),</div><div>ELEAF VARCHAR(20),</div><div>LVLS VARCHAR(20),</div><div>NDEL VARCHAR(20),</div><div>KEYS VARCHAR(20),</div><div>LEAF_RECSIZE VARCHAR(20),</div><div>NLEAF_RECSIZE VARCHAR(20),</div><div>LEAF_PAGE_OVERHEAD VARCHAR(20),</div><div>NLEAF_PAGE_OVERHEAD VARCHAR(20),</div><div>PCT_PAGES_SAVED VARCHAR(20),</div><div>F4 VARCHAR(20),</div><div>F5 VARCHAR(20),</div><div>F6 VARCHAR(20),</div><div>F7 VARCHAR(20),</div><div>F8 VARCHAR(20),</div><div>REORG VARCHAR(10)</div><div>)IN USERSPACE1 INDEX IN IXSPACE;</div><div><br></div><div>*************************************************init.sql*******************************************************************************</div><div>--一共8张表，每个表都是由DATE_KEY来定位日期，格式20120416，用DB_NAME来定位数据库，查询时可以通过这两个字段来进行定位</div><div>--以下列出大体分析方法</div><div><br></div><div>1.如果要对比两次日期的DBM里的参数值是否有变化</div><div>SELECT * FROM (</div><div>SELECT A.DATE_KEY,A.DB_NAME,A.NAME,A.VALUE A_VAL,B.VALUE B_VAL FROM WEEKLY.DBM_CFG A ,WEEKLY.DBM_CFG B WHERE A.DB_NAME=B.DB_NAME AND A.NAME=B.NAME AND A.DATE_KEY=20120411 AND B.DATE_KEY=20120411)</div><div>WHERE A_VAL<>B_VAL</div><div><br></div><div><br></div><div>2.如果要对比两次日期的DB里的参数值是否有变化</div><div>SELECT * FROM (</div><div>SELECT A.DATE_KEY,A.DB_NAME,A.NAME,A.VALUE A_VAL,B.VALUE B_VAL FROM WEEKLY.DB_CFG A ,WEEKLY.DB_CFG B WHERE A.DB_NAME=B.DB_NAME AND A.NAME=B.NAME AND A.DATE_KEY=20120411 AND B.DATE_KEY=20120411)</div><div>WHERE A_VAL<>B_VAL</div><div><br></div><div>3.要对比表空间使用的增长情况，根据表结构，以及记录的增量方式，如果需要很好的展现出结果，我使用了递归来做，这里的比较系数是一周比较一次，如果想改大环比周期，修改以下的B.NUM+为B.NUM+4（即1个月，4个星期）</div><div>USED记录目前的使用页数，CUR记录每次递归的过程中当前表空间使用的页数，B.CUR-A.USED就是这一天的页数减去上个周期的页数</div><div>USED是当前的页数，INCREASED里面第一个是上次增长的，第二个是上上次增长的，以此类推……</div><div>with A(DATE_KEY,DB_NAME,ID,NAME,TYPE,CONTENT,PAGE_SIZE,TOTAL,USABLE,FREE,USED,NUM) AS</div><div>  (SELECT DATE_KEY,DB_NAME,ID,NAME,TYPE,CONTENT,PAGE_SIZE,TOTAL,USABLE,FREE,USED,ROW_NUMBER() OVER(PARTITION BY DB_NAME,ID ORDER BY DATE_KEY DESC) NUM FROM WEEKLY.TBSP_SIZE </div><div>  )</div><div>  ,B(DATE_KEY,DB_NAME,ID,NAME,TYPE,CONTENT,PAGE_SIZE,TOTAL,USABLE,FREE,USED,CUR,INCREASED,NUM) AS</div><div>  (SELECT DATE_KEY,DB_NAME,ID,NAME,TYPE,CONTENT,PAGE_SIZE,TOTAL,USABLE,FREE,USED,USED,CAST ('' AS VARCHAR(1000)) INCREASED,NUM FROM A WHERE NUM=1</div><div>  UNION ALL </div><div>  SELECT B.DATE_KEY,B.DB_NAME,B.ID,B.NAME,B.TYPE,B.CONTENT,B.PAGE_SIZE,B.TOTAL,B.USABLE,B.FREE,B.USED,A.USED,B.INCREASED||','||(B.CUR-A.USED) INCREASED,B.NUM+1 FROM A,B WHERE </div><div>  A.DB_NAME=B.DB_NAME AND A.ID=B.ID AND A.NUM=B.NUM+1 AND B.NUM<3--这里设置层数，即只查看最近3周的增长情况,考虑到以后数据量越来越大，递归会很慢，</div><div> --但我们只关注最近几周的增长情况</div><div>  )</div><div>  SELECT DATE_KEY,DB_NAME,ID,NAME,TYPE,CONTENT,PAGE_SIZE,TOTAL,USABLE,FREE,USED,INCREASED FROM B WHERE NUM=(SELECT MAX(NUM) FROM B)</div><div><br></div><div>上面的是记录每次增长的量，这个是记录每次的量,HISTORY里面的值是从现在到以前每周的值</div><div>with A(DATE_KEY,DB_NAME,ID,NAME,TYPE,CONTENT,PAGE_SIZE,TOTAL,USABLE,FREE,USED,NUM) AS</div><div>  (SELECT DATE_KEY,DB_NAME,ID,NAME,TYPE,CONTENT,PAGE_SIZE,TOTAL,USABLE,FREE,USED,ROW_NUMBER() OVER(PARTITION BY DB_NAME,ID ORDER BY DATE_KEY DESC) NUM FROM WEEKLY.TBSP_SIZE </div><div>  )</div><div>  ,B(DATE_KEY,DB_NAME,ID,NAME,TYPE,CONTENT,PAGE_SIZE,TOTAL,USABLE,FREE,USED,HISTORY,NUM) AS</div><div>  (SELECT DATE_KEY,DB_NAME,ID,NAME,TYPE,CONTENT,PAGE_SIZE,TOTAL,USABLE,FREE,USED,CAST (USED AS VARCHAR(1000)) HISTORY,NUM FROM A WHERE NUM=1</div><div>  UNION ALL </div><div>  SELECT B.DATE_KEY,B.DB_NAME,B.ID,B.NAME,B.TYPE,B.CONTENT,B.PAGE_SIZE,B.TOTAL,B.USABLE,B.FREE,B.USED,B.HISTORY||','||A.USED HISTOR,B.NUM+1 FROM A,B WHERE </div><div>  A.DB_NAME=B.DB_NAME AND A.ID=B.ID AND A.NUM=B.NUM+1 AND B.NUM<3</div><div>  )</div><div>  SELECT DATE_KEY,DB_NAME,ID,NAME,TYPE,CONTENT,PAGE_SIZE,TOTAL,USABLE,FREE,USED,HISTORY FROM B WHERE NUM=(SELECT MAX(NUM) FROM B)</div><div><br></div><div><br></div><div><br></div><div><br></div><div><br></div><div><br></div><div><br></div><div>4.如3.这是对表增长进行查看</div><div>with A(DATE_KEY,DB_NAME,TBSPACEID,TABID,TABNAME,NPAGES,NUM) AS</div><div>  (SELECT DATE_KEY,DB_NAME,TBSPACEID,TABID,TABNAME,NPAGES,ROW_NUMBER() OVER(PARTITION BY DB_NAME,TBSPACEID,TABID ORDER BY DATE_KEY DESC) NUM FROM WEEKLY.TB_SIZE  WHERE  NPAGES<>-1</div><div>  )</div><div>  ,B(DATE_KEY,DB_NAME,TBSPACEID,TABID,TABNAME,USED,CUR,INCREASED,NUM) AS</div><div>  (SELECT DATE_KEY,DB_NAME,TBSPACEID,TABID,TABNAME,NPAGES,NPAGES,CAST ('' AS VARCHAR(1000)) INCREASED,NUM FROM A WHERE NUM=1</div><div>  UNION ALL </div><div>  SELECT B.DATE_KEY,B.DB_NAME,B.TBSPACEID,B.TABID,B.TABNAME,B.USED,A.NPAGES,B.INCREASED||','||(B.CUR-A.NPAGES) INCREASED,B.NUM+1 FROM A,B WHERE </div><div>  A.DB_NAME=B.DB_NAME AND A.TBSPACEID=B.TBSPACEID AND A.TABID=B.TABID AND A.NUM=B.NUM+1  AND B.NUM<3</div><div>  )</div><div>  SELECT DATE_KEY,DB_NAME,TBSPACEID,TABID,TABNAME,USED,INCREASED FROM B  WHERE NUM=(SELECT MAX(NUM) FROM B)</div><div><br></div><div>5.缓冲池。例如查看命中率小于70%的数据</div><div>select *</div><div>  from "WEEKLY"."BP_SIZE" WHERE HITRATIO<70 AND HITRATIO<>0;</div><div><br></div><div>6.通过REORGCHK的结果，分为表和索引两个表（以下只是初步实例，具体要做的时候请仔细测试生成的结果）</div><div>表的REORG</div><div> select 'db2 reorg table '||TABLE_SCHEMA||'.'||TABNAME||' inplace allow write access start'  </div><div>  from "WEEKLY"."REORGCHK_TAB" where DATE_KEY=20120417 AND DB_NAME='UBTDB' AND REORG LIKE '%*%'</div><div><br></div><div>表的RUNSTATS</div><div> SELECT 'db2 runstats on table '||TABLE_SCHEMA||'.'||TABNAME||' with distribution and detailed indexes all allow write access '</div><div>  from "WEEKLY"."REORGCHK_TAB" where DATE_KEY=20120417 AND DB_NAME='UBTDB' AND REORG LIKE '%*%'</div><div><br></div><div>索引的REORG</div><div>select 'db2 reorg index '||INDEX_SCHEMA||'.'||INDNAME||' for table '||TABLE_SCHEMA||'.'||TABNAME</div><div>  from "WEEKLY"."REORGCHK_IND" where DATE_KEY=20120417 AND DB_NAME='UBTDB' AND REORG LIKE '%*%'</div><div> </div><div>索引的RUNSTATS</div><div> select 'db2 runstats on table '||INDEX_SCHEMA||'.'||INDNAME||' for sampled detailed indexes '||TABLE_SCHEMA||'.'||TABNAME||'allow write access'</div><div>  from "WEEKLY"."REORGCHK_IND" where DATE_KEY=20120417 AND DB_NAME='UBTDB' AND REORG LIKE '%*%'  </div><div><br></div><div>7.死锁和锁等待的增长</div><div>select *</div><div>  from "WEEKLY"."LOCKS" where DB_NAME='UBTDB';</div><div>  </div><div>基于这些表，应该可以有更多的维度分析，这里只是做了几个比较常规的分析，大家可以集思广益</div><div><br></div></div>

测试

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

添加新评论39 条评论

lcc322其它ACC
2013-12-09 12:44

感谢无私分享学习中

taylor840326数据库管理员中国百盛集团
2013-04-09 15:32

itsbegin: 好帖啊！不藏私，支持！不断总结才能不断进步～

同支持。

wp28556259软件架构设计师CMBC
2013-01-24 16:37

steve25: 分享一个关于抓DB CFG改变的方法,使用db2diag -gi FUNCTION:="config/install",EDUNAME:=db2agent可以抓住那些做过的改变，不过前提是db2diag.log不能被

非常感谢，这个很不错呢

steve25数据库管理员IBM
2012-10-19 11:34

分享一个关于抓DB CFG改变的方法,使用db2diag -gi FUNCTION:="config/install",EDUNAME:=db2agent可以抓住那些做过的改变，不过前提是db2diag.log不能被truncated.

zdlqs技术支持用友软件股份有限公司电信事业部
2012-07-12 22:12

看起来很厉害的样子啊！~学习学习思路~

yeexun软件开发工程师北京宇信易诚科技有限公司
2012-07-03 08:27

收藏下，学习学习！

foxnet2005工程师IBM
2012-06-27 23:33

好贴

阳光普照技术经理hlmc
2012-05-28 22:00

学习了

wp28556259软件架构设计师CMBC
2012-05-28 08:55

哈哈，谢谢，这两个存储过程确实方便了很多，学习了。之前找了半天没找到

cyx2yc7软件开发工程师IBM canada
2012-05-27 12:00

New update:
一般awk command 不支持字符串作为分隔符，gwak支持。所以命令改为 “db2 reorgchk current statistics |sed 's/Table:/@/' |awk -f reorgchk_v97.awk”。相应的改为“RS=@".

2. 可能用DB2提供的Stored Procedure "reorgchk_TB_stats" and "reorgchk_IX＿STATS　加一个grep　ｃｏｍｍａｎｄ更简单．．

uc804数据库运维工程师企业
2012-05-23 16:31

学习了

yritech数据库管理员国信网联
2012-05-22 14:07

不错，学习了

wp28556259软件架构设计师CMBC
2012-05-17 08:48

cyx2yc7: awk 'BEGIN{RS="Table:"} {print $2}' 是为了测试用 Table:作为行分隔符是否可行，你可以看出来第二个field 数据很杂乱。

Table: OSCDM.CDCOMPLTARGET

if ( NF == 11 && $NF ~ /*/)，这个就是判断，这一行是否符合规则，符合了才从中提取信息

cyx2yc7软件开发工程师IBM canada
2012-05-17 00:45

awk 'BEGIN{RS="Table:"} {print $2}' 是为了测试用 Table:作为行分隔符是否可行，你可以看出来第二个field 数据很杂乱。

Table: OSCDM.CDCOMPLTARGETTP
18 0 1 1 - 1260 0 - 100 ---
Table: OSCDM.CDCOMPOPTP
3 0 1 1 - 102 0 - 100 ---

第二个field应该是18 和3。

wp28556259软件架构设计师CMBC
2012-05-16 10:55

cyx2yc7: 输出太诡异了？
db2 reorgchk current statistics |awk 'BEGIN{RS="Table:"} {print $2}' |more

statistics:
Pages)

F1
DB2HUBI.AC
0
DB2HUBI.ADEFUSR

awk 'BEGIN{RS="Table:"} {print $2}' |more
你这是什么意思呢，你按照我的脚本应该可以的，我刚测过。调用方法
db2 reorgchk current statistics|awk -f reorgchk_97.awk

cyx2yc7软件开发工程师IBM canada
2012-05-16 10:18

输出太诡异了？
db2 reorgchk current statistics |awk 'BEGIN{RS="Table:"} {print $2}' |more

statistics:
Pages)

F1
DB2HUBI.AC
0
DB2HUBI.ADEFUSR
DB2HUBI.ADVISE_INDEX

cyx2yc7软件开发工程师IBM canada
2012-05-16 09:34

reorgchk_97.awk 在aix上结果不对，王鹏能不能查一下？