LINUX中使用SHELL重跑DB2存储过程

#!/bin/sh
. $(dirname "$0")/public_s.sh
#张明伟
#本shell适合有传出参数的标准存储过程
#v1.1 张明伟 20150107

dt=$(date -d "-1 days " +"%Y%m%d")
log=${0##*/};
log=$HOME/zmw_data/logs/${log%%.*}_$dt.log

cd ~/zmw_data
lastyend=$pub_lastyearend;

#本日为1月27号，取上年底
if [ ${dt:4:4} -ge 127 ]
then
  i_dt_e=${dt:0:4}0101
  lastyend=$(date -d "$i_dt_e -1 days" +"%Y%m%d" )
fi;
echo lastyend is $lastyend | tee -a $log;

debug="N" #是否调试
bkflg="N" #表备份标志
datatype="M" #日期类型,M表示月数据,D表示日数据

dt_sub="";

#运行存储过程及判断是否成功，失败则循环运行直到成功
exec_tmpfile()
{
  proc_tmp=$1;  #存储过程
  date_tmpflg=$2; #日期参数
  extp=$3;      #扩展参数
 
  while true
  do
   #执行存储过程
   dtss1=$(date +"%Y%m%d%H%M%S")
   dtss=$(date +"%s")
   echo $dtss1 | tee -a $log;
   db2 connect to zxjxdbs >$pub_nul;
   tmp_rf=logs/$$_file;
   touch $tmp_rf;
   if [ ${#date_tmpflg} -eq 0 ];then
      db2 -x "call $proc_tmp(?,?,?,?$extp)" >$tmp_rf;
   else
      db2 -x "call $proc_tmp(?,?,?,?,$date_tmpflg$extp)" >$tmp_rf;
   fi;
   dtse1=$(date +"%Y%m%d%H%M%S")
   dtse=$(date +"%s")
   echo $dtse1 | tee -a $log;
   cat $tmp_rf>>$log;
   d_sub=$((dtse-dtss))
   echo "$proc_tmp(?,?,?,?,$date_tmpflg$extp)运行时长为:$((d_sub/3600))小时$((d_sub/60))分$((d_sub%60))秒" | tee -a $log;
   #结果判断
   #原失败判断标志改为成功判定标志
   stext="Parameter Value : 0";
   res_tmp=` grep -iE "Success|$stext" $tmp_rf | tr -d ' ' `;
   if [ "$res_tmp" != "" ];then
     #成功则进行下一步操作，否则继续运行
     echo "$proc_tmp 运行成功" | tee -a $log;
     rm -f $tmp_rf>$pub_nul;
     break;
   fi;

   echo ".....isquiet is $isquiet";
   [ "$isquiet" != "Y" ] && echo "是否需要跑其它的?";
   echo "$proc_tmp 运行失败，继续运行!" | tee -a $log;
   
   rm -f $tmp_rf>$pub_nul;
   sleep 15;
  done;
}

#调用函数
call_p()
{
  ar="$1"
  dts="$2"
  extendpss="$3"
  echo "存储过程参数为:$ar" | tee -a $log;
  echo "月日期参数为:$dts"  | tee -a $log;
  echo "附加参数为:$extendpss"  | tee -a $log;

  if [ ${#dts} -eq 0 ];then
    echo "db2 -x call $ar(?,?,?,?$extendpss)" | tee -a $log
    db2 connect to zxjxdbs>$pub_nul;
    if [ "$debug" != "Y" ];then
      exec_tmpfile $ar $dts;
    fi;
  fi;

  if [ ${#extendpss} -gt 0 -a ${#dts} -gt 0 ];then
    echo "db2 -x call $ar(?,?,?,?,$dts$extendpss)" | tee -a $log
    db2 connect to zxjxdbs>$pub_nul;
    if [ "$debug" != "Y" ];then
      exec_tmpfile $ar $dts "$extendpss";
    fi;
  fi;

  for d in  $dts 
  do
    echo d is $d.......
    if [ $d -lt 0 ];then
      d=",$d"
      echo "数据日期为：$d" | tee -a $log
    fi;
    dt_sub=$d; 
    i=0;

    
    for a in  $ar 
    do
      #echo -n "是否确定跑此存储过程:$a..[y/n]:"
      #read yn  
yn='Y';
      ryn=`echo $yn | tr 'a-z' 'A-Z' `
#      echo $ryn
      tb=${art[i]}

      if [ $d -eq $lastyend -a "$a" = "PC_ZLKH" ]
      then
        continue; 
      fi;

      a=` echo "$a" | tr 'a-z' 'A-Z' `;
      xpc=` echo "$xpc" | tr 'a-z' 'A-Z' `;
      
      if [ "$a" = "$xpc" ]
      then
        continue;
      fi;

      dtime=$(date +"%Y%m%d%H%M%S")
      db2 connect to zxjxdbs>$pub_nul;
      if [ ! -e "bak/${tb}_${d}*.del" -a "$tb" != "" -a "$bkflg" = "Y" -a "$debug" != "Y" ]
      then
        echo db2 "export to bak/${tb}_${d}_$dtime.del of del select * from $tb where datadate=$d" | tee -a $log; 
        if [ "$debug" != "Y" ]
        then
          db2 "export to bak/${tb}_${d}_$dtime.del of del select * from $tb where datadate=$d" | tee -a $log; 
          gzip bak/${tb}_${d}_$dtime.del &
        fi;
      fi;

      if [ "$ryn" = "Y" ];then
        echo "db2 -x call $a(?,?,?,?,$d$extendpss)............" | tee -a $log
        if [ "$debug" != "Y" ];then
          #db2 -x "call $a(?,?,?,?,$d)" | tee -a $log
          exec_tmpfile "$a" "$dt_sub" "$extendpss"
        fi;
      elif [ "$ryn" = "N" ];then
        echo "db2 -x call $a(?,?,?,?,$d$extendpss) is not run!" | tee -a $log
        if [ "$debug" != "Y" ];then
          #db2 -x "call $a(?,?,?,?,$d) is not run!" | tee -a $log
          exec_tmpfile "$a" "$dt_sub" "$extendpss"
        fi;
      elif [ "$ryn" = "Q" ];then
        echo "quit!"
        exit;
      fi;
      i=$((i+1))
      db2 connect reset>$pub_nul;
      db2 terminate>$pub_nul;
    done;
  done;
}
count_i=0;
datechk()
{
  dc_date=$1;

  if [ ${#dc_date} -gt 0 ]
  then 
   if [[ $dc_date -lt 19000101  ||  $dc_date -gt 29991231 ]]
   then
     echo "日期$dc_date无效!" | tee -a $log;
     count_i=$((count_i+1));
     exit;
   fi;
  fi;
}

#日期参数，一定要按顺序排列
#dtms=" 20130131 20130228 20130331 20130430 20130531 20130630 20130731 20130831 20130930 20131031 20131130 20131231 "
pub_datatype="M";

if [ $lastyend -eq $pub_lastmend ]
then
  getlastyearend $lastyend;
  lastyend="$pub_result";
fi;

fc_getdayebtw $lastyend $pub_lastmend
dtms="$pub_result";



#命令交互入口
while getopts "s:e:d:p:b:gt:i:x:z:jaE:" opt
do
  case "$opt" in 
   "s") strdt=$OPTARG;;  #开始日期
   "t") datatype=$OPTARG;;  #日期类型
   "p") procs=$OPTARG;;
   "x") extendp=$OPTARG;;
   "z") xpc=$OPTARG;;
   "e") enddt=$OPTARG;;
   "i") incr=$OPTARG;;  #时间步长
   "d") datadate=$OPTARG;;
   "j") jxflg="Y";; #每月27号绩效数据
   "g") debug="Y";;
   "b") bkflg=$OPTARG;;
   "a") autoflg="Y";;
   "E") excluop="$OPTRAG";;
   *) echo "Usage: ${0##*/} [-g] [-t datatype:M/D] [-p procname] [-a] [ -s startdate -e enddate] [-d datadate] "
      exit;;
  esac
