[原創]DB2 並行Export 指令碼 --分割槽資料庫

#!/bin/sh

USAGE ()
{
  echo "Usage: `basename $0` [-d database] {-t tablename} [-p path] [-s filetype] [-o output] [-l logfile]";
  echo "Db2export Parameter Infomation:";
  echo "  Parameter -t must be specified                             [必須指定表名]";
  echo "  Parameter -d: Database Name,Default Value:sdncbi           [資料庫名]";
  echo "  Parameter -p: OutPath Name,Default Value:Current Directory [輸出目錄]";
  echo "  Parameter -s: File Type,{IXF | DEL | WSF}                  [檔案型別]";
  echo "  Parameter -o: File Name,Default Value:Table Name           [輸出檔案]";
  echo "  Parameter -f: Logfile Name,Default Value: null             [日誌檔案]";
  echo ""
  exit 1;
}

if [ $# -lt 2 ]
  then
   USAGE
  fi

dbname=""
tablename=""
pathdir=""
filetype=""
outfile=""
logfile=""
tmpfile=$(basename $0).tmp

#***************************Judge Parameter Begin
  while getopts :d:t:p:s:o:l: OPTION ;
  do
   case "$OPTION" in
     d) dbname="$OPTARG" ;;
     t) tablename="$OPTARG" ;;
     p) pathdir="$OPTARG" ;;
     s) filetype="$OPTARG" ;;
     o) utfile="$OPTARG" ;;
     l) logifle="$OPTARG" ;;
     \?) #usage statement
       USAGE;
       ;;
   esac
  done
 
  if [ "$tablename" = "" ]
   then
     echo "Parameter -t must be specified "
     USAGE;
   else
   if [ `expr index $tablename .` -le 0  ]
     then
       echo "The Table Name is the same as tabschema.tabname."
       echo "Eg: comm.dim_latn "
       exit 1
   fi
  fi
 
  if [ "$dbname" = "" ]
   then
     dbname="sdncbi"
  fi
  if [ "$pathdir" = "" ]
   then
     pathdir=`pwd`
  fi
  if [ "$filetype" = "" ]
   then
     filetype="del"
  fi
  if [ "$outfile" = "" ]
   then
    utfile=$tablename
  fi
#***************************Judge Parameter End

date
TABSCHEMA=`echo $tablename|cut -d. -f1`
TABNAME=`echo $tablename|cut -d. -f2`

# get partitioning key
db2look -e -d sdncbi -z $TABSCHEMA -t $TABNAME > $tmpfile 2>&1
flag=`cat $tmpfile|grep "CREATE TABLE"|wc -l`
if [ "$flag" -lt 1 ]
 then
 echo "Tabname is not exists,Please Imput a Correct Tabname!!"
 exit
fi

key=`cat $tmpfile | grep "PARTITIONING KEY" | awk -F\" '{print $2}' | sed -e "s/\"//g" `
if [ ! -n "$key" ]; then
key=`cat $tmpfile | grep "HASH" | awk -F\" '{print $2}' | sed -e "s/\"//g" `
fi

#echo $key
#Export data to file
echo "Begin Export [${tablename}] to file" |tee -a $logfile

if [ -n "$key" ]; then
db2_all "\"||db2 connect to ${dbname};db2 'export to ${pathdir}/${outfile}.${filetype}.## of ${filetype} select * from
${tablename} where dbpartitionnum(${key}) = ## with ur';" >$tmpfile
cat $tmpfile|grep "Number of rows exported:" |tee -a $logfile
cat $tmpfile|grep "Number of rows exported:"|awk -F":" 'BEGIN{sum=0}{sum=sum+$3}END{printf("Total Export Rows:%d\n",sum)}' |tee -a $logfile

 for file in ${fullname}.del.*;
   do
    ldfile=`echo $file|cut -d. -f4`
    newfile=`echo $oldfile|awk '{printf("%03d\n",$1);}'`
    #echo $oldfile -- $newfile
   
    if [ "$oldfile" != "$newfile" ]
     then
       mv $file ${fullname}.del.$newfile
    fi
   done
  
else
 db2 connect to ${dbname};db2 "export to ${pathdir}/${outfile}.${filetype} of ${filetype} select * from ${tablename} with ur" >$tmpfile
 cat $tmpfile|grep "Number of rows exported:" |tee -a $logfile
fi

if [ `cat $tmpfile|grep "Number of rows exported:"|wc -l` -gt 0 ]
  then
    echo "Export  [${tablename}] Success!!!!" |tee -a $logfile
    exit 1
  else
    echo "!!!Export  [${tablename}] Failed!!!!" |tee -a $logfile
    exit -1
fi

date