扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条
我们在项目中经常遇到这样一种情况：项目组所有成员共用一个系统集成测试环境（SIT）中的数据库，每一个成员PC机上也安装一个相同的数据库作为单元测试环境。因为业务模型的变化，导致数据库结构发生变化时，通常由数据库管理员根据数据模型组的需求修改SIT的数据库结构，并保持原有数据的延续性。此时，PC机上单元测试数据库结构与SIT数据库结构就可能不一致，通常有以下几种方式来继续保持它们的一致性（我们称这个过程为数据库SCHEMA的同步）：
1、 由模型组或者数据库管理员发布数据库修改的命令清单，各成员按照清单逐步操作，实现数据库SCHEMA的同步。这种方法要求每一次SIT数据库SCHEMA的修改都必须被记录在案，并通知到项目组每一个成员。如果某个成员遗漏了其中某一次修改，则他以后的修改都可能会有问题。当然，他也可以从其他成员的单元测试环境中备份、恢复最新版的数据库。
2、 由数据库管理员将SIT数据库整库备份，各成员在PC机上删除原有数据库，恢复该备份数据库。这种方法看起来总能得到最新的SCHEMA和数据，但通常比较难于实施。因为SIT环境通常在UNIX环境下，而单元测试环境通常在WINDOWS环境下，二者对于磁盘设备的管理方式相差甚大，通过数据库备份工具得到的备份文件，在不同操作系统下恢复时，对于磁盘设备不同导致的修改相当麻烦。同时，SIT环境的数据量通常比较大，有的项目甚至于会定期从生产线上获取最新的数据，而单元测试环境通常是在PC机上，所能提供的磁盘空间相对较小，多数情况下都不足以恢复全量的SIT数据库。
3、 由模型组或者数据库管理员发布数据库修改的通知，各成员采用本文所描述的方法，根据SIT数据库最新的SCHEMA，用PC机上的数据同步数据库。这种方法采用本文提供的SHELL程序，可以比较快的完成，其中仅需要较小的人工干预。并且，这种方法也能保持单元测试环境中数据的延续性。
本文说明如何实现第三种方法，完成DB2数据库的同步。
本文所使用的环境为：
本文使用如下约定：
1、从SIT环境中取得最新的数据库SCHEMA
2、从单元测试环境取得旧数据库的SCHEMA
3、在单元测试环境，用db2move命令卸出旧数据库的数据
4、比较两个版本的异同
5、如果没有任何表的SCHEMA发生了改变，则执行第7步
6、如果有表的SCHEMA发生了改变，则执行：
用新的表结构创建一个临时表；
生成insert into transtmptbl (...) select ... from <oldtbl>的SQL；
将临时表中的数据导出到文件中；
删除临时表；
用新的数据替换第3步导出的指定表的数据文件，然后执行第7步
7、取得新SCHEMA的表空间名称
8、重建数据库
9、有选择的将新库中独有的表的数据导入到本地新数据库中
10、检查数据库的完整性
以下，我们详细说明这些步骤。
在SIT环境中，执行以下命令：
db2look -d cusgadev -i db2admin -w db2admin -e -a -o cusnew.sql |
该命令用于从SIT环境中抽取最新的DDL语句。其中各选项的涵义如下：
-d <dbname>：指定被抽取DDL的数据库名称。
-e：抽取数据库的DDL语句，包括表、视图、自动摘要表、别名、索引、触发器、Sequences、User defined Distinct Types、Primary Key/RI/CHECK约束、用户定义的结构化类型、用户定义的函数、用户定义的方法、用户定义的转换。
-i <userid>：连接远程数据库时，需要用这个选项指定登录用户名。
-w <passwd>：连接远程数据库时，需要用这个选项指定登录用户的密码。
-a：输出所有用户创建的对象，而不仅仅是当前登录用户创建的对象。
-o <outfile>：将DDL输出到指定的文件中。如果不指定该选项，则将输出到标准输出。
在单元测试环境中，执行以下命令：
db2look -d cusgadev -e -a -o cusnew.sql |
该命令用于从单元测试环境中抽取最新的DDL语句。各选项的涵义见上文。
在单元测试环境中，执行以下命令：
db2move cusgadev export |
该命令用于将单元测试环境中已有的数据输出到当前目录下。该命令为每一个表生成独立的PC/IXF格式的数据文件，这些文件可以传输到任何其他的机器上并通过load或者import命令装入到那台机器上的DB2数据库中。
该命令同时生成一个名为"db2move.lst"的文件，这个文件是卸出表的清单，指明哪一个表对应到哪一个数据文件。该文件将用于之后的步骤，以便用最新的SCHEMA卸出原有表的数据。
以下，我们使用cygwin环境下的SHELL程序来分析新旧数据库的SCHEMA。
格式化SCHEMA文件
为了比较新旧数据库的SCHEMA，我们需要将两个SCHEMA文件转换成统一格式，包括：将所有大写替换成小写；删除所有行首的空格；将多个空格合并成1个空格。其中，空格包括制表符（Tab）和空格字符(Space)。
使用以下命令组合格式化SCHEMA文件：
sed -e "s/^[ ]*//" \
-e "s/[ ][ ]*/ /g" \
${OLDDBSCHEMA} | tr [:upper:] [:lower:] > ${OLDDBSCHEMATMP}
|
取得新旧数据库的table清单
定义如下函数，从数据库的SCHEMA文件中取得其中的table清单。这个函数适用于由db2look生成的、DB2数据库的DDL文件。
# 根据数据库的SCHEMA文件，取得其中的table清单
# $1. 数据库的SCHMEA文件
# $2. 输出的table清单文件
gettbllist()
{
grep -i "^[ ]*create[ ][ ]*table" $1 > $2.tmp
sed -e "s/^[ ]*CREATE[ ][ ]*TABLE//" \
-e "s/(.*$//" \
$2.tmp | sort > $2
rm $2.tmp
}
|
找出仅在旧库或者仅在新库独有的表
定义如下函数，取出仅在指定数据库SCHEMA中存在的table的清单。这个函数适用于由db2look生成的、DB2数据库的DDL文件。
# 取出仅在第一个参数指定的数据库SCHEMA中存在的表的名称清单
# $1: 待分析的数据库SCHEMA文件
# $2: 用作参照的数据库SCHEMA文件
# $3: 屏幕输出的提示信息
gettableonlyinone()
{
if [ $# -eq 3 ]
then
echo $3
fi
TABLEINBOTH=""
for tblname in `awk -F"." '{printf("%s\n", $2);}' $1 | awk -F"\""
'{printf("%s ", $2)}' | sort`
do
grep -i "\"$tblname\"" $2 1>/dev/null 2>&1
if [ ! $? -eq 0 ]
then
if [ $# -eq 3 ]
then
echo $tblname
fi
else
TABLEINBOTH="${TABLEINBOTH} ${tblname}"
fi
done
}
|
对于仅在旧数据库中存在的表，需要从db2move.lst中删除该表对应的行。
对于仅在新数据库中存在的表，需要根据需要有选择的从新数据库中卸出数据并装入到新数据库中。
找出结构不同的表，并生成卸出数据的SQL脚本
通过以下步骤，找到在新旧数据库中都存在，并且结构发生了变化的table，并生成重新卸出数据的SQL语句。
# 根据数据库的SCHEMA文件，取得指定table的SCHEMA
# $1: table名称
# $2: 数据库的SCHEMA文件，需要先做格式化
# $3: 输出的表的SCHEMA文件
gettableschema()
{
#echo "table name: " $1
#echo "dbschema name: " $2
#echo "outfile name: " $3
awk -v_tblname=$1 'BEGIN{isThisTable = 0;}{
if($1 == "create" && $2 == "table" && match($0, "\""_tblname"\"") > 0)
{
isThisTable = 1;
printf("create table %s (\n", _tblname); #### )
}
else if(isThisTable == 1)
{
if(match($0, ";") > 0)
{
isThisTable = 0;
if($1 == "in")
printf("; \n", $0);
else
printf("%s\n", $0);
}
else if(match($0, "timestamp not null with default ,") > 0)
{
printf("%s timestamp not null with default current timestamp , \n", $1);
}
else
{
printf("%s\n", $0);
}
}
}' $2 > $3
}
# 生成卸出指定表的数据的SQL脚本
# $1. 旧数据库的SCHEMA文件
# $2. 新数据库的SCHEMA文件
# $3. 输出的卸出指定表的数据的SQL脚本文件名
# $4. 指定的表名
# $5. db2move卸出文件时的清单文件
genunloadsql()
{
TMPSQLFILE=.tmpsql.sql.sql
TMPNEWTBLFLD=.tmpfld.fld.fld
rm -f ${TMPSQLFILE}
# 1. 用新的表结构创建一个临时表
awk '{
if(NR > 1)
{
gsub("\"", "", $1); # remove character "
printf("%s\n", $0);
}
else
{
printf("create table sihitranstmptbl (\n"); # )
}
}' $2 > ${TMPSQLFILE}
# 2. 生成insert into transtmptbl (...) select ... from <oldtbl>的SQL
# 2.1. 生成新旧两个表的字段列表
awk '{
if(NR > 1 && $1 != ";")
{
gsub("\"", "", $1); # remove character "
printf("%s\n", $1);
}
}' $2 > ${TMPNEWTBLFLD}
isFirstFeild=1
Select=""
Into=""
for fldname in `cat ${TMPNEWTBLFLD}`
do
grep "\"${fldname}\"" $1 1>/dev/null 2>&1
if [ $? -eq 0 ]
then
# 2.2. 取出在新旧两表中都有的字段名，加入到select子句和into子句中
if [ ${isFirstFeild} -eq 0 ]
then
Select="${Select},"
Into="${Into},"
fi
Select="${Select} ${fldname}"
Into="${Into} ${fldname}"
isFirstFeild=0
else
# 2.3. 取出仅在新表中出现的字段，如果该字段不允许null，
# 且没有设置default值，则按以下原则取默认值放到select子句中，
# 并将字段名放到into子句中
grep "\"${fldname}\"" $2 | grep "not null" 1>/dev/null 2>&1
if [ $? -eq 0 ]
then
grep "\"${fldname}\"" $2 | grep "with default" 1>/dev/null 2>&1
if [ ! $? -eq 0 ]
then
if [ ${isFirstFeild} -eq 0 ]
then
Select="${Select},"
Into="${Into},"
fi
# 计算默认值
# 对于新增的字符型字段，默认值为''；
# 对于新增的数值型字段，默认值为0；
# 对于新增的TIMESTAMP字段，默认值为'';
# 对于新增的SERIAL开字段，默认值为0；
# 对于新增的DATE字段，默认值为；
# 对于新增的DATETIME字段，默认值为；
Const=`grep "\"${fldname}\"" $2 | awk '{
if(match($2, "int") > 0) # integer, smallint, bigint
printf("0");
else if(match($2, "numeric") > 0)
printf("0.0");
else if(match($2, "decimal") > 0)
printf("0.0");
else if(match($2, "double") > 0)
printf("0.0");
else if(match($2, "float") > 0)
printf("0.0");
else if(match($2, "real") > 0)
printf("0.0");
else if(match($2, "char") > 0) # char, varchar
printf("\" \"");
else if($2 == "timestamp")
printf("current timestamp");
else if($2 == "date")
printf("current date");
else if($2 == "time")
printf("current time");
else
printf("\" \"");
}'`
Select="${Select} ${Const}"
Into="${Into} ${fldname}"
isFirstFeild=0
fi
fi
fi
done
tablefullname=`grep -i "\"$4\"" $5 | awk -F"!" '{printf("%s", $2);}'`
echo "insert into sihitranstmptbl (${Into}) select ${Select} from
${tablefullname} ;" >> ${TMPSQLFILE}
# 3. 将临时表中的数据导出到文件中
Unloadfile=`grep -i "\"$4\"" $5 | awk -F"!" '{printf("%s", $3);}'`
echo "export to ${Unloadfile} of ixf select * from sihitranstmptbl ;"
>> ${TMPSQLFILE}
# 4. 删除临时表
echo "drop table sihitranstmptbl ;" >> ${TMPSQLFILE}
cat ${TMPSQLFILE} >> $3
rm -f ${TMPSQLFILE}
rm -f ${TMPNEWTBLFLD}
}
echo "table in both database:"
TABLEINBOTH="`echo ${TABLEINBOTH} | tr [:upper:] [:lower:]`"
echo > ${UNLOADSQLFILE}
for tblname in ${TABLEINBOTH}
do
gettableschema ${tblname} ${OLDDBSCHEMATMP} ${OLDTBLSCHEMA}
gettableschema ${tblname} ${NEWDBSCHEMATMP} ${NEWTBLSCHEMA}
diff ${OLDTBLSCHEMA} ${NEWTBLSCHEMA} 1>/dev/null 2>&1
if [ ! $? -eq 0 ]
then
# 如果有不同，则生成卸出数据的SQL脚本
echo "different table:" ${tblname}
genunloadsql ${OLDTBLSCHEMA} ${NEWTBLSCHEMA}
${UNLOADSQLFILE} ${tblname} ${DB2MOVELISTFILE}
else
echo "same table:" ${tblname}
fi
done
|
其中，TABLEINBOTH是由上一步（找出仅在旧库或者新库中独有的表）的副产品。
注意，上述程序中，并没有处理新旧table中均有并且字段类型不同的字段，这种情况可以在卸出数据时按需要手工编辑生成的SQL脚本。
生成的SQL脚本名称叫"unload.sql"。在PC机上的DB2命令窗口中执行：
db2 -z result.txt -tvf unload.sql |
其中，"-z"选项将执行结果同时输出到屏幕及文件result.txt中。执行结束时，需要查看result.txt，如果其中有错误提示，请按需要修改unload.sql。修改之后，再重新执行上述命令，直到所有SQL命令均执行无错为止。
通过以下步骤，找出新数据库使用的表空间的名称，并给出创建数据库缓冲池及表空间的SQL的建议。
# 8. 根据新数据库SCHEMA文件取得tablespace的名称清单
# 并给出创建缓冲池及tablespace的SQL建议
echo "创建缓冲池的命令: "
echo "create bufferpool BF81 size 10000 pagesize 8 K"
echo "创建表空间的命令: "
echo "--------------------------------------------------------------"
for tblspace in ` grep -i "^[ ]*in[ ]" ${NEWDBSCHEMA} | grep ";" | awk '{
printf("%s\n", $2);
if(NF > 3)
{
if($3 == "INDEX" && $4 == "IN")
{
printf("%s\n", $5);
}
}
}' | sort | uniq `
do
echo "CREATE TABLESPACE ${tblspace} PAGESIZE 8K MANAGED BY DATABASE USING
(FILE '<filename>' 50000) bufferpool bf81"
done
echo "--------------------------------------------------------------"
|
执行结果类似于：
创建缓冲池的命令:
create bufferpool BF81 size 10000 pagesize 8 K |
创建表空间的命令:
-------------------------------------------------------------- CREATE TABLESPACE "DATATBS00_8K" PAGESIZE 8K MANAGED BY DATABASE USING (FILE '<filename>' 50000) bufferpool bf81 CREATE TABLESPACE "DATATBS01_8K" PAGESIZE 8K MANAGED BY DATABASE USING (FILE '<filename>' 50000) bufferpool bf81 CREATE TABLESPACE "DATATBS02_8K" PAGESIZE 8K MANAGED BY DATABASE USING (FILE '<filename>' 50000) bufferpool bf81 CREATE TABLESPACE "IDXTBS00_8K" PAGESIZE 8K MANAGED BY DATABASE USING (FILE '<filename>' 50000) bufferpool bf81 CREATE TABLESPACE "IDXTBS02_8K" PAGESIZE 8K MANAGED BY DATABASE USING (FILE '<filename>' 50000) bufferpool bf81 CREATE TABLESPACE "STATICTBS00_8K" PAGESIZE 8K MANAGED BY DATABASE USING (FILE '<filename>' 50000) bufferpool bf81 CREATE TABLESPACE "USERSPACE1" PAGESIZE 8K MANAGED BY DATABASE USING (FILE '<filename>' 50000) bufferpool bf81 -------------------------------------------------------------- |
在实际创建表空间时，需要将其中的"<filename>"替换成Windows系统下的一个文件名称，每一个表空间使用不同的文件名。也可以根据对数据库中数据分布的估计，调整表空间的大小。
按以下步骤重建数据库：
1、编辑db2move.lst，删除在新库中已不使用的表。
根据上述步骤中得到的仅在旧数据库中存在的表，编辑db2move.lst，删除该表所在的行。
2、删除旧数据库。
执行以下命令，删除旧数据库：
db2 drop database cusgadev |
3、创建数据库
执行以下命令，创建新数据库：
db2 create database cusgadev using codeset iso8859-1 territory cn |
其中，"iso8859-1"表示数据库使用的字符集，"cn"表示数据库使用的地域。
4、连接数据库
执行以下命令，连接新数据库：
db2 connect to cusgadev |
5、修改物理日志参数
执行以下命令，修改数据库的日志参数：
db2 update database configuration using logfilsiz 25000 deferred |
创建数据时，默认的日志空间比较小，对于大事务的处理有影响，所以，需要根据应用的需要及PC机的配置适当调整日志空间的值。
可以使用"GET DATABASE CONFIGURATION"命令查看数据库的当前配置。
6、创建缓冲池
执行以下命令，为数据库创建缓冲池：
db2 create bufferpool BF81 size 10000 pagesize 8 K |
7、创建表空间
执行以下命令，为数据库创建表空间：
db2 CREATE TABLESPACE "DATATBS00_8K" PAGESIZE 8K MANAGED BY DATABASE USING (FILE '<filename>' 50000) bufferpool bf81 db2 CREATE TABLESPACE "DATATBS01_8K" PAGESIZE 8K MANAGED BY DATABASE USING (FILE '<filename>' 50000) bufferpool bf81 db2 CREATE TABLESPACE "DATATBS02_8K" PAGESIZE 8K MANAGED BY DATABASE USING (FILE '<filename>' 50000) bufferpool bf81 db2 CREATE TABLESPACE "IDXTBS00_8K" PAGESIZE 8K MANAGED BY DATABASE USING (FILE '<filename>' 50000) bufferpool bf81 db2 CREATE TABLESPACE "IDXTBS02_8K" PAGESIZE 8K MANAGED BY DATABASE USING (FILE '<filename>' 50000) bufferpool bf81 db2 CREATE TABLESPACE "STATICTBS00_8K" PAGESIZE 8K MANAGED BY DATABASE USING (FILE '<filename>' 50000) bufferpool bf81 db2 CREATE TABLESPACE "USERSPACE1" PAGESIZE 8K MANAGED BY DATABASE USING (FILE '<filename>' 50000) bufferpool bf81 |
其中，tablespace的名称是从新数据库的SCHEMA文件中提取出来的。本例中，它们都使用同一个缓冲池。"<filename>"需要按照PC机硬盘空余空间的情况，修改为实际的全路径文件名称。
8、用新的SCHEMA创建数据库
执行以下命令，按照新数据库的SCHEMA创建其中的各种对象，如表、索引等：
db2 -tvf cusnew.sql |
9、装入数据
执行以下命令，将旧数据库的数据装入到刚创建的新数据库中：
db2move cusgadev load |
该命令使用的修改后的db2move.lst作为装入表的清单。
有选择的将新库中独有的表的数据导入到本地新数据库中对于仅在新数据库中存在的表，可以根据需要有选择的将其数据从SIT环境导入到单元测试环境。本文中，不考虑新表对于其他表的外键关联。
假设某个仅在新数据库中存在的表的名称为"newtable"，在SIT环境执行以下命令卸出数据：
db2 unload to newtable.ixf of ixf select * from newtable |
将文件"newtable.ixf"传输到单元测试环境所在的PC机上，执行以下命令：
db2 load from newtable.ixf of ixf insert into newtable |
如果这个新表对于其他表有外键关联，还需要导入其他相关表的数据。
检查完整性的SHELL程序
以下是"CheckIntegrity.sh"的源代码。该程序用于在数据load结束以后，检查数据库的完整性。
#!/usr/bin/sh
# 在数据load结束后，检查数据库的完整性
# Copyright: SI HITECH 2006
# 作 者: 郑靖华
# 创建日期: 2006 年 8 月 2 日
# 指定数据库名称
if [ $# -lt 1 ]
then
DBNAME=ccdb
else
DBNAME=$1
fi
# 检查db2move的lst文件
if [ ! -r db2move.lst ]
then
echo "当前目录下没有db2move的清单文件 [ db2move.lst ]，请确保目录正确并再次执行本命令!"
exit 1
fi
# 生成执行完整性检查的SHELL程序
awk -F"!" -v_dbname=${DBNAME} 'BEGIN{
printf("connect to %s;\n", _dbname);
}{
printf("set integrity for %s immediate checked;\n", $2);
}' db2move.lst > $DBNAME.integrity.sql
# 执行检查程序
db2 -tvf $DBNAME.integrity.sql
echo "完整性检查完毕，请仔细查看检查结果!"
exit 0
|
所谓外键关联链，是指A表依赖于B表，B表依赖于C表。只有当C表的外键关联检查成功以后，才能检查B表；同样的，只有当B表的外键关联检查成功以后，才能检查A表。即为了检查A表，可能需要多次执行上述SHELL程序。
所谓外键关联环，是指A表直接或者间接依赖于自身（A表）。比如，A表的a1字段依赖于A表的a2字段，同时，A表的a2字段也依赖于A表的a3字段（其中，a1、a2、a3可以相同，也可以不同），这是一个直接关联的环。再如，A表的a1字段依赖于B表的b1字段，同时，B表的b2字段依赖于A表的a2字段（其中，a1和a2、b1和b2可以相同，也可以不同），这是一个间接关联的环。如果存在这样的环，我们必须通过删除其中的某一个或几个依赖来打破这个环，在完整性检查结束以后，再重建那些被临时删除的依赖。
验证完整性的SHELL程序
我们通过查询所有表的记录数，来验证所有表的完整性是否都已经检查成功。以下是"CntDb.sh"的源码。
#!/usr/bin/sh
# 在数据load结束后，取得数据库每一个表的记录数
# Copyright: SI HITECH 2006
# 作 者: 郑靖华
# 创建日期: 2006 年 8 月 3 日
# 指定数据库名称
if [ $# -lt 1 ]
then
DBNAME=ccdb
else
DBNAME=$1
fi
# 检查db2move的lst文件
if [ ! -r db2move.lst ]
then
echo "当前目录下没有db2move的清单文件 [ db2move.lst
]，请确保目录正确并再次执行本命令!"
exit 1
fi
# 生成执行完整性检查的SHELL程序
awk -F"!" -v_dbname=${DBNAME} 'BEGIN{
printf("connect to %s;\n", _dbname);
}{
tbname = $2;
gsub("\"", "", tbname);
gsub(" ", "", tbname);
printf("select count(*) from %s;\n", tbname);
}' db2move.lst > $DBNAME.cnt.sql
# 执行检查程序
db2 -tvf $DBNAME.cnt.sql
echo "完整性检查完毕，请仔细查看检查结果!"
exit 0
|
如果某个表未完成完整性检查，则在执行上述SHELL程序时，会出现下述错误提示：
select count(*) from sampletable 1 ----------- SQL0668N 由于表 "SAMPLETABLE" 上的原因代码 "1"，所以不允许操作。 SQLSTATE=57016 |
这个错误代码及原因代码，表示该表正处于"Check Pending"状态，需要用"IMMEDIATE CHECKED"选项执行"SET INTEGRITY"命令，即立即检查其完整性。
打破外键关联的环，检查完整性
通过多次执行上述两个SHELL程序，可以判断出数据库SCHEMA中是否存在外键关联环。通过分析数据库SCHEMA文件，找出这个环，并找到这个环上的一个依赖关系，执行以下步骤：
1、找到外键关联环上的一个依赖关系
2、删除这个外键
db2 ALTER TABLE a1 DROP FOREIGN KEY a1_FK02 |
3、检查完整性并验证完整性
执行"CheckIntegrity.sh"、"CntDb.sh"，如果完整性检查成功，则执行第4步；如果多次执行这两个SHELL后，完整性均没有检查成功，说明可能还有其他的外键关联环，需要再次执行第1步。
4、重建这个外键关联
从新数据库SCHEMA文件中提取出刚才被临时删除的外键关联的语句，在命令行执行这些语句。
如果您非常迫切的想了解IT领域最新产品与技术信息，那么订阅至顶网技术邮件将是您的最佳途径之一。
京公网安备 11010802021500号