done 

if [ "$jxflg" = "Y" -a "$bkflg" = "" ]
then
  bkflg="Y";
fi;

call_usage()
{
  echo "Usage: ${0##*/} 
         [-g] out the debug commands
         [-j] excute the managers d&l between yearstart and lastmonths 
         [-t datatype] M or D 
         [-p procname] procedurece anem 
         [-a] autobak flag Y/N 
         [-s startdate -e enddate] 
         [-d datadate] 
         [-x] extend arguments 
          "
}

datechk $strdt;
datechk $enddt;
datechk $datadate;

[ "$debug" = "Y" ] && log=$pub_null && echo "at debug status!";

echo | tee -a $log;
date | tee -a $log;

if [ $count_i -gt 0 ]
then
  echo "日期输入有误，请检查！" | tee -a $log;
  exit;
fi;
#1.重新跑绩效前的准备

#上月末
lastmon=${dt:0:6}01
lastmon=$(date -d "$lastmon -1 days " +"%Y%m%d")
lastwek=$(date -d "$dt -7 days " +"%Y%m%d")
lastday=$(date -d "$dt -1 days " +"%Y%m%d")

#若指定数据日期，则不运行以下过程
if [ "$strdt" != "" ]
then
 cut_dtms=0;

 for sub_dtms in $dtms
 do
   #echo $sub_dtms
   if [ $sub_dtms -ge $strdt ]
   then
     cut_dtms=$sub_dtms;
     break;
   fi;
 done;
 t_dtms=$cut_dtms" "${dtms##*${cut_dtms}};
 if [ "$enddt" != "" ]
 then
  t_dtms=${t_dtms%%${enddt}*}" "$enddt;
 fi;
 dtms="$t_dtms";
 #若指定数据日期，则重新写月日期参数变量
elif [ "$datadate" != "" ]
then
 strdt=$datadate
 enddt=$datadate
 dtms=$datadate;
elif [ "$autoflg" = "Y" ]
then
 strdt=$dt
 enddt=$dt
 dtms=$dt;
else
 echo "";
fi;

[ "$dtms" != "" ] && datatype="D";
[ "$jxflg" = "Y" ] && datatype="M";
[ "$incr" = "" ] && incr=1;

echo "datatype is $datatype.........."

[ "$jxflg" = "Y" -a "$strdt" = "" ] && strdt=$lastyend && enddt=$lastmon;
datatype=$(echo $datatype | tr 'a-z' 'A-Z');

echo "datatype is $datatype"

if [ "$datatype" = "D" -o "$datatype" = "M" ]
then
  if [ "$strdt" = "" -o "$enddt" = "" ]
  then
    echo "请输入开始时间和结束时间参数!"
    call_usage;
    exit;
  fi;
  
  ds_1=$strdt
  de_1=$enddt
  dtms="";
  while [ $ds_1 -le $de_1 ]
  do
    if [ "$datatype" = "M" ]
    then
      ds_1=${ds_1:0:6}01
      #echo ds_1 is $ds_1
      ds_1=$(date -d "$ds_1 + $incr months " +"%Y%m01" )
      ds_1=$(date -d "$ds_1 - $incr days " +"%Y%m%d" )
      #echo "datatype is $datatype and ds_1 is $ds_1"
    fi;

    [ $ds_1 -le $de_1 ] && dtms="$dtms $ds_1"
    ds_1=$(date -d "$ds_1 +$incr day " +"%Y%m%d");
  done;
  echo $dtms .....
fi;
#添加除了每个月末外的上周，昨天，今天
if [ "$jxflg" = "Y" -a "$strdt" = "$lastyend" -a "${lastday:0:4}" = "${lastyend:0:4}" ] 
then
  echo "开始算绩效了!!!" | tee -a $log;
  dtms="$dtms $lastwek $lastday $dt";
fi;

if [ "$procs" = "" -a "$strdt" = "$lastyend" ]
then
 #0.1分成关系重跑过程
 aro=( PC_GENEMPSPLTRLT )
 call_p "$aro" ;
 
 aro=( PC_EMPMSTKPI_DEPYS )
 call_p "$aro" ;
fi;

echo "新月日期参数为 dtms is :$dtms" | tee -a $log;

#2.跑入其它对公存储过程
aro=" PC_EMPWMSTKPI PC_EMPRMSTKPI PC_EMPMSTKPI_U PC_ACCRSKSTC2Q PC_ACCINFO_SHZX PC_KHJLFZYWJS PC_ZLKH PC_KHJL_GM"
if [ "$procs" != "" ]
then
  aro="$procs";
  extendps="$extendp";
fi;
#备份表数组
art=( PLAT_EMPWMSTKPI PLAT_EMPRMSTKPI PLAT_EMPMSTKPI_U PLAT_ACCINFOSH )

call_p "$aro" "$dtms" "$extendps"

[ "$jxflg" == "Y" ] && echo "绩效已经算完了!!!" | tee -a $log;
#3.交通费，张军的营销费
exit;

if [ "$datadate" = "" -o "$autoflg" = "Y" ];then
  datadate=$(date -d " -1 days"  +"%Y%m%d")
  strdt=$datadate
  enddt=$datadate
else 
  strdt=$datadate
  enddt=$datadate
fi;

if [ "$strdt" != "" -a "$enddt" = "" ];then
  echo "enddt is null,please input then -e argument"
  exit
fi;

while [ $strdt -le $enddt ]
do
  echo strdate is $strdt enddate is $enddt
  echo db2 -x "call pc_zlkh($strdt)" | tee -a $log;
  d=0
  i=1
  while [ $d -le 5 -a $i -lt 10 ]
  do
   if [ $i -gt 1 ];then
     i=$((i+1))
     sleep 300
   fi

   db2 connect to zxjxdbs>$pub_nul;
   db2 "export ${ard[i]} to ${art[i]}$dtime.del of del select * from ${ard[i]}$dtime where datadate=$strdt" | tee -a $log;
   dts=$(date +"%Y%m%d%H%M%S")
   db2 -x "call pc_zlkh($strdt)" >>$log;
   dte=$(date +"%Y%m%d%H%M%S")
   d=$((dte-dts))
   echo db2 -x "call pc_zlkh($strdt)" 运行第 $i 次 | tee -a $log;
   echo "pc_zlkh($strdt)运行时长为:$((d/3600))小时$((d/60))分$((d%60))秒" | tee -a $log;
   db2 connect reset>$pub_nul;
   db2 terminate>$pub_nul;
  done

  strdt=$(date -d "$strdt +1 days"  +"%Y%m%d")
done