数据相关脚本

#!/bin/sh
. /home/db2inst1/.profile
#数据上报程序
#author huodaihao
#获得系统
DATE=$(date +%Y%m%d)
DATE_TIME=$(date +'%Y%m%d%H%M%S')
ORG="Z2017112000011"


DBNAME="pbctjuat"
 
USERNAME="db2admin"

PASSWORD="db2@admin"


#shell文件目录
SHELL_PATH=/home/tianjing/sh
#csv文件基本路径
CSV_PATH=/home/tianjing/csv
#csv文件路径永久路径
CSV_SAVE_PATH=/home/tianjing/savecsv
#日志存放路径
LOG_PATH=/home/tianjing/log
#定义一个正在执行
TMPFILE=/home/tianjing/sh/shell_csv.tmp

#检查是否有其他的实
if [ -e ${TMPFILE} ]
then
        echo "Other instance is running!"
        exit 0    #退出本脚本的执行
else
        touch ${TMPFILE} #新建一个临时文件

        chmod 600 ${TMPFILE} 
fi


#设置一个

rap "rm -f ${TMPFILE}; exit" 0 1 2 3 9 15

echo "`date +%Y-%m-%d\ %T` ------------START----------">>${LOG_PATH}/load_data_${DATE}.log
#连接数据库   输出日志信息
db2 connect to $DBNAME  user $USERNAME using $PASSWORD
echo "`date +%Y-%m-%d\ %T` -清空ods_temp下的T_SC_MERCHANT_INFO，T_SC_MERCHANT_IM_DETAIL，T_SC_MERCHANT_EX_DETAIL，T_RBYHZHXX表的数据">>${LOG_PATH}/load_data_${DATE}.log
db2 "delete from ODS_TEMP.T_SC_MERCHANT_INFO "
db2 "delete from ODS_TEMP.T_SC_MERCHANT_IM_DETAIL "
db2 "delete from ODS_TEMP.T_SC_MERCHANT_EX_DETAIL "
db2 "delete from ODS_TEMP.T_RBYHZHXX "
db2 commit

