下面的shell脚本用于操作db2数据库,具体的业务逻辑可以忽略,关键是掌握在shell脚本中如何去操作db2数据库以及在shell脚本中读取文件的方法。在shell脚本中经常需要使用到读取配置文件的功能,使用类似cat $APPHOME/etc/${conffile} |grep -v "^#"| while read table_list table_head的方式来读取文件。
#!/bin/bash
# 一个账户对应一个币种的更新,使用方式:
#sh addCurrency.sh [配置文件名]
#说明:配置文件格式参照addCurrency.conf,[]代表参数可选,不传的话默认使用addCurrency.conf,放在$APPHOME/etc/目录下
. /etc/profile
. ~/.bashrc
conffile="addCurrency.conf"
if [ $# -eq 1 ];then
conffile=$1
fi
logfile=$APPHOME/log/addCurrency.log
if [ ! -e $logfile ]; then
cat /dev/null > $logfile
fi
date | tee -a $logfile
cat $APPHOME/etc/${conffile} |grep -v "^#"| while read table_list table_head
do
db2 +o connect to $DBNAME user $DBUSER using $DBPWD;
#满足if的去掉and a.trandate=b.submitdate
if [ "${table_list}" == "HIS_OGN_ALLREADYEXCHLIST" -o "${table_list}" == "CRT_ALLREADYEXCHLIST" \
-o "${table_list}" == "HIS_OGN_STOCKCASHTRANLIST" -o "${table_list}" == "CRT_STOCKCASHTRANLIST" ];then
echo "由各项准备或库存现金分支处理:ftzetl.${table_list}" |tee -a $logfile
db2 +w \
" update ftzetl.${table_list} a set a.currency=( " \
" select distinct b.currency from ftzetl.${table_head} b where " \
" a.accountno=b.accountno and a.bankcode=b.bankcode and exists ( " \
" select h.bankcode,h.accountno from ftzetl.${table_head} h where " \
" b.accountno =h.accountno and b.bankcode =h.bankcode group by h.bankcode,h.accountno " \
" having count(distinct h.currency)=1 " \
" ) " \
" ) where exists ( " \
" select h.bankcode,h.accountno from ftzetl.${table_head} h where " \
" a.accountno =h.accountno and a.bankcode =h.bankcode group by h.bankcode,h.accountno " \
" having count(distinct h.currency)=1 " \
" ) " | tee -a $logfile
db2 +o commit
echo "由各项准备或库存现金分支处理:ftzrpt.${table_list}" |tee -a $logfile
db2 +w \
" update ftzrpt.${table_list} a set a.currency=( " \
" select distinct b.currency from ftzrpt.${table_head} b where " \
" a.accountno=b.accountno and a.bankcode=b.bankcode and exists ( " \
" select h.bankcode,h.accountno from ftzrpt.${table_head} h where " \
" b.accountno =h.accountno and b.bankcode =h.bankcode group by h.bankcode,h.accountno " \
" having count(distinct h.currency)=1 " \
" ) " \
" ) where exists ( " \
" select h.bankcode,h.accountno from ftzrpt.${table_head} h where " \
" a.accountno =h.accountno and a.bankcode =h.bankcode group by h.bankcode,h.accountno " \
" having count(distinct h.currency)=1 " \
" ) " | tee -a $logfile
db2 +o commit
else
echo "由普通分支处理:ftzetl.${table_list}" |tee -a $logfile
db2 +w \
" update ftzetl.${table_list} a set a.currency=( " \
" select distinct b.currency from ftzetl.${table_head} b where " \
" a.accountno=b.accountno and a.bankcode=b.bankcode and a.trandate=b.submitdate and exists ( " \
" select h.bankcode,h.accountno from ftzetl.${table_head} h where " \
" b.accountno =h.accountno and b.bankcode =h.bankcode group by h.bankcode,h.accountno " \
" having count(distinct h.currency)=1 " \
" ) " \
" ) where exists ( " \
" select h.bankcode,h.accountno from ftzetl.${table_head} h where " \
" a.accountno =h.accountno and a.bankcode =h.bankcode group by h.bankcode,h.accountno " \
" having count(distinct h.currency)=1 " \
" ) " | tee -a $logfile
db2 +o commit
echo "由普通分支处理:ftzrpt.${table_list}" |tee -a $logfile
db2 +w \
" update ftzrpt.${table_list} a set a.currency=( " \
" select distinct b.currency from ftzrpt.${table_head} b where " \
" a.accountno=b.accountno and a.bankcode=b.bankcode and a.trandate=b.submitdate and exists ( " \
" select h.bankcode,h.accountno from ftzrpt.${table_head} h where " \
" b.accountno =h.accountno and b.bankcode =h.bankcode group by h.bankcode,h.accountno " \
" having count(distinct h.currency)=1 " \
" ) " \
" ) where exists ( " \
" select h.bankcode,h.accountno from ftzrpt.${table_head} h where " \
" a.accountno =h.accountno and a.bankcode =h.bankcode group by h.bankcode,h.accountno " \
" having count(distinct h.currency)=1 " \
" ) " | tee -a $logfile
db2 +o commit
fi
db2 +o connect reset
done
echo "更新完毕!"