#脚本名称：delete.sh
#作者：乡村野中医
#创作时间2024-10-21
# crontab(root) : 0 1 * * * su - gbasedbt -c "/home/gbase/delete.sh" >/home/gbase/delete.log 2>&1
# crontab(gbasedbt): 0 1 * * * . /home/gbase/.bash_profile && /home/gbase/delete.sh >/home/gbase/delete.log 2>&1
#录入数据保存天数
keep_day=180
#录入数据库名：表名：列名。列名必须是非空的时间类型列，根据时间类型的列删除
cat > list_table <<!
testdb:td_dat_abnormal:receive_datetime
testdb:sj_register_list_agm:receive_time
!
while read line
do
dataname=`echo $line|awk -F':' '{print $1}'`
tabname=`echo $line|awk -F':' '{print $2}'`
colname=`echo $line|awk -F':' '{print $3}'`
while true
do
data_row=`echo "select min(${colname}) from ${tabname} where ${colname} < current - ${keep_day};"|dbaccess ${dataname} -`
data_row=`echo ${data_row}|awk '{print $2}'`
if [ 'x' = 'x'${data_row} ]
then
dbaccess -e ${dataname} <<!
update statistics high for table ${tabname}(${colname});
!
break
else
dbaccess -e ${dataname} <<!
delete from ${tabname} where date(${colname})=(select date(min(${colname})) from ${tabname} where ${colname} < current - ${keep_day});
!
echo "${dataname}:${tabname} delete task is Complete,${data_row} date data is deleted "
fi
done
done < list_table
rm -f list_table
