dbload导入数据脚本

脚本可一次导入单张表或者多张表数据

 

#!/usr/bin/env sh
#
# File: dbload.sh
# Creation Date: 10/09/12
# Last Modified:  2013/6/14 11:02:29
# Purpose:脚本可一次导入单张表或者多张表数据
#
usage()
{
echo "=================使用说明=================== "
echo "Usage: "
echo " `basename $0` -a 目录 [-d] [-n] "
echo " `basename $0` -s 表名 [-d] [-n] "
echo " "
echo "Options: "
echo " -a 目录 导入多张表,必须指定多张表数据目录的绝对路径. "
echo " -s 表名 导入一张表,必须指定该表数据文件的绝对路径. "
echo " -d 数据库名字 默认为tnmsdb2 "
echo " -n 导入行数 默认为10000 "
echo " "
echo "例子: "
echo "导入多张表: "
echo " `basename $0` -a /home/informix/tnmsdb2 "
echo "导入一张表: "
echo " `basename $0` -s /home/informix/circuit "
echo "指定数据库和导入行数: "
echo " `basename $0` -s /home/informix/circuit -d tnmsdb2 -n 1000 "
exit 0
}
#
while getopts "a:s:d:n:" OPTION
do
case $OPTION in
a) Dir="${OPTARG}"
;;
s) TF="${OPTARG}"
;;
d) DN="${OPTARG}"
;;
n) number="${OPTARG}"
;;
*|\?) usage
;;
esac
done
#创建存放控制文件目录
if [ ! -d $HOME/ctrfile ];then
echo "Create control file directory."
mkdir $HOME/ctrfile
else
echo "$HOME/ctrfile is exist."
fi
#定义控制文件目录、导入脚本、导入日志名字变量
Ctrpath=$HOME/ctrfile
TNfile=$Ctrpath/TNfile
dbload=$Ctrpath/dbload.sh
dbloadlog=$Ctrpath/dbload.log
#定义默认数据库名字
if [ -z $DN ];then
DN=tnmsdb2
fi
dbname=`onstat -d|grep 'N B'|awk '{print $10}'|grep -l $DN`
if [ -z $dbname ];then
echo $DN is not exist.
exit 0
fi
#定义默认每次导入行数
if [ -z $number ];then
number=10000
fi
#判断输入参数是否正确
if [ $# = 0 ];then
echo "error:`basename $0` parameter is error"
usage
fi
#
if [ ! -d $Dir ];then
echo error:Enter the directory is not exist.
exit 0
fi
#
if [ ! -f $TF ];then
echo error:Enter the tabfile is not exist.
exit 0
fi
#数据导入函数
Impot()
{
chmod +x $dbload
echo "Begin backstage import data......"
nohup $dbload >$dbloadlog &
echo "Please check the log $dbloadlog."
}
#判断参数-a后面参数是否正确
if [ $2 = "$Dir" ];then
ls $Dir >$TNfile
echo "Begin create control file,Please wait......"
while read Tabname
do
#查询表的列数
echo "select ncols num from systables where tabname='$Tabname'"|dbaccess $DN>$Ctrpath/a
num=`awk 'NR==5{print $Tabname}' $Ctrpath/a`
#判断表列数是否为空
if [ ! -z $num ];then
#定义控制文件名字
CF=$Ctrpath/$Tabname.ctr
echo "FILE $Dir/$Tabname DELIMITER '|' $num;" >$CF
echo "INSERT INTO $Tabname;" >>$CF
echo 'echo `date`' Begin dbload $Tabname >>$dbload
echo "dbload -d $DN -c $CF -n $number -l '`date +%Y%m%d`'.log" >>$dbload
echo 'echo `date`' finished dbload $Tabname >>$dbload
fi
done<$TNfile
echo "create control file finished."
if [ -f $Ctrpath/a ];then
#删除临时文件
rm $TNfile $Ctrpath/a
fi
#判断参数-s后面参数是否正确
elif [ $2 = "$TF" ];then
#去除目录取文件名字
TF=`basename $TF`
#定义控制文件名字
CF=$Ctrpath/$TF.ctr
#查询表的列数
echo "select ncols num from systables where tabname='$TF'"|dbaccess $DN>a
num=`awk 'NR==5{print $TF}' $Ctrpath/a`
#判断表列数是否为空
if [ ! -z $num ];then
echo "FILE $2 DELIMITER '|' $num;" >$CF
echo "INSERT INTO $TF;" >>$CF
echo 'echo `date`' Begin dbload $TF >$dbload
echo "dbload -d $DN -c $CF -n $number -l '`date +%Y%m%d`'.log" >>$dbload
echo 'echo `date`' finished dbload $TF >>$dbload
fi
if [ -f $Ctrpath/a ];then
#删除临时文件
rm $Ctrpath/a
fi
else
echo "error:The input parameters is error"
usage
exit 0
fi

#判断是否需要后台执行导入命令,回车默认为y
echo "Import data[y]: y/n"
read execute
if [ -z $execute ];then
execute="y"
fi
if [ $execute = "y" ];then
Impot
else
echo "Please manually run the script."
echo "nohup $dbload >$dbloadlog &"
fi