EBS 中HOST主机并发请求模板
#!/bin/sh
#####################################################################################################
# 程序名称: 数据导入
#
# 文件名称: XXT_DATALOAD
#
# 描述:获取核心的数据文件，导入到数据库中
# Notes:
# 1) Copy all host (shell) programs to the correct XXT_TOP/bin
# 2) chmod +x programs
# eg:chmod +x XXT_DATALOAD
#
# Change History
# Date Author Version Change Reference
# ----------- ------------------ ------- ----------------------------------
# 20130901 huangrx 1.0 Create
######################################################################################################
PROG_NAME=${0}
LOGON=${1}
USER_ID=${2}
APP_USER_NAME=${3}
REQUEST_ID=${4}
#Date paremeter
FILE_DATE=${5}
echo "+---------------------------------------------------------------------------+"
echo "Program parameters are : "
echo " "
echo " Program name : " $PROG_NAME
echo " User ID : " $USER_ID
echo " Application user name : " $APP_USER_NAME
echo " Request ID : " $REQUEST_ID
echo " Accounting date : " $FILE_DATE
echo "+---------------------------------------------------------------------------+"
if [ "$FILE_DATE" = "" ] ; then
echo "Error: the Parameter of control date is null!"
exit 1;
fi
##FTPUSER从配置文件中获取核心数据文件存放的路径(ftpdir=<HOME路径>/interface_data)
CONFIG_PATH=$XXT_TOP/bin/config.ini
SHOME=`awk -F "=" '{if($1=="ftpdir") print $2}' $CONFIG_PATH`
##数据文件源路径
SOURCE_PATH=$SHOME
##数据文件目标路径
DESTINATION_PATH=$XXT_TOP/bin/interface_data
##SQLLDR所用的控制文件所在目录
CTRL_PATH=$XXT_TOP/bin/ctrl
##文件名前缀(不带扩展名及日期)
FILE_SUFFIX=rate
##文件名扩展格式
FILE_EXT=".dat"
##总循环次数
COUNT=120
##数据文件名
FILE_NAME=${FILE_SUFFIX}${FILE_DATE}${FILE_EXT}
##文件分割后的DATA文件
DATA_FILE=${FILE_SUFFIX}_data${FILE_DATE}.dat_split
##文件分割后的END文件
END_FILE=${FILE_SUFFIX}_end${FILE_DATE}.dat_split
##DATA文件的CTRL文件
DATA_CTRL=XXT_$(echo ${FILE_SUFFIX} | tr '[a-z]' '[A-Z]')_LOAD_DATA.ctrl
##END文件的CTRL文件
END_CTRL=XXT_$(echo ${FILE_SUFFIX} | tr '[a-z]' '[A-Z]')_LOAD_END.ctrl
##SQLLDR DATA文件时的错误文件
DATA_BAD=${FILE_SUFFIX}_data_${FILE_DATE}.bad
##SQLLDR DATA文件时的日志文件
DATA_LOG=${FILE_SUFFIX}_data_${FILE_DATE}.log
##SQLLDR END文件时的错误文件
END_BAD=${FILE_SUFFIX}_end_${FILE_DATE}.bad
##SQLLDR END文件时的日志文件
END_LOG=${FILE_SUFFIX}_end_${FILE_DATE}.log
##将FILE_DATE转换为YYYY-MM-DD
FILE_DATE=`echo $FILE_DATE |sed 's/-//g'`
##按照约定格式格式化FILE_DATE
FILE_DATE=`sqlplus -s $LOGON <<EOF
set echo off
set verify off
set head off
set feedback off
select to_char(to_date($FILE_DATE, 'yyyy-mm-dd hh24:mi:ss'), 'yyyymmdd') from dual;
EOF`
FILE_DATE=`echo $FILE_DATE |sed 's/ //g'`
echo "Step1--Start to fetch ${FILE_SUFFIX} files!"
cd ${DESTINATION_PATH}
if [ -d ${DESTINATION_PATH}/${FILE_DATE} ] ; then
cd ${FILE_DATE}
rm -rf ${DATA_FILE}
rm -rf ${END_FILE}
else
mkdir ${FILE_DATE}
cd ${FILE_DATE}
fi
while [ $COUNT -gt 0 ]
do
if [ -f ${SOURCE_PATH}/${FILE_NAME} ] ; then
cp -f ${SOURCE_PATH}/${FILE_NAME} ${DESTINATION_PATH}/${FILE_DATE}
break
fi
COUNT=`expr $COUNT - 1`
echo "loop--$COUNT times"
sleep 300
done
if [ $COUNT -eq 0 ] ; then
echo "${FILE_SUFFIX} files are not ready!"
echo "Please reload ${FILE_SUFFIX} files and run request again manually"
exit 1
else
echo "finish fetch ${FILE_SUFFIX} files successfully!"
fi
echo "Step2--Start to delete bad&log files already exist"
##删除已存在的错误文件和日志文件
cd ${DESTINATION_PATH}/${FILE_DATE}
rm -rf ${DATA_BAD}
rm -rf ${DATA_LOG}
rm -rf ${END_BAD}
rm -rf ${END_LOG}
echo "Step3--Start to split ${FILE_NAME}"
##将数据文件分割为DATA（数据）和END（数据记录总数）文件
cd ${DESTINATION_PATH}/${FILE_DATE}
sed '$d' ${FILE_NAME} > ${DATA_FILE}
cat ${FILE_NAME} | grep "^END!" > ${END_FILE}
echo "Step4--Start SQL*Loader"
##装载数据文件
echo "######Run SQL*Loader, LOAD $DATA_FILE INTO XXT_DATA_TEMP_B######"
sqlldr userid=${LOGON} control=${CTRL_PATH}/${DATA_CTRL} data=${DESTINATION_PATH}/${FILE_DATE}/${DATA_FILE} bad=${DESTINATION_PATH}/${FILE_DATE}/${DATA_BAD} log=${DESTINATION_PATH}/${FILE_DATE}/${DATA_LOG} direct=ture
##如果错误文件存在，说明发生了错误，退出
##bad文件只有在数据异常，Load不进去的时候，才会生成。语法报错，或文件不存在引发的错误并不生成bad文件
##SQLLDR命令返回状态
RETCODE=$?
if [ -f ${DESTINATION_PATH}/${FILE_DATE}/${DATA_BAD} ] ; then
echo "######## Error records are found, please check ${DATA_LOG} and fix them! #########"
exit 1
fi
if [ $RETCODE -ne 0 ]; then
echo "######## Program Error Or table and file not exist, please check ${DATA_LOG} and fix them! #########"
exit 1
fi
##装载数据记录总数文件
echo "######Run SQL*Loader, LOAD ${END_FILE} INTO XXT_DATA_END_B######"
##如果错误文件存在，说明发生了错误，退出
sqlldr userid=${LOGON} control=${CTRL_PATH}/${END_CTRL} data=${DESTINATION_PATH}/${FILE_DATE}/${END_FILE} bad=${DESTINATION_PATH}/${FILE_DATE}/${END_BAD} log=${DESTINATION_PATH}/${FILE_DATE}/${END_LOG} direct=ture
##SQLLDR命令返回状态
RETCODE=$?
if [ -f ${DESTINATION_PATH}/${FILE_DATE}/${END_BAD} ] ; then
echo "######## Error records are found, please check ${END_LOG} and fix them! #########"
exit 1
fi
if [ $RETCODE -ne 0 ]; then
echo "######## Program Error Or table and file not exist, please check ${END_LOG} and fix them! #########"
exit 1
fi
echo "Step5--Delete 7 days before files"
##只保留7天的历史数据
FILE_DATE_PRE=`sqlplus -s $LOGON <<EOF
set echo off
set verify off
set head off
set feedback off
select to_char(to_date($FILE_DATE,'yyyymmdd')-7,'yyyymmdd') from dual;
EOF`
FILE_DATE_PRE=`echo ${FILE_DATE_PRE} |sed 's/ //g'`
##得到7天前的数据文件的文件名
PRE_FILE_NAME=${FILE_SUFFIX}${FILE_DATE_PRE}${FILE_EXT}
##删除7天前的数据文件，包括来源和目标文件
rm -rf ${SOURCE_PATH}/{PRE_FILE_NAME}
rm -rf ${DESTINATION_PATH}/${FILE_DATE_PRE}/${DATA_FILE}
rm -rf ${DESTINATION_PATH}/${FILE_DATE_PRE}/${END_FILE}
