DB2快速清理海量数据最佳实践

由于公司系统数据库容量相对比较庞大，在使用传统方案清理历史数据时耗时较长，成本高昂。所以笔者采用了detach分区的方法快速清理海量数据，以下步骤均经过验证，十分靠谱且清理数据的效率惊人。现分享出来以供大家参考学习~
一、准备工作:
db2 "select distinct status from syscat.tables"
db2 "select  PKGNAME,VALID  from syscat.packages where VALID='N'"
以上表状态及packages有效性检查日志已放在目录：/home/db2inst1/fzh/20140521
二、备份数据库
cd /backupdb
db2 force applications all&&db2 backup db dbname compress
三、生成需要失效的外键(sh gendis.sh) 以下为gendis.sh的内容
---------------------------------------------
db2 connect to dbname 
db2 -x "select distinct FK_NAME from sysibm.sqlforeignkeys where FKTABLE_SCHEM='DB2INST1' and PKTABLE_SCHEM='DB2INST1' and FK_NAME like 'FK_%'" > disfk.list
cat disfk.list | while read fkname
do
 echo "$fkname"
 db2 -x "select distinct 'alter table '||FKTABLE_NAME||' alter foreign key '||FK_NAME||' not enforced;' from sysibm.sqlforeignkeys where FK_NAME='$fkname'" >> disable_fk.sql
done
----------------------------------------------
三、失效外键
db2 connect to dbname
db2 -tvf disable_fk.sql|tee -a disable.log
四、detach分区表，执行sh detach.sh 以下为脚本内容
----------------------------------------------
LOGPATH=detach.log
db2 connect to dbname
db2 -x "select substr(tabname,1,20) from syscat.DATAPARTITIONEXPRESSION where tabschema='DB2INST1' and length(tabname)=8 " >detach_part.list
cat detach_part.list|while read tabname
do
  echo "#####################################################" |tee -a $LOGPATH
  echo "Current time is: `date +%Y%m%d%H%M%S`" |tee -a $LOGPATH
  echo "Starting detach table $tabname !" |tee -a $LOGPATH
  db2 "ALTER TABLE db2inst1.$tabname DETACH PARTITION 806 INTO ${tabname}_806" |tee -a $LOGPATH
  db2 "ALTER TABLE db2inst1.$tabname DETACH PARTITION 809 INTO ${tabname}_809" |tee -a $LOGPATH
  db2 "ALTER TABLE db2inst1.$tabname DETACH PARTITION 810 INTO ${tabname}_810" |tee -a $LOGPATH
  db2 "ALTER TABLE db2inst1.$tabname DETACH PARTITION 818 INTO ${tabname}_818" |tee -a $LOGPATH
   echo "Current time is: `date +%Y%m%d%H%M%S`" |tee -a $LOGPATH
done
------------------------------------------------
五、激活外键
  一）首先执行gen_enable.sh生成激活外键SQL文，脚本内容如下：
----------------------------------------------------------
db2 connect to dbname 
db2 -x "select distinct FK_NAME from sysibm.sqlforeignkeys where FKTABLE_SCHEM='DB2INST1' and PKTABLE_SCHEM='DB2INST1' and FK_NAME like 'FK_%'" > enafk.list
cat enafk.list | while read fkname
do
 echo "$fkname"
 db2 -x "select distinct 'alter table '||FKTABLE_NAME||' alter foreign key '||FK_NAME||' enforced;' from sysibm.sqlforeignkeys where FK_NAME='$fkname'" >> enable_fk.sql
done
-----------------------------------------------------------
  二）生效外键
db2 connect to dbname
db2 -tvf enable_fk.sql|tee -a enable.log
六、添加分区
执行 addpart.sh 添加刚刚detach掉的分区
----------------------------------------------------------
LOGPATH=addpart.log
db2 connect to dbname 
db2 -x "select substr(tabname,1,20) from syscat.DATAPARTITIONEXPRESSION where tabschema='DB2INST1' and length(tabname)=8 " >part.list
cat part.list|while read tabname
do
        echo "#####################################################" |tee -a $LOGPATH
        echo "Current time is: `date +%Y%m%d%H%M%S`" |tee -a $LOGPATH
        echo "Starting add tablepartition $tabname !" |tee -a $LOGPATH
        db2 "ALTER TABLE db2inst1.$tabname  add PARTITION 806 STARTING '806' ENDING '807' exclusive in CBOD_P_806" |tee -a $LOGPATH
        db2 "ALTER TABLE db2inst1.$tabname  add PARTITION 809 STARTING '809' ENDING '810' exclusive in CBOD_P_809" |tee -a $LOGPATH
        db2 "ALTER TABLE db2inst1.$tabname  add PARTITION 810 STARTING '810' ENDING '811' exclusive in CBOD_P_810" |tee -a $LOGPATH
        db2 "ALTER TABLE db2inst1.$tabname  add PARTITION 818 STARTING '818' ENDING '819' exclusive in CBOD_P_818" |tee -a $LOGPATH
        echo "Current time is: `date +%Y%m%d%H%M%S`" |tee -a $LOGPATH
done
-----------------------------------------------------------------------
六、优化
对操作过的表进行reorg和runstat
sh compress.sh  #脚本内容如下
-----------------------------------------------------------------------
LOGPATH=compress_tab_01.log
db2 connect to dbname 
cat tablist_1 | while read tabname
do
        echo "#####################################################" |tee -a $LOGPATH
        echo "Current time is: `date +%Y%m%d%H%M%S`" |tee -a $LOGPATH
        echo "Starting compress table $tabname !" |tee -a $LOGPATH
        db2 "alter table ${tabname} compress yes" |tee -a $LOGPATH
        db2 "reorg table ${tabname} use CBODTMP resetdictionary" |tee -a $LOGPATH
        db2 "reorg indexes all for table db2inst1.${tabname}" |tee -a $LOGPATH
        db2 "runstats on table db2inst1.${tabname} with distribution and detailed indexes all" |tee -a $LOGPATH
        echo "Current time is: `date +%Y%m%d%H%M%S`" |tee -a $LOGPATH
        echo "Compress table $tabname completed!" |tee -a $LOGPATH
done
-------------------------------------------------------------------------
七、绑定
对packeage重新绑定执行 sh rebind.sh
LOGPATH=rebind_pkg.log
echo "Current time is: `date +%Y%m%d%H%M%S`" |tee -a $LOGPATH
db2 connect to dbname
db2 -x "select substr(pkgname,1,8) from syscat.packages where pkgschema='DB2INST1' and length(pkgname)=8" > pkglist
cat pkglist| while read pkgname
do
 echo $pkgname |tee -a $LOGPATH
 db2 "rebind $pkgname" |tee -a $LOGPATH
done
echo "Current time is: `date +%Y%m%d%H%M%S`" |tee -a $LOGPATH