unload_rpt_table.sh

最新推荐文章于 2022-03-24 15:48:43 发布

师从风清扬

最新推荐文章于 2022-03-24 15:48:43 发布

阅读量368

点赞数

分类专栏： SHELL

SHELL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

#!/bin/ksh
##########################################################################################
#程序名称：unload_rpt_table.sh
#作者姓名：

#开发时间：2010-7-8
#功能说明：

##########################################################################################
cd $HOME/smp_run/smpser/bin
. public.sh.rc

####################################################################
# 函数：getlastday
# 目的：得到前N天的日期
# 输入: 要得到前几天的日期
# 返回: 前N天的日期 YYYYMMDD形式
####################################################################
getlastday()
{
    RDAY=${1}
    INPUTDATE=${2}
    YEAR=`echo $INPUTDATE|cut -c 1-4`
    MONTH=`echo $INPUTDATE|cut -c 5-6`
    DAY=`echo $INPUTDATE|cut -c 7-8`
    DAY=`expr $DAY - $RDAY`
    if [ "$DAY" -le 0 ] ; then
        MONTH=`expr $MONTH - 1`
        case $MONTH in
        1|3|5|7|8|10)
            DAY=`expr $DAY + 31`
            ;;
        4|6|9|11)
            DAY=`expr $DAY + 30`
            ;;
        2)
            #闰年计算
            DAY=`expr $DAY + 28`
            if [ `expr $YEAR % 400` -eq 0 ] ; then
                DAY=`expr $DAY + 1`
            elif [ `expr $YEAR % 100` -ne 0 -a `expr $YEAR % 4` -eq 0 ] ; then
                DAY=`expr $DAY + 1`
            fi
            ;;
        0)
            YEAR=`expr $YEAR - 1`
            MONTH="12"
            DAY=`expr $DAY + 31`
        esac
        if [ $MONTH -lt 10 ] ; then     #保证月份为MM形式
            MONTH="0"$MONTH
        fi
    fi
    if [ $DAY -lt 10 ] ; then           #保证天为DD形式
        DAY="0"$DAY
    fi
    LASTDATE=$YEAR$MONTH$DAY
}

# ====== main ==============================================
ProgName=`basename $0`

if [ "-$#" = "-1" ]; then
    processdate="$1"
elif [ "-$#" = "-0" ]; then
    processdate=`date '+%Y%m%d'`
else
    echo "please usage : $ProgName or $ProgName date"
    exit 1
fi

getlastday 1 $processdate
yesdate=$LASTDATE

#日志文件
LogFile=${LogDir}/unload_table.log

#程序启动日志
LogStart $ProgName

#每天只能运行一次
Flagfile=$HOME/smp_run/smpser/flag/unload_table_${processdate}.unl
if [ -f ${Flagfile} ]; then
    Log "Can't run ${ProgName} again today!"
    LogEnd $ProgName
    exit 1
fi

touch $Flagfile

#下载的文件存放目录
tempdir=${HOME}/smp_run/smpser/temp

Log "#################DBSERVERTYPE is ${DBSERVERTYPE}.."

Log "#################start unload bmpdbnode.."
filefirst=${tempdir}/${processdate}_bmpdbnode.list
if [ "-$DBSERVERTYPE" = "-INFORMIX" ]; then
    echo "unload to ${filefirst} SELECT BMPDBNODE,NODETYPE,DATASOURCE,VERSION FROM RPT_TB_BMPDBNODE WHERE NODETYPE = 0" | dbaccess $SMPDBNAME
elif [ "-$DBSERVERTYPE" = "-ORACLE" ]; then
    echo "set   heading   off
set linesize 260
set   pagesize   0
set   feedback   off
set   echo   off
set   VERIFY   off
set   trimout   on
set   trimspool   on
set   termout   off
spool   ${filefirst}
select BMPDBNODE||'|'||NODETYPE||'|'||DATASOURCE||'|'||VERSION||'|' FROM RPT_TB_BMPDBNODE WHERE NODETYPE = 0;
spool   off " > bmpdbnode.sql
echo "@bmpdbnode.sql" | sqlplus $SMPDBNAME/$SMP_ORA_DB_PASS@$ORACLE_INSTANCE >/dev/null 2>&1
fi
compress -cf ${filefirst} > "${filefirst}.Z"
Log "#################end unload bmpdbnode.."

Log "#################start unload bmpuserdbnode.."
filesecond=${tempdir}/${processdate}_bmpuserdbnode.list
if [ "-$DBSERVERTYPE" = "-INFORMIX" ]; then
    echo "unload to ${filesecond} SELECT BMPDBNODE,NODETYPE,DATASOURCE,VERSION FROM RPT_TB_BMPDBNODE WHERE NODETYPE = 1" | dbaccess $SMPDBNAME
elif [ "-$DBSERVERTYPE" = "-ORACLE" ]; then
    echo "set   heading   off
