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

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

在多个成员用一个系统进行集成测试时，每一个成员 PC 机上也要安装一个相同的数据库作为单元测试环境。本文介绍了保持各个数据库一致性——我们称这个过程为数据库 schema 的同步——的几种方式。

场景说明

我们在项目中经常遇到这样一种情况：项目组所有成员共用一个系统集成测试环境（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数据库的同步。

本文所使用的环境为：

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环境中，执行以下命令：

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。

格式化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
}