gbase8s之逻辑导出导入脚本（完美版本）

该脚本dbexport.sh用于快速导出库和导入库（使用多并发unload，和多并发dbload的方式） 

#!/bin/sh
#脚本功能：将数据导出成文本，迁移至其他实例
#最后更新时间：2023-12-19
#使用方法：
#1.执行该脚本
#2.输入要导出的数据库名称后回车
#3.导出程序在后台执行，可执行 ps -ef|grep dbaccess 监控是否所有表都导出完毕
#4.导出过程中，会在数据文件夹内自动生成数据导入 importdb.sh，执行该脚本可将数据导入至其他实例
#5.修正三个问题，第一个是外键的自动处理；第二个用dbload替换load解决了长事务的问题；第三实现了并行的dbload
#6.注意aix平台没有sed -i参数，可能会有问题，需要单独处理
#7.通过dbload后台执行的方式，并行加载数据，不建议同时使用dbaccess dbname idx.sql。这样可能会带来先创建索引，后倒入数据的现象checkRunDba(){RUNDBA=$(ps -ef | grep dbaccess | grep -v grep | wc -l)if [ ${RUNDBA} -ge 20 ]; thenreturn 1elsereturn 0fi
}if [ $# -lt 1 ]; thenread -p "Please input database name: " DBNAME
elseDBNAME=$1
fiBACKDIR=BAK-${DBNAME}-$(date +%Y%m%d%H%M)
mkdir -p $BACKDIR/ctl
mkdir -p $BACKDIR/log
cd $BACKDIRexport DBDATE=Y4MD-
# env file
env | egrep '(DB_LOCALE|CLIENT_LOCALE|GL_|DBDATE)' | awk -F'=' '{if($0~/ /){print "export "$1"=\047"$2"\047"}else{print "export "$1"="$2}}' > ${DBNAME}_env_filedbschema -d ${DBNAME} -q -ss ${DBNAME}.sql
if [ ! $? -eq 0 ]; thenecho "Dbschema out error."exit 1
fidbaccess ${DBNAME} - << EOF >/dev/null 2>&1unload to BAK_tabname.unl delimiter ' 'SELECT t.tabname,t.ncols, p.nrows::int8 nrowsFROM systables t, sysmaster:sysptnhdr pWHERE t.tabid > 99AND t.tabtype = 'T'AND t.partnum = p.partnumUNIONSELECT t.tabname,t.ncols,sum(p.nrows)::int8 nrowsFROM systables t, sysfragments f, sysmaster:sysptnhdr pWHERE t.tabid > 99AND t.tabtype = 'T'AND t.tabid = f.tabidAND f.fragtype = 'T'AND f.partn = p.partnumGROUP BY 1,2;
EOFwhile read TAB NCOL NROWS
do
{dbaccess ${DBNAME} - << EOF >/dev/null 2>&1set isolation to dirty read;unload to ${TAB}.unl select * from ${TAB};
EOF
}&while truedocheckRunDbaif [ $? -eq 0 ]; thenbreakelsesleep 5fidone
done < BAK_tabname.unl# 建表语句和建索引语句分离，允许去除主键、约束名称
echo "set pdqpriority 96;" > IDX_${DBNAME}.sql
sed -n '/revoke usage /,$p' ${DBNAME}.sql >> IDX_${DBNAME}.sql
sed -i '/revoke usage /,$d' ${DBNAME}.sql
#sed 's/check \(.*\) constraint .*,/check \1,/g;s/unique \(.*\) constraint .*,/unique \1,/g;s/primary key \(.*\) constraint .*,/primary key \1,/g' ${DBNAME}.sql > nocon_${DBNAME}.sql
#if [ -f nocon_${DBNAME}.sql ]; then
#  sed -i 's/check \(.*\) constraint .*/check \1/g;s/unique \(.*\) constraint .*/unique \1/g;s/primary key \(.*\) constraint .*/primary key \1/g' ${DBNAME}.sql nocon_${DBNAME}.sql
#fi# 导出注释
dbaccess ${DBNAME} - << EOF >/dev/null 2>&1-- tabcomm, delimiter '\t' (ctrl + i)unload to _tmp_table_comment.unl delimiter '  'select 'comment on table ' || tabname || ' is ''' || replace(comments, chr(39), '''''') || ''';' as comment from syscomments;-- colcomm, delimiter '\t' (ctrl + i)unload to _tmp_column_comment.unl delimiter '  'select 'comment on column ' || tabname || '.' || colname || ' is ''' || replace(comments, chr(39), '''''') || ''';' as commentfrom syscolcomments;
EOFif [ -f _tmp_table_comment.unl ]; thencat _tmp_table_comment.unl > COMM_${DBNAME}.sqlrm -f _tmp_table_comment.unl
fiif [ -f _tmp_column_comment.unl ]; thencat _tmp_column_comment.unl >> COMM_${DBNAME}.sqlrm -f _tmp_column_comment.unl
ficat << EOF  > importdb.sh
#!/bin/sh
# filename : importdb.shcheckRunDbl(){RUNDBA=\$(ps -ef | grep 'dbload \-d ${DBNAME}' | grep -v grep | wc -l)if [ \${RUNDBA} -gt \${1:-20} ]; thenreturn 1elsereturn 0fi
}if [ -f ${DBNAME}_env_file ]; then. ./${DBNAME}_env_file
fidbaccess ${DBNAME} ${DBNAME}.sql 2>${DBNAME}_error_run.log
if [ ! \$? -eq 0 ]; thenecho "Load schema error! Please check if Database [ ${DBNAME} ] exists, or/and DDL error/warning at ${DBNAME}_error_run.log. "echo "Some version has 'create implicit cast ... ' in ${DBNAME}.sql file, you need to delete them. "exit 1
fiawk '{sum+=$3}END{printf("Rows: %d for database: ${DBNAME} will load.\n",sum)}' BAK_tabname.unlwhile read TAB NCOL NROWS
docat << ! > ./ctl/\${TAB}.ctl 2>/dev/null
FILE \${TAB}.unl DELIMITER '|' \${NCOL};
INSERT INTO \${TAB};
!
done < BAK_tabname.unlwhile read TAB NCOL NROWS
do
{dbload -d ${DBNAME} -c ./ctl/\${TAB}.ctl -n 5000 -l ./log/\${TAB}.log
}&while truedocheckRunDblif [ \$? -eq 0 ]; thenbreakelsesleep 5fidone
done < BAK_tabname.unlwhile true
docheckRunDbl 0if [ \$? -eq 0 ]; thenbreakelsesleep 5fi
doneif [ -f IDX_${DBNAME}.sql ]; thendbaccess ${DBNAME} IDX_${DBNAME}.sql > ./log/IDX_${DBNAME}.log 2>&1if [ ! \$? -eq 0 ]; thenecho "Create index had error, check ./log/IDX_${DBNAME}.log"fi
fiif [ -f COMM_${DBNAME}.sql ]; thendbaccess ${DBNAME} COMM_${DBNAME}.sql > ./log/COMM_${DBNAME}.log 2>&1if [ ! \$? -eq 0 ]; thenecho "Comment on table had error, check ./log/IDX_${DBNAME}.log"fi
fiexit 0EOFchmod +x importdb.shexit 0

使用方式如下：

# 导出，生成目录 BAK-库名-时间
dbexport.sh 库名
# 在新环境下创建同名库
echo 'create database 库名 with buffered log in datadbs01' | dbaccess - -
# 导入，进入生成的 BAK-库名-时间 目录
importdb.sh