需求
- 有若干个模块setup,smsdelivery,release,connect,smssubmit,callin,finish;每个模块对应一个目录,在目录中有01到31个子目录(文件夹),每个子目录中是需要导入到数据库中去的数据文件(txt文件)
- 已经导入的文件不再导
代码实现
#!/bin/bash
source /etc/profile
source ~/.bash_profile
export NLS_LANG=American_America.UTF8
prog_path=/utxt/softnew/scripts/axg_jk/as
progname=axg_aslogfile_load
logfile=$prog_path/proglog/$progname.log
pid=$$
work_type=$1
platform=`echo $2 | tr '[:lower:]' '[:upper:]'`
prefix_work_path="/uloc/axg/"${work_type}
work_path="${prefix_work_path}/setup/ ${prefix_work_path}/smsdelivery/ ${prefix_work_path}/release/ ${prefix_work_path}/connect/ ${prefix_work_path}/smssubmit/ ${prefix_work_path}/callin/ ${prefix_work_path}/finish/ "
db_srv="dfjr_new_axgmt_jk/czty_dfjr_new_axgmt_jk@AXG_JK"
rm_day=`date +%d -d "-8 day"`
error_path=${prefix_work_path}/error/
lockfile=$prog_path/proglog/$progname".lock"
flag=0
row_num=50
backup_path="${prefix_work_path}/backup/"
rm_path=$backup_path"$rm_day"
mkdir -p $prog_path/proglog/
mkdir -p ${backup_path}setup/{01..31}
mkdir -p ${backup_path}smsdelivery/{01..31}
mkdir -p ${backup_path}release/{01..31}
mkdir -p ${backup_path}connect/{01..31}
mkdir -p ${backup_path}smssubmit/{01..31}
mkdir -p ${backup_path}callin/{01..31}
mkdir -p ${backup_path}finish/{01..31}
mkdir -p ${error_path}
sleep_time=1
echo ${work_path} >>$logfile
log_f="$prog_path"/proglog/dfjr_msg_load${pid}.log
bad_f="$prog_path"/proglog/dfjr_msg_load${pid}.bad
lv_rows=10000
lv_bindsize=8192000
lv_readsize=8192000
Create_Sqlldr_Ctl(){
file_type=$1
ctl_file=""
case $file_type in
20)
file_type="setup"
if [ ! -f t_axg_${file_type}.ctl ]; then
cat>t_axg_${file_type}.ctl <<EOF
load data
infile *
append
into table t_axg_setup
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
TIME TIMESTAMP"yyyyMMddHH24MISSFF3",
CALLID ,
CALLERNUMBER ,
CALLEDNUMBER ,
SESSIONID ,
RESULT ,
CODE ,
MESSAGE ,
DELAY ,
SOURCE constant "$platform" ,
PRT1TYPE ,
CALLTYPE
)
EOF
fi
ctl_file=t_axg_${file_type}.ctl
;;
21)
file_type="smsdelivery"
if [ ! -f t_axg_${file_type}.ctl ]; then
cat>t_axg_${file_type}.ctl <<EOF
load data
infile *
append
into table t_axg_smsdelivery
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
TIME TIMESTAMP"yyyyMMddHH24MISSFF3",
CALLINGDN ,
CALLEDDN ,
MSGCODING ,
MSGCONTENT char(256),
CODE ,
MESSAGE ,
DELAY
)
EOF
fi
ctl_file=t_axg_${file_type}.ctl
;;
22)
file_type="release"
if [ ! -f t_axg_${file_type}.ctl ]; then
cat>t_axg_${file_type}.ctl <<EOF
load data
infile *
append
into table t_axg_release
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
TIME TIMESTAMP"yyyyMMddHH24MISSFF3",
CALLID ,
SESSIONID ,
RELEASECAUSE ,
RELEASEDIRECT ,
STARTTIME ,
ALERTINGTIME ,
CONNECTINGTIME,
RELEASETIME ,
CODE ,
MESSAGE ,
DELAY ,
SOURCE constant "$platform" ,
INVOKEID ,
PLAN ,
REASON
)
EOF
fi
ctl_file=t_axg_${file_type}.ctl
;;
40)
file_type="connect"
if [ ! -f t_axg_${file_type}.ctl ]; then
cat>t_axg_${file_type}.ctl <<EOF
load data
infile *
append
into table t_axg_connect
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
TIME TIMESTAMP"yyyyMMddHH24MISSFF3",
CALLID ,
SESSIONID ,
DISPLAYDN,
CALLEDDN,
CODEC ,
CODE ,
MESSAGE,
DELAY,
SOURCE constant "$platform" ,
INVOKEID,
CALLHANDLE
)
EOF
fi
ctl_file=t_axg_${file_type}.ctl
;;
41)
file_type="smssubmit"
if [ ! -f t_axg_${file_type}.ctl ]; then
cat>t_axg_${file_type}.ctl <<EOF
load data
infile *
append
into table t_axg_smssubmit
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
TIME TIMESTAMP"yyyyMMddHH24MISSFF3",
CALLINGDN,
CALLEDDN ,
MSGCODING ,
MSGCONTENT char(256),
WAY,
SMCGT,
CODE,
MESSAGE,
DELAY
)
EOF
fi
ctl_file=t_axg_${file_type}.ctl
;;
91)
file_type="callin"
if [ ! -f t_axg_${file_type}.ctl ]; then
cat>t_axg_${file_type}.ctl <<EOF
load data
infile *
append
into table t_axg_callin
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
TIME TIMESTAMP"yyyyMMddHH24MISSFF3",
APPKEY,
TS TIMESTAMP"yyyyMMddHH24MISSFF3",
SUBID,
APPNAME,
SERVICETYPE,
CALLTYPE ,
TELX ,
FROMDN ,
TODN ,
CALLID,
CODE ,
MESSAGE,
DELAY
)
EOF
fi
ctl_file=t_axg_${file_type}.ctl
;;
92)
file_type="finish"
if [ ! -f t_axg_${file_type}.ctl ]; then
cat>t_axg_${file_type}.ctl <<EOF
load data
infile *
append
into table t_axg_finish
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
TIME TIMESTAMP"yyyyMMddHH24MISSFF3",
APPKEY ,
TS TIMESTAMP"yyyyMMddHH24MISSFF3",
SUBID,
MSGDGT ,
APPNAME ,
APPTRANSID,
SERVICETYPE,
TELX ,
FROMDN ,
TODN ,
CALLSTART,
CALLANSWER,
CALLEND ,
CALLID ,
STATUS ,
READURL ,
CALLTIME ,
CALLTYPE ,
GROUPID ,
CODE ,
MESSAGE,
DELAY
)
EOF
fi
ctl_file=t_axg_${file_type}.ctl
;;
*)
ctl_file=""
echo "File type ${file_type} is not exists sqlldr ctl file!" >>$logfile
esac
echo "${ctl_file},${file_type}"
}
cd $prog_path
THE_DATE=`date +"%Y-%m-%d %H:%M:%S"`
echo "[$THE_DATE $progname]:Start $progname pid:$pid......" >> $logfile
lock_flag=`find $prog_path -name $progname.lock|wc -l`
if test $lock_flag -eq "0"; then
touch $lockfile
else
echo "$lock_flag" >>$logfile
THE_DATE=`date +"%Y-%m-%d %H:%M:%S"`
echo "[$THE_DATE $convert_name Abnormal exit]:end $progname pid:$pid......" >> $logfile
exit 1
fi
while lock_flag=1
do
lock_flag=`find $prog_path -name $progname.lock|wc -l`
if test $lock_flag = "0"; then
echo "$lock_flag $infile" >>$logfile
THE_DATE=`date +"%Y-%m-%d %H:%M:%S"`
echo "[$THE_DATE $convert_name Normal exit]:end $progname pid:$pid......" >> $logfile
exit 1
fi
for par_dir in $work_path
do
pre_day=`date +%d -d "-1 day"`
day=`date +"%d"`
for infile in `find ${par_dir}${pre_day} ${par_dir}${day} -name \*.txt |sort -t_ -k3`
do
infile_full_path=$infile
infile=`basename $infile`
first_letter=`echo $infile |cut -d_ -f2 |cut -c2-3`
file_time=`echo $infile | cut -d_ -f3 | cut -c2-9`
file_list=$prog_path/"axg_msg_"${file_time}_${first_letter}".list"
[ -f $file_list ]
if [ $? -eq 1 ]; then
>$file_list
rm_day=`date +%d -d "-8 day"`
find ${backup_path}*/${rm_day} -name \*.txt -exec rm -f {} \;
echo "find ${backup_path}*/${rm_day} -name \*.txt -exec rm -f {} \;" >>$logfile
rm_list=`date +%Y%m%d -d "-8 day"`
rm -f "axg_msg_"${rm_list}"*.list"
fi
loaded_flag=`grep $infile $file_list | wc -l`
if [ "$loaded_flag" -gt "0" ]; then
continue
fi
if [ -s $infile_full_path ]; then
file_type_ctl_file=`Create_Sqlldr_Ctl ${first_letter}`
echo ${file_type_ctl_file}
ctl_file=`echo $file_type_ctl_file | cut -d, -f1`
file_type=`echo $file_type_ctl_file | cut -d, -f2`
if [ -f ${ctl_file} ]; then
dos2unix $infile_full_path >/dev/null 2>&1
if (('60'==${first_letter}));then
sqlldr userid="$db_srv_for_callcdr" control="$prog_path"/${ctl_file} data=$infile_full_path log="$log_file_for_callcdr" bad="$bad_file_for_callcdr" rows="$lv_rows" bindsize="$lv_bindsize" readsize="$lv_readsize" direct=n >/dev/null 2>&1
else
sqlldr userid="$db_srv" control="$prog_path"/${ctl_file} data=$infile_full_path log="$log_f" bad="$bad_f" rows="$lv_rows" bindsize="$lv_bindsize" readsize="$lv_readsize" direct=n >/dev/null 2>&1
fi
if [ $? -eq 0 ]; then
echo $infile >>$file_list
backup_day=`date -d ${file_time} +"%d"`
cur_backup_path=${backup_path}${file_type}/${backup_day}
mv -f ${infile_full_path} ${cur_backup_path}
else
cat $log_f >>$logfile
mv -f ${infile_full_path} ${error_path}
fi
fi
fi
done
done
sleep "$sleep_time"
done