用AWK实现DB2 数据库 Schema的同步（1）

　　在多个成员用一个系统进行集成测试时，每一个成员 PC 机上也要安装一个相同的数据库作为单元测试环境。本文介绍了保持各个数据库一致性——我们称这个过程为数据库 schema 的同步——的几种方式。　　场景说明　　我们在项目中经常碰到这样一种情况：项目组所有成员共用一个系统集成测试环境（SIT）中的数据库，每一个成员PC机上也安装一个相同的数据库作为单元测试环境。因为业务模型的变化，导致数据库结构发生变化时，通常由数据库治理员根据数据模型组的需求修改SIT的数据库结构，并保持原有数据的延续性。此时，PC机上单元测试数据库结构与SIT数据库结构就可能不一致，通常有以下几种方式来继续保持它们的一致性（我们称这个过程为数据库SCHEMA的同步）：　　1、由模型组或者数据库治理员发布数据库修改的命令清单，各成员按照清单逐步操作，实现数据库SCHEMA的同步。这种方法要求每一次SIT数据库SCHEMA的修改都必须被记录在案，并通知到项目组每一个成员。假如某个成员遗漏了其中某一次修改，则他以后的修改都可能会有问题。当然，他也可以从其他成员的单元测试环境中备份、恢复最新版的数据库。　　2、由数据库治理员将SIT数据库整库备份，各成员在PC机上删除原有数据库，恢复该备份数据库。这种方法看起来总能得到最新的SCHEMA和数据，但通常比较难于实施。因为SIT环境通常在UNIX环境下，而单元测试环境通常在WINDOWS环境下，二者对于磁盘设备的治理方式相差甚大，通过数据库备份工具得到的备份文件，在不同操作系统下恢复时，对于磁盘设备不同导致的修改相当麻烦。同时，SIT环境的数据量通常比较大，有的项目甚至于会定期从生产线上获取最新的数据，而单元测试环境通常是在PC机上，所能提供的磁盘空间相对较小，多数情况下都不足以恢复全量的SIT数据库。 12345678910下一页　　3、由模型组或者数据库治理员发布数据库修改的通知，各成员采用本文所描述的方法，根据SIT数据库最新的SCHEMA，用PC机上的数据同步数据库。这种方法采用本文提供的SHELL程序，可以比较快的完成，其中仅需要较小的人工干预。并且，这种方法也能保持单元测试环境中数据的延续性。　　本文说明如何实现第三种方法，完成DB2数据库的同步。　　本文所使用的环境为：　　SIT环境：AIX UNIX 5.1、DB2 8.1。　　单元测试环境：Windows XP、DB2 8.1 for Windows、cygwin。　　本文使用如下约定：　　数据库的名称为cusgadev。　　旧数据库SCHEMA文件的名称为cusold.sql。　　新数据库SCHEMA文件的名称为cusnew.sql。　　输出的卸出SCHEMA有改变的表的数据的SQL脚本文件名称为unload.sql。　　DB2数据库SCHEMA同步步骤　　1、从SIT环境中取得最新的数据库SCHEMA。　　2、从单元测试环境取得旧数据库的SCHEMA。　　3、在单元测试环境，用db2move命令卸出旧数据库的数据。　　4、比较两个版本的异同。　　5、假如没有任何表的SCHEMA发生了改变，则执行第7步。　　6、假如有表的SCHEMA发生了改变，则执行：　　用新的表结构创建一个临时表；　　生成insert into transtmptbl (...) select ... from 的SQL；　　将临时表中的数据导出到文件中；　　删除临时表；　　用新的数据替换第3步导出的指定表的数据文件，然后执行第7步。　　7、取得新SCHEMA的表空间名称。　　8、重建数据库。　　9、有选择的将新库中独有的表的数据导入到本地新数据库中。　　10、检查数据库的完整性。　　以下，我们具体说明这些步骤。　　从SIT环境中取得最新的数据库SCHEMA。在SIT环境中，执行以下命令：上一页1234567下一页

db2look -d cusgadev -i db2admin -w
db2admin -e -a -o cusnew.sql

