#! /bin/bash
# Description: sh db2
# chkconfig: 35 55 25
# Copyright (c) 2010-08 Gerry
# db2.sh
#===============================
# user conifg here
# param is table name ! you must put upon string here
table_name="TEST_PARTITION"
backup_date="6"
#===============================
backup_tablename="backup_$table_name`date +%Y%m%d%H%M%S`"
sql_3="DROP TABLE $backup_tablename"
schar="t.*"
sql_export="select "${schar}" from $backup_tablename as t"
sql_partitionno="SELECT datapartitionname FROM SYSIBM.SYSDATAPARTITIONS where tabschema='XXXXDB' and tabname='$table_name'
and lowvalue=(select ''''|| to_char(year(current date - $backup_date months)||'-'||month(current date - $backup_date months)||'-01','yyyy-MM-dd')||'''' from sysibm.sysdummy1)"
#echo $sql_partitionno
#select parno
db2 connect to xxxxdb user username using password
sdata0=`db2 -x "$sql_partitionno"`
db2 connect reset
#echo "=======$sdata0========="
#step 0
if [ "${sdata0:0:1}" != "P" ] ; then
echo "not data exit step 0..."
exit 1
fi
#getdate6this
sql_pathdate6="select year(current date - $backup_date months)||'-'||month(current date - $backup_date months)||'-01' from sysibm.sysdummy1"
db2 connect to xxxxdb user username using password
sdata0_1=`db2 -x "$sql_pathdate6"`
db2 connect reset
filepath="/db2users/backup/"$table_name"-"${sdata0_1:0:9}""
#echo "==================== $filepath ============= $sql_pathdate6 ========="
sdata1="0"
sdata2="0"
sdata3="0"
sdata4="0"
sql_2="ALTER TABLE $table_name DETACH PARTITION $sdata0 into $backup_tablename"
# delete the part from db2 table
db2 connect to xxxxdb user username using password
sdata1=`db2 -x "$sql_2"`
db2 connect reset
#echo "==========$sql_2=============="
#step 1
if [ "${sdata1:0:8}" = "DB20000I" ] ; then
echo "step1 sucessful"
db2 connect to xxxxdb user username using password
sdata2=`db2 -x export to $filepath of del $sql_export`
db2 connect reset
else
echo "step 1 quit..."
exit 1
fi
#echo "==============$sdata2================="
#step 2
if [ "${sdata2:0:8}" = "SQL3104N" ] ; then
echo "step2 successful"
db2 connect to xxxxdb user username using password
sdata3=`db2 -x "$sql_3"`
db2 connect reset
else
echo "step 2 quit"
exit 1
fi
#step 3
if [ "${sdata3:0:8}" = "DB20000I" ] ; then
echo "step3 successful"
else
echo "step 3 quit"
exit 1
fi