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

   来源：IBM.com    作者：郑靖华

# 生成卸出指定表的数据的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 的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命令均执行无错为止。

取得新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\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 '' 50000) bufferpool bf81"
done
echo "-----------------------------------
---------------------------"

执行结果类似于：

创建缓冲池的命令:create bufferpool BF81 size 10000 pagesize 8 KB。

创建表空间的命令:

--------------------------------------
------------------------
CREATE TABLESPACE "DATATBS00_8K" PAGESIZE 
8K MANAGED BY DATABASE USING (FILE
'' 50000) bufferpool bf81
CREATE TABLESPACE "DATATBS01_8K" PAGESIZE 
8K MANAGED BY DATABASE USING (FILE
'' 50000) bufferpool bf81
CREATE TABLESPACE "DATATBS02_8K" PAGESIZE 
8K MANAGED BY DATABASE USING (FILE
'' 50000) bufferpool bf81
CREATE TABLESPACE "IDXTBS00_8K" PAGESIZE 
8K MANAGED BY DATABASE USING (FILE
'' 50000) bufferpool bf81
CREATE TABLESPACE "IDXTBS02_8K" PAGESIZE 
8K MANAGED BY DATABASE USING (FILE
'' 50000) bufferpool bf81
CREATE TABLESPACE "STATICTBS00_8K" PAGESIZE 
8K MANAGED BY DATABASE USING
(FILE '' 50000) bufferpool bf81
CREATE TABLESPACE "USERSPACE1" PAGESIZE 
8K MANAGED BY DATABASE USING (FILE
'' 50000) bufferpool bf81
------------------------------------
--------------------------

在实际创建表空间时，需要将其中的""替换成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 '' 50000) bufferpool bf81
db2 CREATE TABLESPACE "DATATBS01_8K" PAGESIZE 
8K MANAGED BY DATABASE USING
(FILE '' 50000) bufferpool bf81
db2 CREATE TABLESPACE "DATATBS02_8K" PAGESIZE 
8K MANAGED BY DATABASE USING
(FILE '' 50000) bufferpool bf81
db2 CREATE TABLESPACE "IDXTBS00_8K" PAGESIZE 
8K MANAGED BY DATABASE USING
(FILE '' 50000) bufferpool bf81
db2 CREATE TABLESPACE "IDXTBS02_8K" PAGESIZE 
8K MANAGED BY DATABASE USING
(FILE '' 50000) bufferpool bf81
db2 CREATE TABLESPACE "STATICTBS00_8K" PAGESIZE 
8K MANAGED BY DATABASE USING
(FILE '' 50000) bufferpool bf81
db2 CREATE TABLESPACE "USERSPACE1" PAGESIZE 
8K MANAGED BY DATABASE USING
(FILE '' 50000) bufferpool bf81

其中，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;\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文件中提取出刚才被临时删除的外键关联的语句，在命令行执行这些语句。