　　该命令用于从SIT环境中抽取最新的DDL语句。其中各选项的涵义如下：　　-d ：指定被抽取DDL的数据库名称。　　-e：抽取数据库的DDL语句，包括表、视图、自动摘要表、别名、索引、触发器、Sequences、User defined Distinct Types、Primary Key/RI/CHECK约束、用户定义的结构化类型、用户定义的函数、用户定义的方法、用户定义的转换。　　-i ：连接远程数据库时，需要用这个选项指定登录用户名。　　-w ：连接远程数据库时，需要用这个选项指定登录用户的密码。　　-a：输出所有用户创建的对象，而不仅仅是当前登录用户创建的对象。　　-o ：将DDL输出到指定的文件中。假如不指定该选项，则将输出到标准输出。　　从单元测试环境取得旧数据库的SCHEMA。在单元测试环境中，执行以下命令：　　db2look -d cusgadev -e -a -o cusnew.sql　　该命令用于从单元测试环境中抽取最新的DDL语句。各选项的涵义见上文。　　在单元单元测试环境，卸出旧数据库的数据。在单元测试环境中，执行以下命令：　　db2move cusgadev export　　该命令用于将单元测试环境中已有的数据输出到当前目录下。该命令为每一个表生成独立的PC/IXF格式的数据文件，这些文件可以传输到任何其他的机器上并通过load或者import命令装入到那台机器上的DB2数据库中。　　该命令同时生成一个名为"db2move.lst"的文件，这个文件是卸出表的清单，指明哪一个表对应到哪一个数据文件。该文件将用于之后的步骤，以便用最新的SCHEMA卸出原有表的数据。　　分析新旧数据库SCHEMA　　以下，我们使用cygwin环境下的SHELL程序来分析新旧数据库的SCHEMA。上一页12345678下一页　　格式化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
", $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
}

上一页123456789下一页　　对于仅在旧数据库中存在的表，需要从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 (
", _tblname); #### )}
else if(isThisTable == 1)
{
if(match($0, ";") > 0)
{
isThisTable = 0;
if($1 == "in")
printf(";
", $0);
else
printf("%s
", $0);
}
else if(match($0,
"timestamp not null with default ,") > 0)
{
printf("%s timestamp not null
with default current timestamp ,
", $1);
}
else
{
printf("%s
", $0);
}
}
}' $2 > $3
}

上一页12345678910下一页　　# 生成卸出指定表的数据的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
", $0);
}
else
{
printf("create table sihitranstmptbl (
"); # )
}
}' $2 > ${TMPSQLFILE}
# 2. 生成insert into transtmptbl (...) select ... from <oldtbl>的SQL
# 2.1. 生成新旧两个表的字段列表
awk '{
if(NR > 1 && $1 != ";")
{
gsub(""", "", $1); # remove character "
printf("%s
", $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

上一页234567891011下一页　　其中，TABLEINBOTH是由上一步（找出仅在旧库或者新库中独有的表）的副产品。　　注重，上述程序中，并没有处理新旧table中均有并且字段类型不同的字段，这种情况可以在卸出数据时按需要手工编辑生成的SQL脚本。　　生成的SQL脚本名称叫"unload.sql"。在PC机上的DB2命令窗口中执行：db2 -z result.txt -tvf unload.sql　　其中，"-z"选项将执行结果同时输出到屏幕及文件result.txt中。执行结束时，需要查看result.txt，假如其中有错误提示，请按需要修改unload.sql。修改之后，再重新执行上述命令，直到所有SQL命令均执行无错为止。　　取得新SCHEMA的表空间名称　　通过以下步骤，找出新数据库使用的表空间的名称，并给出创建数据库缓冲池及表空间的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
", $2);
if(NF > 3)
{
if($3 == "INDEX" && $4 == "IN")
{
printf("%s
", $5);
}}
}' | sort | uniq `
do
echo "CREATE TABLESPACE ${tblspace} PAGESIZE 8K MANAGED BY DATABASE USING
(FILE '<filename>' 50000) bufferpool bf81"
done
echo "-----------------------------------
---------------------------"

上一页34567891011下一页执行结果类似于：　　创建缓冲池的命令:create bufferpool BF81 size 10000 pagesize 8 KB。　　创建表空间的命令:

--------------------------------------
------------------------
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
------------------------------------
--------------------------

　　在实际创建表空间时，需要将其中的""替换成Windows系统下的一个文件名称，每一个表空间使用不同的文件名。也可以根据对数据库中数据分布的估计，调整表空间的大小。上一页4567891011下一页　　重建数据库　　按以下步骤重建数据库：　　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

上一页567891011下一页其中，tablespace的名称是从新数据库的SCHEMA文件中提取出来的。本例中，它们都使用同一个缓冲池。""需要按照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;
", _dbname);
}{
　　printf("set integrity for %s immediate checked;
", $2);
}' db2move.lst > $DBNAME.integrity.sql
# 执行检查程序
db2 -tvf $DBNAME.integrity.sql
echo "完整性检查完毕，请仔细查看检查结果!"
exit 0