DB2数据库下 将A服务的数据导入B服务的数据库中 脚本有两个:
*注:该脚本千万不要在A环境中执行,应在B中执行,在B中创建编目来连接A机器,执行完成后删除编目。
-
movedata.sh
#!/bin/bash
#====================数据操控前需要配置的信息开始====================
soureIP="XX.XX.XX.XX" #A机器IP
soureport="XXXXX" #A数据库端口
soureDBname="XXX" #A数据库名称
sourename="XXX" #A用户名
sourePWD="XXX" #A密码
targetDB="XXX" #B数据库名
#====================数据操控前需要配置的信息结束====================
sDB="soureupp" #A数据库别名
nodeName="testnode" #节点名称
ID=$$
logfilelist="imv.log imvError.log load.log emv.log emvError.log export.log createTB.sql createTB.log a.sql" #定义使用的日志
#导出表结构
dblook()
{
echo "=======================导出表结构开始...======================="
db2look -d ${sDB} -i ${sourename} -w ${sourePWD} -e -a -t `cat list.list |tr "\n" " " ` -o a.sql > /dev/null
chk $?
echo "=======================导出表结构结束...======================="
}
#建立表结构
dbcreate()
{
echo "=======================建立表结构开始...======================="
db2 -tvf a.sql >createTB.log
echo "=======================建立表结构结束...======================="
}
#list导入
imvlist()
{
echo "========================根据list导入数据开始...======================="
if [ -f ./list.list ]
then
## dbcreate
count=0;
errcont=0;
for i in `cat list.list|tr -d " "`
do
db2 "load from ./ixf/$i.ixf of ixf insert into $i NONRECOVERABLE " >>load.log
if [ $? -eq 0 ]
then
echo "[Suss] $i 导入成功" >>imv.log
count=`expr $count + 1`
else
echo "[Err] $i 导入错误" >>imv.log
echo "[Err] $i 导入错误" >>imvError.log
errcont=`expr $errcont + 1`
fi
done
echo "已成功导入${count}张表 ${errcont}错误未导入"
else
echo "未找到list.list导入文件"
fi
echo "=======================根据list导入数据结束...======================="
}
#连接源库
connectSDB()
{
echo "连接源库..."
db2 connect to ${sDB} user ${sourename} using ${sourePWD} >/dev/null ;
chk $?
}
#连接目标库
connectTDB()
{
echo "连接目标库..."
db2 connect to ${targetDB} user ${sourename} using ${sourePWD} > connectd;
chk $?
}
#断开库
dbreset()
{
db2 connect reset >/dev/null ;
}
#错误信息页面
errormain()
{
echo "执行错误,请检查配置信息...."
read DUMMY
uncatlogDB
#exit
kill -9 ${ID}
}
#关键步骤错误检查处理
chk()
{
if [ ! $1 -eq 0 ]
then
errormain
fi
}
#全部导入
imvall()
{
echo "=======================DB全数据导入开始...======================="
db2move ${targetDB} load >>load.log
chk $?
echo "=======================DB全数据导入结束...======================="
}
#list导出
emvlist()
{
if [ ! -d ./ixf ]
then
mkdir ixf
fi
echo "=======================根据list导出数据开始...======================="
if [ -f ./list.list ]
then
dblook
count=0;
errcont=0;
for i in `cat list.list|tr -d " "`
do
db2 "export to ./ixf/$i.ixf of ixf select * from $i " >>export.log
if [ $? -eq 0 ]
then
echo "[Suss] $i 导出成功" >>emv.log
count=`expr $count + 1`
else
echo "[Err] $i 导出错误" >>emv.log
echo "[Err] $i 导出错误" >>emvError.log
errcont=`expr $errcont + 1`
fi
done
echo "已成功导出${count}张表 ${errcont}错误未导出"
else
echo "未找到list.list导入文件"
errormain
fi
echo "=======================根据list导出数据结束...======================="
}
#全部导出
emvall()
{
echo "=======================DB全数据导出开始...======================="
db2move ${sDB} export -u ${sourename} -p ${sourePWD} >>export.log
chk $?
echo "=======================DB全数据导出结束...======================="
}
#编目数据库信息
catlogDB()
{
echo "=======================DB编目开始...======================="
db2 catalog tcpip node ${nodeName} remote ${soureIP} server ${soureport} >/dev/null
db2 catalog db ${soureDBname} as ${sDB} at node ${nodeName} >/dev/null
db2 TERMINATE >/dev/null
echo "=======================DB编目结束...======================="
}
#反编目数据库信息
uncatlogDB()
{
echo "=======================DB反编目数据库开始...======================="
db2 uncatalog db ${sDB} >/dev/null
db2 uncatalog node ${nodeName} >/dev/null
db2 TERMINATE >/dev/null
echo "=======================DB反编目数据库结束...======================="
}
#清空日志
clearLog()
{
for file in $logfilelist
do
rmlog $file
done
}
rmlog()
{
if [ -f ./$1 ]
then
rm -f $1
fi
}
#导入导出界面
moveView()
{
tput clear
cat <<MAYDAY
*******************************************************
用户名: $USER 日期 :`date +%d/%m/%Y`
源库:${soureDBname} (${soureIP}) 目标库:${targetDB}
请选择数据移库方式
*******************************************************
1 根据list.list导出库数据
2 根据list.list导入数据库
0 退出
*******************************************************
MAYDAY
echo "请输入 [1,2,0],按回车键执行操作"
read choice
case $choice in
1)
connectSDB
emvlist
dbreset
#connectTDB
#imvlist
#dbreset
;;
2)
connectTDB
imvlist
dbreset
;;
0)
return
;;
*)
echo "\007输入错误,请按回车键返回主菜单重新输入"
read DUMMY
moveView
;;
esac
}
main()
{
#日志清空
clearLog
#编目数据库
catlogDB
#选择导入导出
moveView
#反编目数据
uncatlogDB
}
main
-
list.list
list.list是要导出的表名,每张表名占一行,例:
Tablename1
Tablename2
Tablename3
脚本实现了,将1、2脚本上传至B机器任意位置,执行1脚本 弹出可视化操作界面,选择操作项 OK导入导出完毕!