DB2数据库导出脚本

#!/bin/bash

v_month=`date +"%Y%m"`
v_today=`date +"%Y%m%d"`
v_home=/u02/backup/${v_month}/${v_today}
v_data=${v_home}/central_db2move_${v_today}
v_tar=${v_home}/central_db2move_${v_today}.tar
v_sql=${v_home}/central_script_${v_today}.sql

mkdir -p ${v_data}
mkdir -p ${v_data}/log
mkdir -p ${v_data}/msg

. /home/db2inst1/.profile

db2look -d testdb -i db2inst1 -w db2inst1 -a -e -o ${v_home}/central_script_${v_today}.sql

function genTabList()
{
db2 connect to testdb
db2 -x "SELECT tabname FROM syscat.tables where tabschema='DB2INST1' AND type='T' \
AND TABNAME NOT IN ('EVENT','EV_COMBINED','ALARM','PRINTABLE_EVENT','LO_DATAPT_STATE_CHANGE', \
'ERROR_LOG','DATALOG_DP_LOG_REPORT','EV_SOE','DR_DATAPOINT_DATA','EMS_CONSUMPTION','DATALOG_DP_LOG_TREND') \
AND TABNAME NOT LIKE '%P20%' " > ${v_home}/tab.lst
db2 terminate
}

function expTable()
{
tabName=$1
echo "Export table ${tabName}..."
db2 connect to testdb
echo "Start: " `date` > ${v_data}/log/exp_${tabName}.log
db2 -z ${v_data}/msg/exp_${tabName}.msg -v "EXPORT TO ${v_data}/${tabName}.del OF DEL SELECT * FROM ${tabName}"
echo "End: " `date` >> ${v_data}/log/exp_${tabName}.log
db2 terminate
}

genTabList

echo "Start: " `date` > ${v_data}/log/expData.log
while read tabName
do
expTable $tabName &
done < ${v_home}/tab.lst
wait
echo "End: " `date` >> ${v_data}/log/expData.log

tar -cf ${v_tar} ${v_data}

v_ret=$?

if [ "${v_ret}" == "0" ]
then
rm -rf ${v_data}
gzip ${v_tar}
fi

 

导入脚本

for i in `ls *.del`; do  tabname=`echo $i|awk -F '.' '{print $1}'`;  db2 load from $i of del replace into $tabname nonrecoverable > $tabname.log;  done