LINUX下SHELL对DB2数据分区表进行批量DETACH

#!/bin/sh
#tbdetach.sh
#对特定的数据表进行分区分离及附加(DETACH/ATTACH) D/A,这里的附加表示数据处理,并不对分区进行并入
#除了月底数据外
#输入:1.操作类型 A/D X表示先D后A
#     2.表名
#     3.开始日期
#     4.结束日期
#张明伟 20141029
#command:./tbdetach.sh D "PLAT_ACCPCDEPSTC PLAT_CUPIDXSTC PLAT_ACCPTDEPSTC PLAT_PCUSIDNSTC" 20130401 20140401 
. $(dirname 0)/public_s.sh
#pub_debug="Y";
getlogname $0;
log=$pub_result;
tboptype=$1;      #操作类型
tabname=$2;       #操作表
strdate=$3;       #开始日期
enddate=$4;       #结束日期
lim=10;

#月底数据
pub_datatype="M";
fc_getdayebtw $strdate $enddate;
mdts="$pub_result";
#echo "dts is [$dts]";
#echo "mdts is [$mdts]";

#"PLAT_ACCPCDEPSTC PLAT_CUPIDXSTC PLAT_ACCPTDEPSTC PLAT_PCUSIDNSTC"

for tb in $tabname
do
    echo >>$log;
    date | tee -a $log;
    
    #判断日期间隔是否大于数据库可传入的最大值(FC_SPLITSTR最大传入8000)
    #若大于,则对数据进行分割,限制为100个传入值
    fc_datediff $strdate $enddate;
    datediff=$pub_result;
    
    #初使化变量sstrdate,senddate
    sstrdate=$strdate;
    senddate=$enddate;
    sdts="";
    #echo "datediff is [$datediff] and sstrdate is [$sstrdate] and senddate is [$senddate]"
    
    #20150110改变思路,由数据库出数据,再做处理
    #######大于$lim的处理,超出时间的分段处理
    ######i=1;
    ######[ $datediff -lt $lim ] && lim=0;
    ######while [ $datediff -gt $lim -a $sstrdate -lt $enddate ]
    ######do
        ######    #只删除非月底数据
        ######    pub_datatype="D"; #数据类型
        ######    incr=$lim;
        ######    
        ######    #如果最后几个日期,不足lim,用datediff来替代incr
        ######    #例最后还有3天,步长是10天,则末数为3天
        ######    [ $lim -eq 0 ] && incr=$datediff;
    ######  nextday $sstrdate $incr ;#$lim;
    ######  senddate=$pub_result;
        ######    fc_getdayebtw $sstrdate $senddate;
    ######  dts="$pub_result";
    ######  echo "[$dts]"
    ######  
      ######  #db2cmd " SELECT RE FROM TABLE(FC_SPLITSTR('$dts',' ')) ";
      ######  
      ######  ssql="SELECT RIGHT(TRIM(a.DATAPARTITIONNAME),8)
        ######                FROM SYSCAT.DATAPARTITIONS a
        ######                WHERE a.TABNAME='$tb'
        ######                 AND RIGHT(TRIM(a.DATAPARTITIONNAME),8) IN 
        ######                 (
        ######                   SELECT RE FROM TABLE(FC_SPLITSTR('$dts',' '))
        ######                 )
        ######                WITH UR";
      ######  #echo "$ssql";
      ######  
      ######  [ $senddate -gt $enddate ] && sstrdate=$enddate;
      ######  
      ######  [ $senddate -gt $enddate ] && senddate=$enddate;
      ######  
    ######  sstrdate=$senddate;
    ######  nextday $sstrdate;
    ######  sstrdate=$pub_result;
    ######  
    ######  fc_datediff $sstrdate $enddate;
    ######  datediff=$pub_result;
    ######  
    ######  #如果最后日期小于限定值,则把限制值降为0,以便处理最好的几个日期值
    ######  [ $datediff -lt $lim ] && lim=0;
    ######  #echo "datediff is [$datediff] and sstrdate is [$sstrdate] and senddate is [$enddate]"
    ######  echo "$ssql";
      ######  db2cmd "$ssql";
      ######  
      ######  echo [pub_sqlcode is $pub_sqlcode and pub_sqlinfo is $pub_sqlinfo];
      ######  
      ######  sdts="$sdts$pub_result";
    ######  
    ######  i=$((i+1));
    ######done;
    ######
    ######echo "$tb s patition is [$sdts]";
    

      ssql="SELECT RIGHT(TRIM(a.DATAPARTITIONNAME),8)
                        FROM SYSCAT.DATAPARTITIONS a
                        WHERE a.TABNAME='$tb'
                         AND a.DATAPARTITIONNAME BETWEEN 'P_'||$strdate AND 'P_'||$enddate
                        ORDER BY RIGHT(TRIM(a.DATAPARTITIONNAME),8)
                        WITH UR";
                        
        db2cmd "$ssql";
        sdts="$pub_result";
        
    for dt in $sdts
    do
        for fdt in $mdts
        do
          #echo "fdt is [$fdt] and mdts is [$mdts] and dt is [$dt]"
          [ "$fdt" = "$dt" ] && continue 2; #跳转到上一层数据 $dts
        done

        #检查分区是否存在
        sql_chk="select 1 from syscat.datapartitions where tabname=upper('$tb') and datapartitionname like '%'||varchar($dt)"; 
        #echo "[$sql_chk]";
        db2cmd "$sql_chk";
        tbptflg=$pub_result;
        echo "tbpt flag is [$tbptflg]"
        if [ "$tbptflg" = "" -o "$tbptflg" = "-" ]
        then
          echo "$tb s $dt datapartition is not exists!";
          dt=$(date -d "$dt 1 days " +"%Y%m%d" );
          continue;
        fi;

        echo "for dt is [$dt]"
        if [ "$tboptype" = "D" -o "$tboptype" = "X" ]
        then
          #分离分区到目标表
          sqld=" ALTER TABLE $tb DETACH PARTITION P_$dt INTO ${tb}_P_$dt"; 
        fi;
        
        if [ "$tboptype" = "A" -o "$tboptype" = "X" ]
        then
          #从各分区数据插入到目标表
          sqla=" INSERT INTO $tb SELECT * FROM ${tb}_P_$dt";
        fi;
        
        sqldd=" drop table ${tb}_P_$dt";
        for sql in "$sqld" "$sqla" "$sqldd"
        do
            echo "$sql" | tee -a $log;
            db2cmd "$sql" ;
            echo "$pub_result" | tee -a $log;
        done;
    done;
done;