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

    sed -e "s/^[    ]*//" \    -e "s/[     ][      ]*/ /g" \    ${OLDDBSCHEMA} | tr [:upper:] [:lower:] > ${OLDDBSCHEMATMP}    

# 根据数据库的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中存在的表的名称清单# $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}

# 根据数据库的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}	fidone

db2 -z result.txt -tvf unload.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"doneecho "--------------------------------------------------------------"

create bufferpool BF81 size 10000 pagesize 8 K

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