DB2数据库分层测试环境下表结构版本一致性验证最佳实践

由于公司严格按照行业测试规范实施测试环境部署从而导致测试环境跨度较大（从FT、UT、IT、ST、SIT）。作为DBA随着时间的推移，难免部分环境数据库结构与基准环境不一致，但此时相应测试环境已经埋入大量测试用例数据，又无法删除整个数据库重建。为了方便同步数据库版本至指定环境版本所以才有了本文。
在现有的IT环境已经埋数如何将IT环境的数据库表结构与基准环境进行表结构进行比较并将差异化的表记录下来生效到IT环境？本文给出了如下方案以供参考：
1、同时在基准环境和待比较环境创建表普通表dbtab和参数表cstab
db2 "create table cstab(tabname varchar(128),colname varchar(128),typename varchar(128),length INTEGER)"
db2 "create table dbtab(tabname varchar(128),colname varchar(128),typename varchar(128),length INTEGER)"
2、存储表信息至cstab表，执行collectcs.sh该脚本存储表信息(参数环境)
db2 connect to cbusparm user db2inst1 using ibm456cbb >>/dev/null 2>&1
db2 "import from /dev/null of del replace into cstab" #清空表
cat tblist|while read a
do
db2 "insert into cstab(tabname,colname,typename,length) select distinct tabname,colname,typename,length from syscat.columns where tabname='${a}'"
done
-----------------------------------------------------------------------------------------
存储数据移植表dbtab（普通表其中tblist为研发提供需要比较的表清单）
db2 connect to cbusdb >>/dev/null 2>&1
db2 "import from /dev/null of del replace into dbtab"
cat tblist|while read a
do
db2 "insert into dbtab(tabname,colname,typename,length) select distinct tabname,colname,typename,length from syscat.columns where tabname='${a}'"
done
3、将以上信息导出到ixf文件并专场至异地基准环境恢复
db2 "export to cstab_3.ixf of ixf select * from cstab"
-----------clear_cstab.sh-------------------------------------
db2 connect to cbusdb >>/dev/null 2>&1
db2 "import from /dev/null of del replace into cstab"
db2 "import from cstab_3.ixf of ixf insert into cstab"
--------------------------------------------------------------
db2 "export to dbtab_3.ixf of ixf select * from dbtab"
-----------clear_dbtab.sh-------------------------------------
db2 connect to cbusdb >>/dev/null 2>&1
db2 "import from /dev/null of del replace into dbtab"
db2 "import from dbtab_3.ixf of ixf insert into dbtab"
--------------------------------------------------------------
4、参数表比较脚本，执行该脚本进行比较
---------------------------------参数表结构比较---------------------------------------------------------
db2 connect to cbusdb >>/dev/null 2>&1
db2 -x "select distinct tabname from cstab" > tablelist
cat tablelist|while read line                                
do                                                           
db2 connect to cbusdb > /dev/null
result=`db2 -x "select distinct a.tabname from cstab a ,syscat.columns b where a.tabname=b.tabname and a.colname=b.colname and a.typename=b.typename and a.length=b.length and a.tabname='$line' "`
if [ $result == $line ]; then
echo $result
echo $result >> yz.log
else
echo $line >> byz.log
fi
done |tee tablelist.log
---------------------------------普通表结构比较------------------------------------------------------------
db2 connect to cbusdb >>/dev/null 2>&1
db2 -x "select distinct tabname from dbtab" > tablelist
cat tablelist|while read line                                
do                                                           
db2 connect to cbusdb > /dev/null
result=`db2 -x "select distinct a.tabname from dbtab a ,syscat.columns b where a.tabname=b.tabname and a.colname=b.colname and a.typename=b.typename and a.length=b.length and a.tabname='$line' "`
if [ $result == $line ]; then
echo $result
echo $result >> yz.log
else
echo $line >> byz.log
fi
done |tee tablelist.log
---------------------------------------------------------------------------------------------
二、比较索引结构
1、基准和对比环境创建表ftindex
db2 "create table csindex(tabname varchar(128),colnames varchar(640),INDNAME varchar(128),UNIQUERULE varchar(1))"
2、存储需要比较的表相关索引信息
db2 connect to cbusparm user db2inst1 using ibm456cbod >>/dev/null 2>&1
cat tblist|while read a
do
db2 "insert into csindex(tabname,colnames,indname,uniquerule) select distinct tabname,colnames,indname,uniquerule from syscat.indexes where TABNAME='${a}'"
done
3、备份和转场比较
db2 "export to ftindex.ixf of ixf select * from csindex"
db2 "import from ftindex.ixf of ixf insert into csindex"
4、执行比较脚本开始比较信息
db2 connect to cbusdb >>/dev/null 2>&1
db2 -x "select indname from csindex where indname not like 'SQL%'" > indexlist
cat indexlist|while read line                                
do                                                           
db2 connect to cbusdb > /dev/null 2>&1
result=`db2 -x "select distinct a.indname from csindex a ,syscat.indexes b where a.tabname=b.tabname and a.colnames=b.colnames and a.INDNAME=b.INDNAME and a.COLNAMES=b.COLNAMES and a.UNIQUERULE=b.UNIQUERULE and a.indname='$line' "`
if [ $result == $line ]; then
 echo $result >> indyz.log
 echo $result
else
 echo $line >> indbyz.log
echo
fi
done|tee indexlist.log
4、确认哪些表不一致
db2 connect to cbusdb >>/dev/null 2>&1
cat indbyz.log|while read a
do
db2 -x "select tabname from csindex where indname='${a}'" >>tbname.txt
done
三、比较其不存在的表
cat tblist|while read a
do
db2 connect to cbusdb >>/dev/null 2>&1
res=`db2 -x "select TABNAME from syscat.tables where tabname='${a}'"`
if [ $res==$a ]; then
echo $res >>right.list
else
echo $a >>wron.list
fi
done
----------------------------------------------------------------------------------------------
四、根据以上收集的日志结果协同研发同步表结构