for file_all_name in ${CSV_PATH}/*.csv

do    
            
    echo "$file_all_name --------------"
    #获取csv文件名称
    CSV_NAME=$(basename "$file_all_name")
    echo "$CSV_NAME----"
    echo "`date +%Y-%m-%d\ %T` csv文件的文件名称是：${CSV_NAME}">>${LOG_PATH}/load_data_${DATE}.log

         # gbk --> utf-8
    if file "${file_all_name}"|grep -q UTF-8 >/dev/null ; 
        then
        echo "csv is utf-8" >> ${LOG_PATH}/load_data_${DATE}.log

       else     
         echo "csv is gbk to utf-8 success" >> ${LOG_PATH}/load_data_${DATE}.log

                iconv -c -f gbk -t utf-8 ${file_all_name}> ${LOG_PATH}/temp_${CSV_NAME}
                rm -f  ${file_all_name}
                mv ${LOG_PATH}/temp_${CSV_NAME}   ${CSV_PATH}/${CSV_NAME}
                
        fi
    #截取1个字
    FLAG=${CSV_NAME:0:6}
    ORG_CODE=${CSV_NAME:9:14}
    LOAD_DATE=${CSV_NAME:24:8}
    #商户基本信息表
    TEMPLATE_FILE_ID1='20160813173727372741975374175096280'
    #商户入金支付明细
    TEMPLATE_FILE_ID2='16081318351835185705507814252728000'
    #商户出金支付明细
    TEMPLATE_FILE_ID3='16081318360836823267029553581420000'
    #融宝商户对应账号的信息
    TEMPLATE_FILE_ID4='16081318365236529830235585519220000'
    sed -i '1d' $file_all_name
    sed -i "s/\//-/g" $file_all_name


    if [ $FLAG = "SSJBXX" ]
    then
        db2 "update DPS.T_SYS_COLLECTION_LOG set VERIFY_STATUS=1 where FISCAL_TERM='${LOAD_DATE}' and ORG_ID='${ORG_CODE}' and TEMPLATE_FILE_ID='${TEMPLATE_FILE_ID1}'"
        echo "`date +%Y-%m-%d\ %T` load 商户基本信息数据">>${LOG_PATH}/load_data_${DATE}.log
        echo "1111111111111111"
        #商户基本信息
        db2 "LOAD client FROM '${file_all_name}' OF DEL MODIFIED BY CODEPAGE=1208 COLDEL, DECPLUSBLANK DATESISO INSERT INTO ODS_TEMP.T_SC_MERCHANT_INFO  NONRECOVERABLE  "
        db2 commit
    fi 
    if [ $FLAG = "SHRJMX" ]
    then 
        db2 "update DPS.T_SYS_COLLECTION_LOG set VERIFY_STATUS=1 where FISCAL_TERM='${LOAD_DATE}' and ORG_ID='${ORG_CODE}' and TEMPLATE_FILE_ID='${TEMPLATE_FILE_ID2}'"
        echo "`date +%Y-%m-%d\ %T` load 入金支付明细">>${LOG_PATH}/load_data_${DATE}.log
        #入金支付明细
        db2 "LOAD client FROM '${file_all_name}' OF DEL MODIFIED BY CODEPAGE=1208 COLDEL, DECPLUSBLANK DATESISO INSERT INTO ODS_TEMP.T_SC_MERCHANT_IM_DETAIL  NONRECOVERABLE"
        db2 commit
    fi
     if [ $FLAG = "SSCJMX" ]
    then
        db2 "update DPS.T_SYS_COLLECTION_LOG set VERIFY_STATUS=1 where FISCAL_TERM='${LOAD_DATE}' and ORG_ID='${ORG_CODE}' and TEMPLATE_FILE_ID='${TEMPLATE_FILE_ID3}'"
        echo "`date +%Y-%m-%d\ %T` load 出金支付">>${LOG_PATH}/load_data_${DATE}.log
        #出金支付明细
        db2 "LOAD client FROM '${file_all_name}' OF DEL MODIFIED BY CODEPAGE=1208 COLDEL, DECPLUSBLANK DATESISO INSERT INTO ODS_TEMP.T_SC_MERCHANT_EX_DETAIL  NONRECOVERABLE"
        db2 commit
    fi        
    if [ $FLAG = "RBYHXX" ]
    then    
        db2 "update DPS.T_SYS_COLLECTION_LOG set VERIFY_STATUS=1 where FISCAL_TERM='${LOAD_DATE}' and ORG_ID='${ORG_CODE}' and TEMPLATE_FILE_ID='${TEMPLATE_FILE_ID4}'"
        echo "`date +%Y-%m-%d\ %T`  load荣宝信息">>${LOG_PATH}/load_data_${DATE}.log
        #融宝银行账户信息
    db2 "LOAD client FROM '${CSV_PATH}/${CSV_NAME}' OF DEL MODIFIED BY CODEPAGE=1208 COLDEL, DECPLUSBLANK DATESISO INSERT INTO ODS_TEMP.T_RBYHZHXX  NONRECOVERABLE"
        db2 commit
    fi
    mv -f ${CSV_PATH}/${CSV_NAME}  ${CSV_SAVE_PATH}    

done
    echo  "`date +%Y-%m-%d\ %T` load ods_temp success">>${LOG_PATH}/load_data_${DATE}.log
    db2 "update DPS.T_SYS_COLLECTION_LOG set VERIFY_STATUS=1 where FISCAL_TERM='${LOAD_DATE}' and ORG_ID='${ORG_CODE}' and TEMPLATE_FILE_ID='${TEMPLATE_FILE_ID1}'"
    db2 "update DPS.T_SYS_COLLECTION_LOG set VERIFY_STATUS=1 where FISCAL_TERM='${LOAD_DATE}' and ORG_ID='${ORG_CODE}' and TEMPLATE_FILE_ID='${TEMPLATE_FILE_ID2}'"
    db2 "update DPS.T_SYS_COLLECTION_LOG set VERIFY_STATUS=1 where FISCAL_TERM='${LOAD_DATE}' and ORG_ID='${ORG_CODE}' and TEMPLATE_FILE_ID='${TEMPLATE_FILE_ID3}'"
    db2 "update DPS.T_SYS_COLLECTION_LOG set VERIFY_STATUS=1 where FISCAL_TERM='${LOAD_DATE}' and ORG_ID='${ORG_CODE}' and TEMPLATE_FILE_ID='${TEMPLATE_FILE_ID4}'"

    db2 "call ODS.P_PAY_TYSHXX_WLJYMX('${LOAD_DATE}','${ORG_CODE}')"
    echo  "`date +%Y-%m-%d\ %T` load ods success">>${LOG_PATH}/load_data_${DATE}.log    

    db2 "call DW.P_PAY_TYSHXX('${LOAD_DATE}','${ORG_CODE}','','')"

    db2 "call DW.P_PAY_WLJYMX('${LOAD_DATE}','${ORG_CODE}','','')"

    echo  "`date +%Y-%m-%d\ %T` load DW success">>${LOG_PATH}/load_data_${DATE}.log
    db2 commit;
    db2 terminate;
    rm -f ${TMPFILE}
    
    

 

 

#!/bin/sh

#----------------------------------------------------
#功能：将文件上传到FTP文件服务器
#
#时间：2015-03-22
#----------------------------------------------------

#FTP地址
IP_ADDRESS="192.168.0.111"
#FTP端存放目录
REMOTE_PATH="/load_data/files/test_data"
#shell存放目录
BASE_PATH="/load_shell"
#本地需要上传的文件存放目录
LOCAL_PATH="/home/pbc-file-maxfiles"
#本地文件上传后备份存放目录
LOCAL_ZIP_PATH="/home/pbc-file-maxfiles/data_bak"
#FTPP用户名
USERNAME="db2inst1"
#FTP密码
PASSWORD="db2inst1"

#获取系统时间到天
DATE=$(date +%Y%m%d)

#echo $DATE

echo "`date +%Y-%m-%d\ %T` : ---------------------------START----------------------------------" >>$BASE_PATH/log/ftp_up_$DATE.log
echo "`date +%Y-%m-%d\ %T` : 开始上传文件" >>$BASE_PATH/log/ftp_up_$DATE.log

ftp -in <<EOF

open $IP_ADDRESS
user $USERNAME $PASSWORD
binary
cd $REMOTE_PATH

lcd $LOCAL_PATH

espv4 off
passive
mput "*.csv"

bye

EOF
echo "`date +%Y-%m-%d\ %T` : 上传文件结束" >>$BASE_PATH/log/ftp_up_$DATE.log

#获取系统时间到天
#DATE=$(date +%Y%m)

echo "`date +%Y-%m-%d\ %T` : 准备移动文件到 ${LOCAL_ZIP_PATH} 目录 " >>$BASE_PATH/log/ftp_up_$DATE.log

for file_all_zip_name in ${LOCAL_PATH}/*;do
        ZIP_FILE_NAME=$(basename "$file_all_zip_name")
        #
        temp_zip_file=$file_all_zip_name
     
    #截取文件名 
        TABLE_NAME_STR=${ZIP_FILE_NAME:0:4}
        ORG=${ZIP_FILE_NAME:4:14}
        DAY=${ZIP_FILE_NAME:18:8}
    MONTH=${ZIP_FILE_NAME:18:6}    
    FILE_NAME=${ZIP_FILE_NAME:0:26}

    if [ ! -d "$LOCAL_ZIP_PATH/$MONTH" ];
       then
            mkdir $LOCAL_ZIP_PATH/$MONTH
    fi
#    echo "`date +%Y-%m-%d\ %T` : 移动文件 ${ZIP_FILE_NAME}" >>$BASE_PATH/log/ftp_up_$DATE.log

    #将解压完的文件从临时文件夹移到永久保存文件夹
    mv -f $temp_zip_file $LOCAL_ZIP_PATH/$MONTH
done

echo "`date +%Y-%m-%d\ %T` : 文件移动完毕" >>$BASE_PATH/log/ftp_up_$DATE.log


#cd $LOCAL_ZIP_PATH
#if [ ! -d "$LOCAL_ZIP_PATH/$DATE" ];
#   then 
#    mkdir $LOCAL_ZIP_PATH/$DATE 
#fi
#mkdir $DATE

#将解压完的文件从临时文件夹移到永久保存文件夹
#mv -f $LOCAL_PATH/* $LOCAL_ZIP_PATH/$DATE
echo "`date +%Y-%m-%d\ %T` : ---------------------------END----------------------------------" >>$BASE_PATH/log/ftp_up_$DATE.log