1、公共环境设置文件
#!/bin/sh
#===============================================================================
# SCRIPT ID : EEOINIT
# SCRIPT NAME : 公共环境设置
# CREATE DATE :
# AUTHOR : @清风徐来@
# COPYRIGHT : @清风徐来@
# VARIABLES
# NONE
#===============================================================================
#exprot命令可新增，修改或删除环境变量，供后续执行的程序使用。export的效力仅及于该次登陆操作
PROD="/xxx/xx/xx";export PROD
#PATH（共有的shell）
SH_PATH=${PROD}"/bin/";export SH_PATH #SH_PATH=${PROD}"/bin/sh/"
#PATH（JAVA-SOURCE）
JAVA_SRC_PATH=${PROD}"/bin/java/";export JAVA_SRC_PATH
#PATH（LOG-PATH）
LOG_PATH=${PROD}"/data/log";export LOG_PATH #LOG_PATH=${PROD}"/log/"
#PATH（IF-FILE）
IF_PATH=${PROD}"/data/if/in/";export IF_PATH
#PATH（SQL-FILE）
SQL_PATH=${PROD}"/etc/sql/";export SQL_PATH
#PATH（SQLLOADER）使用sqlloader向oracle数据库中插入数据
LOADER_PATH=${PROD}"/etc/";export LOADER_PATH #LOADER_PATH=${PROD}"/etc/sqlloader/"
#FILE-NAME(LOG)
LOG_FILE_EEO="EEO.log";export LOG_FILE_EEO
LOG_FILE_EEP="EEP.log";export LOG_FILE_EEP
LOG_FILE_EEQ="EEQ.log";export LOG_FILE_EEQ
LOG_FILE_EEM="EEM.log";export LOG_FILE_EEM
LOG_FILE_EEN="EEN.log";export LOG_FILE_EEN
#JOB开始时间
SYSDATE=`date '+%y%m%d%H%M%S'`
#JOB-ID
SESSIONID=${JOBID}${SYSDATE}
#DB连接变量
DBCONNECT="username/passwd@dbid";export DBCONNECT
#DB-用户名
DBUSER="username";export DBUSER
#DB-密码
DBPASS="passwd";export DBPASS
#JOB正常结束
JOB_EXIT_NORMAL=0;export JOB_EXIT_NORMAL
#JOB异常结束
JOB_EXIT_ERROR=1;export JOB_EXIT_ERROR
#-----Oracle関連-----
#设置oracle字符集
NLS_LANG=CHINESE_CHINA.AL32UTF8
export NLS_LANG
#oracle根目录
ORACLE_BASE=/opt/app/oracle
export ORACLE_BASE
ORACLE_HOME=${ORACLE_BASE}/product/11.2.0/dbhome_1
export ORACLE_HOME
LD_LIBRARY_PATH=${ORACLE_HOME}/product/11.2.0/dbhome_1/network/lib
export LD_LIBRARY_PATH
TNS_ADMIN=${ORACLE_HOME}/product/11.2.0/dbhome_1/network/admin/
export TNS_ADMIN
LANG=zh_CN.UTF-8
export LANG
#####################################################
#case用法
#####################################################
JOBCATEGORY=`echo ${JOBID} | cut -c1-3`
case ${JOBCATEGORY} in
EEM)
LOG_FILE=${LOG_FILE_EEM}
;;
EEN)
LOG_FILE=${LOG_FILE_EEN}
;;
EEO)
LOG_FILE=${LOG_FILE_EEO}
;;
EEP)
LOG_FILE=${LOG_FILE_EEP}
;;
*)
LOG_FILE=${LOG_FILE_EEQ}
;;
esac
2、公共函数shell
#!/bin/sh #=============================================================================== # SCRIPT ID : EEOCOMMON # SCRIPT NAME : 公共函数 # CREATE DATE : # AUTHOR : @清风徐来@ # COPYRIGHT : @清风徐来@ # VARIABLES # NONE #=============================================================================== #------------------------------------------------------------------------------- #=============================================================================== # 函数名：doLogWrite # 说明 ：log输出 # 参数 ： # 返回值：no #=============================================================================== doLogWrite() { DATE=`date +"%Y/%m/%d %H:%M:%S"` echo "${DATE},${JOBID},${SESSIONID},$1,${STEPID}" >> ${LOG_PATH}/${JOBID}.log }
3.job文件
#!/bin/sh #============================================================================== # # JobName : INSERTDATE001 # Description : 使用sqlLoader向oracle插入数据 # creatDate : # author : # copyright : # Change hist : # Owner : # Variables : # #=============================================================================== #=============================================================================== # 初始化模块 #=============================================================================== ##### 环境设置 ##### #JOB ID JOBID=`basename $0`;export JOBID #stepID 标示shell运行阶段 STEPID="" #初始化 FILE_PATH="/XXXXX/XXXXX/";export FILE_PATH #公共shell文件名定义 BIZINIT="BIZINIT";export BIZINIT #公共函数文件名 BIZCOMMON="BIZCOMMON";export BIZCOMMON #sqlloader要导入的文件 IF_FILE="xxxxxx.dat";export IF_FILE #ctl文件名 CTL_FILE="INSERTDATE001";export CTL_FILE #记录插入数据的行数 REC_CNT="";export REC_CNT #待机时间 WAIT_TIME="180";export WAIT_TIME #待机时间循环次数 REPEAT_COUNT="5";export REPEAT_COUNT #数据每行的长度 RECSIZE=20;export RECSIZE #=============================================================================== # 环境设置调用公共文件 #=============================================================================== STEPID="00" #--- 公共环境文件执行 --- DATE=`date +"%Y/%m/%d %H:%M:%S"` if [ -f ${FILE_PATH}${BIZINIT} ] then . ${FILE_PATH}${BIZINIT} ret=$? if [ ${ret} -ne 0 ] then echo "${DATE},${JOBID},Exe Error ${BIZINIT} [Return Code - ${ret}],${STEPID}" exit 1 fi else echo "${DATE},${JOBID},Not Exist ${BIZINIT},${STEPID}" exit 1 fi #--- 公共函数文件 --- DATE=`date +"%Y/%m/%d %H:%M:%S"` if [ -f ${FILE_PATH}${BIZCOMMON} ] then . ${FILE_PATH}${BIZCOMMON} ret=$? if [ ${ret} -ne 0 ] then echo "${DATE},${JOBID},Exe Error ${BIZCOMMON} [Return Code - ${ret}],${STEPID}" exit ${JOB_EXIT_ERROR} fi else echo "${DATE},${JOBID},Not Exist ${BIZCOMMON},${STEPID}" exit ${JOB_EXIT_ERROR} fi #=============================================================================== # job开始 #=============================================================================== ##### JOB-start ##### doLogWrite "INFO, JOB START" #=============================================================================== # Step10 检查文件是否存在 #=============================================================================== STEPID="10" #step doLogWrite "INFO, STEP${STEPID} START" #--- .ctl文件check --- if [ ! -f ${LOADER_PATH}${CTL_FILE}.ctl ] then doLogWrite "ERROR, Not Exist ${CTL_FILE}.ctl" exit ${JOB_EXIT_ERROR} fi #--- 数据文件存在check --- CNT=0 while : do if [ -f ${IF_PATH}${IF_FILE} ] then break fi CNT=`expr ${CNT} + 1` doLogWrite "INFO, Repeat Count ${CNT}" if [ "${CNT}" -eq "${REPEAT_COUNT}" ] then break else sleep ${WAIT_TIME} fi done #--- 处理结果判断 --- case ${CNT} in #数据文件不存在 ${REPEAT_COUNT}) doLogWrite "ERROR, Not Exist ${IF_FILE}" exit ${JOB_EXIT_ERROR};; #数据文件存在 *) doLogWrite "INFO, Exist ${IF_FILE}" esac #解压处理 #gzip -cd ${IF_PATH}${IF_FILE}.gz > ${IF_PATH}${IF_FILE} #--- 处理结果判断 --- #ret=$? #case ${ret} in #0) doLogWrite "INFO, Success gzip ${IF_FILE}.gz";; #*) doLogWrite "ERROR, Fail gzip ${IF_FILE}.gz. ErrCD=${ret}" #exit ${JOB_EXIT_ERROR};; #esac #数据文件行数 REC_CNT=`wc -l ${IF_PATH}${IF_FILE}|awk '{print $1}'` if [ "${REC_CNT}" -eq "0" ] then doLogWrite "ERROR, ${IF_FILE} Record Count Zero" #exit ${JOB_EXIT_ERROR} fi #数据文件格式check FILESIZE=`ls -l ${IF_PATH}${IF_FILE}|awk '{print $5}'` DATE=`date +"%Y%m%d"` echo "===FILE SIZE ="${FILESIZE} >> ${LOG_PATH}/${JOBID}.log if [ `expr ${FILESIZE} % ${RECSIZE}` -ne 0 ] then DATE=`date +"%Y%m%d"` echo ${IF_FILE}"数据文件格式错误" >> ${LOG_PATH}/${JOBID}.log #echo "FILE SIZE ERROR!!" >> ${LOG_PATH}/${JOBID}_${DATE}.log exit ${JOB_EXIT_ERROR}; fi #--- SQL*Loader执行 --- if [ "${REC_CNT}" -ne "0" ] then doLogWrite "INFO, SQL*Loader Start" DATE=`date +"%Y%m%d%H%M%S"` ${ORACLE_HOME}/bin/sqlldr control=${LOADER_PATH}${CTL_FILE}.ctl, \ log=${LOG_PATH}${IF_FILE}_${DATE}.log, \ bad=${LOG_PATH}${IF_FILE}_${DATE}.bad, \ data=${IF_PATH}${IF_FILE}, \ userid=${DBCONNECT}, \ errors=999 fi #--- 处理结果判断 --- ret=$? case ${ret} in 0) doLogWrite "INFO, ${IF_FILE} SQL*Loader Success";; 1) doLogWrite "FATAL, ${IF_FILE} SQL*Loader Fail" 2) doLogWrite "WARN, ${IF_FILE} SQL*Loader Warning End";; esac DATE=`date +"%Y%m%d"` mv -f "${IF_PATH}${IF_FILE}" "${IF_PATH}bak_${IF_FILE}" doLogWrite "INFO, STEP${STEPID} END" #=============================================================================== # 结束处理 #=============================================================================== STEPID="99" doLogWrite "INFO, JOB END" exit ${JOB_EXIT_NORMAL}
4、ctl文件
--CREATE : --AUTHOR : OPTIONS ( READSIZE = 20971520, ROWS = -1, SILENT = FEEDBACK ) LOAD DATA CHARACTERSET JA16SJIS TRUNCATE INTO TABLE BZ_T_W_ORD_DAY ( USERNAME POSITION(0001:0003) CHAR "RTRIM(:NIKNAME ,' ')", AGE POSITION(0004:0015) CHAR "RTRIM(:NIKNAME ,' ')", PASSWD POSITION(0016:0029) CHAR "RTRIM(:NIKNAME ,' ')", NIKNAME POSITION(0030:0039) CHAR "RTRIM(:NIKNAME ,' ')", )
