linux使用shell脚本处理oracle、db2数据库

处理oracle数据库

手动输入时间（yyyymmdd），判断是否存在文件名为yyyymmdd，若存在则先备份再删除echo "请输入时间(yyyymmdd)"，连接oracle查询数据并删除

read date
cd /home/ap/ssp/filedir/infiledir/makecard/jinbangda/
if [ ! -d "$date" ]
then
        echo "file $date not exist"
		exit 0
else
    echo "jinbangda file $date is exist,gameover"
	cp -rf $date $date.bak
	echo "jinbangda file $date is copy,filename is $date.bak ok"
	rm -rf $date
	echo "jinbangda delete file $date ok"
fi
#sq是存放在当前用户下的连接数据库脚本
sq <<EOF
set linesize 1000;
select * from batstat where command='dealmakecard' and inputstr like '$date%';
delete file_mon where filedate='$date';
EOF

　

处理db2数据库

获取当前系统时间的前一百天日期，然后连接数据进行数据迁移，及数据删除

echo "start $(date +%Y-%m-%d-%X)"
time=$(date -d '100 days' +%Y-%m-%d)
echo "start $time"
su - db2inst1 <<EOF
time db2 connect to cmbcepay user epay using epay
echo "连接数据成功"
echo "开始导出日期为$time的bt_clear_trans_bak数据文件为：bt_clear_trans$time.del"
time db2 "select count(1) from bt_clear_trans_bak where trans_cleardate=date('$time')"
echo "执行语句：'time db2 export to bt_clear_trans$time.del of del select * from bt_clear_trans_bak where trans_date=date('$time')"
time db2 "export to bt_clear_trans$time.del of del select * from bt_clear_trans_bak where trans_cleardate=date('$time')"
echo " $time的bt_clear_trans数据导出成功"
echo "开始将数据bt_clear_trans$time.del导入到bt_clear_trans"
time db2 "select count(1) from bt_clear_trans_2015 where trans_cleardate=date('$time')"
echo "执行语句：time db2 import from bt_clear_trans$time.del of del commitcount 10000 insert into bt_clear_trans_2015"
time db2 "import from bt_clear_trans$time.del of del commitcount 10000 insert into bt_clear_trans_2015"
echo "导入$time 的bt_clear_trans成功"
echo "开始删除表数据"
time db2 "select count(1) from bt_clear_trans_bak where trans_cleardate=date('$time')"
echo "执行语句：'time db2 delete from bt_clear_trans' "
time db2 "delete from bt_clear_trans_bak where trans_cleardate=date('$time')"
time db2 "select count(1) from bt_clear_trans_bak where trans_cleardate=date('$time')"
echo "删除成功"
echo "game over"
EOF

以上脚本可以简单修改添加到crontab中进行定时运行