set linesize 260
set   pagesize   0
set   feedback   off
set   echo   off
set   VERIFY   off
set   trimout   on
set   trimspool   on
set   termout   off
spool   ${filesecond}
select BMPDBNODE||'|'||NODETYPE||'|'||DATASOURCE||'|'||VERSION||'|' FROM RPT_TB_BMPDBNODE WHERE NODETYPE = 1;
spool   off " > bmpuserdbnode.sql
echo "@bmpuserdbnode.sql" | sqlplus $SMPDBNAME/$SMP_ORA_DB_PASS@$ORACLE_INSTANCE >/dev/null 2>&1
fi
compress -cf ${filesecond} > "${filesecond}.Z"
Log "#################end unload bmpuserdbnode.."

Log "#################start unload bmpwithcbp.."
filethird=${tempdir}/${processdate}_bmpwithcbp.list
if [ "-$DBSERVERTYPE" = "-INFORMIX" ]; then
    echo "unload to ${filethird} SELECT RPT_TB_BMPDBNODE.BMPDBNODE, RPT_TB_BMPWITHCBP.CBP_NODE FROM RPT_TB_BMPWITHCBP,RPT_TB_BMPDBNODE WHERE RPT_TB_BMPDBNODE.NODETYPE = 0" | dbaccess $SMPDBNAME
elif [ "-$DBSERVERTYPE" = "-ORACLE" ]; then
    echo "set   heading   off
set linesize 260
set   pagesize   0
set   feedback   off
set   echo   off
set   VERIFY   off
set   trimout   on
set   trimspool   on
set   termout   off
spool   ${filethird}
select RPT_TB_BMPDBNODE.BMPDBNODE||'|'||RPT_TB_BMPWITHCBP.CBP_NODE||'|' FROM RPT_TB_BMPWITHCBP,RPT_TB_BMPDBNODE WHERE RPT_TB_BMPDBNODE.NODETYPE = 0;
spool   off " > bmpwithcbp.sql
echo "@bmpwithcbp.sql" | sqlplus $SMPDBNAME/$SMP_ORA_DB_PASS@$ORACLE_INSTANCE >/dev/null 2>&1
fi
compress -cf ${filethird} > "${filethird}.Z"
Log "#################end unload bmpwithcbp.."

Log "#################start unload cbp_inter_node.."
filefouth=${tempdir}/${processdate}_cbp_inter_node.list
if [ "-$DBSERVERTYPE" = "-INFORMIX" ]; then
    echo "unload to ${filefouth} SELECT CBP_INTER_NODE,CBP_HOME_NODE,SUBNODETYPE,PRIMARYFLOATIP,SECONDARYFLOATIP,ROUTE_INTER_NODE FROM RPT_CBP_INTER_NODE" | dbaccess $SMPDBNAME
elif [ "-$DBSERVERTYPE" = "-ORACLE" ]; then
    echo "set   heading   off
set linesize 260
set   pagesize   0
set   feedback   off
set   echo   off
set   VERIFY   off
set   trimout   on
set   trimspool   on
set   termout   off
spool   ${filefouth}
select CBP_INTER_NODE||'|'||CBP_HOME_NODE||'|'||SUBNODETYPE||'|'||PRIMARYFLOATIP||'|'||SECONDARYFLOATIP||'|'||ROUTE_INTER_NODE||'|' FROM RPT_CBP_INTER_NODE;
spool   off " > cbp_inter_node.sql
echo "@cbp_inter_node.sql" | sqlplus $SMPDBNAME/$SMP_ORA_DB_PASS@$ORACLE_INSTANCE >/dev/null 2>&1
fi
compress -cf ${filefouth} > "${filefouth}.Z"
Log "#################end unload cbp_inter_node.."

Log "#################start unload cbp_node.."
filefive=${tempdir}/${processdate}_cbp_node.list
if [ "-$DBSERVERTYPE" = "-INFORMIX" ]; then
    echo "unload to ${filefive} SELECT CBP_NODE,NODETYPE,PRIMARYFLOATIP,SECONDARYFLOATIP,DIAMETER_HOST,DIAMETER_REALM,VERSION,NAME_LANG1 FROM RPT_CBP_NODE" | dbaccess $SMPDBNAME
elif [ "-$DBSERVERTYPE" = "-ORACLE" ]; then
    echo "set   heading   off
set linesize 260
set   pagesize   0
set   feedback   off
set   echo   off
set   VERIFY   off
set   trimout   on
set   trimspool   on
set   termout   off
spool   ${filefive}
select CBP_NODE||'|'||NODETYPE||'|'||PRIMARYFLOATIP||'|'||SECONDARYFLOATIP||'|'||DIAMETER_HOST||'|'||DIAMETER_REALM||'|'||VERSION||'|'||NAME_LANG1||'|' FROM RPT_CBP_Node;
spool   off " > cbp_node.sql
echo "@cbp_node.sql" | sqlplus $SMPDBNAME/$SMP_ORA_DB_PASS@$ORACLE_INSTANCE >/dev/null 2>&1
fi
compress -cf ${filefive} > "${filefive}.Z"
Log "#################end unload cbp_node.."