db2执行shell脚本

最新推荐文章于 2021-07-29 23:13:01 发布

转载最新推荐文章于 2021-07-29 23:13:01 发布 · 794 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/atwanli/articles/4620362.html

文章标签：

#shell #数据库 #python

本文详细介绍了如何使用DB2数据库的SQL语句进行数据迁移和表清理操作。包括从旧表bt_clear_trans_bak导出指定日期的数据并导入到新表bt_clear_trans_2015，同时清理table表TBL_SWINDLE中带有特定标志的数据。

echo "start $(date +%Y-%m-%d-%X)"
time=$(date -d '100 days' +%Y-%m-%d)
time=2014-03-23
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

或者

echo "\n开始清理 table 表TBL_SWINDLE"
db2 connect to elloc user easylink using elinkgit
db2 +c "select count(*) from TBL_SWINDLE where flag=1";
db2 "export to TBL_SWINDLE.del of del select * from TBL_SWINDLE";
db2 +c "alter table TBL_SWINDLE activate not logged INITIALLY"; //关闭日志打印只在执行一次sql后失效
db2 +c "delete from TBL_SWINDLE where flag=1";
db2 -v commit;
echo "table 表清理完毕!\n"

转载于:https://www.cnblogs.com/atwanli/articles/4620362.html