該腳本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
