linux脚本执行db2语句,LINUX下SHELL调用DB2公共函数之public_s.sh

1 #!/bin/sh

2

3 # 张明伟4 # 20140505

5 # SHELL公共函数头6 # 对SHELL公共部分进行处理7 #8

9 . $HOME/.bash_profile10 export LANG=zh_CN11 export LC_ALL=zh_CN12

13 pub_nul=/dev/null; #空设备14 pub_splt=" "; #分割符15 #获取系统数据日期16 pub_td=$(date +%Y%m%d) #today17 pub_tm=$(date +%H%M%S) #time

18 pub_dt="$pub_td $pub_tm"; #日期时间19 pub_monstr=${pub_dt:0:6}01#月开始20

21 #kpi中使用的日期:上日中CMN_SYSDATE22 pub_dt=$(date +"%Y%m%d" -d "-2 days")23 pub_ldt=$(date +"%Y%m%d" -d "-3 days")24 pub_ndt=$(date +"%Y%m%d" -d "-1 days")25 #kpi中使用的日期26

27 #当天数据日期28 pub_dtd=$pub_ndt;29

30 #上年年末31 pub_lastyearend=$(date +"%Y%m%d" -d "${pub_dt:0:4}0101 -1 days");32

33 #下月34 pub_nxtmss=$(date +"%Y%m%d" -d "$pub_monstr 1 months")35

36 #月末37 pub_mοnend=$(date +"%Y%m%d" -d "${pub_nxtmss:0:6}01 -1 days")38 #上月月末39 pub_lastmend=$(date +"%Y%m%d" -d "${pub_ndt:0:6}01 -1 days")40 #全局结果表,共享变量41 pub_result="";42 #logs目录43 pub_logdir="$HOME/zmw_data/logs";44 pub_logf="";45 pub_debug="N"; #这个是不写日志,有些标示,日志文件为/dev/null,只打印屏幕;46

47 #增量,一般用于日期48 [ "$pub_incr" = "" ] && pub_incr=1;49

50 #日期类型,一般是月51 pub_datatype="M";52 pub_datatype=$(echo $pub_datatype | tr ‘a-z‘ ‘A-Z‘);53

54 #获取进程号55 getpid()56 {57 pub_pname="张明伟伟进程名,例如:mainETL.xml";58 [ "$1" != "" ] && pub_pname="$1";59 pub_result="";60 pub_result=$( ps -ef | grep "$pub_pname" | grep -v "grep" | tr -s " " ":" | cut -f 2 -d : );61 }62

63

64 #传入参数上一天65 lastday()66 {67 pub_result="";68 pub_ld=-1;69 pub_thisday=$pub_td;70 [ "$1" != "" ] && pub_lthisday=$1;71 [ "$2" != "" ] && pub_ld=$2;72 #date +"%Y%m%d" -d "$pub_lthisday $pub_ld days"

73 pub_result=$(date +"%Y%m%d" -d "$pub_lthisday $((-1*pub_ld)) days")74 #echo "[$pub_result]"

75 }76

77 #传入参数后一天78 nextday()79 {80 pub_result="";81 pub_d=1;82 pub_thisday=$pub_td;83 [ "$1" != "" ] && pub_nthisday=$1;84 [ "$2" != "" ] && pub_d=$2;85 lastday $pub_nthisday -$pub_d;86 }87

88 #上一年年末89 getlastyearend()90 {91 pub_v_datadate="$1"

92 if [ "$1" = ""]93 then

94 pub_result=$pub_lastyearend;95 else

96 pub_result=$(date +"%Y%m%d" -d "${pub_v_datadate:0:4}0101 -1 days")97 fi;98

99 }100

101 #两日期相减102 fc_datediff()103 {104 [ "$pub_xstrdt" = "" ] && pub_xstrdt=$pub_dtd ;105 [ "$pub_xenddt" = "" ] && pub_xenddt=$pub_dtd;106 pub_xstrdt=$1;107 pub_xenddt=$2;108 v_starts=$(date -d "$pub_xstrdt" +"%s");109 v_ends=$(date -d "$pub_xenddt" +"%s");110 v_sub=$((v_ends-v_starts));111

112 pub_result=$((v_sub/3600/24));113 }114

115 #传入参数间的月末或每天,取决于datatype116 fc_getdayebtw()117 {118 pub_strdt=$1;119 pub_enddt=$2;120 pub_datatype=$(echo $pub_datatype | tr ‘a-z‘ ‘A-Z‘);121 if [ "$pub_datatype" = "D" -o "$pub_datatype" = "M"]122 then

123 if [ "$pub_strdt" = "" -o "$pub_enddt" = ""]124 then

125 echo "请输入开始时间和结束时间参数!"

126 exit;127 fi;128

129 pub_ds_1=$pub_strdt130 pub_de_1=$pub_enddt131 #echopub_ds_1 is [$pub_ds_1] and pub_de_1 is [$pub_de_1];132

133 #pub_result="$pub_ds_1";134 pub_result="";135 while [ $pub_ds_1 -le $pub_de_1 ]136 do

137 if [ "$pub_datatype" = "M"]138 then

139 pub_ds_1=${pub_ds_1:0:6}01

140 #echods_1 is $ds_1141 pub_ds_1=$(date -d "$pub_ds_1 + $pub_incr months" +"%Y%m01")142 pub_ds_1=$(date -d "$pub_ds_1 - $pub_incr days" +"%Y%m%d")143 #echo "datatype is $datatype and ds_1 is $ds_1"

144 fi;145

146 [ $pub_ds_1 -le $pub_de_1 ] && pub_result="$pub_result $pub_ds_1"

147 pub_ds_1=$(date -d "$pub_ds_1 +$pub_incr day" +"%Y%m%d");148 done;149 fi;150 }151

152 #删除空格153 trim()154 {155 pub_str="";156 [ "$1" != "" ] && pub_str="$1";157 pub_str=$( echo "$1" | tr -d " ");158 pub_result="$pub_str";159 }160

161 #替换162 replace()163 {164 pub_str="";165 pub_s="";166 pub_d="";167 [ "$1" != "" ] && pub_str="$1";168 [ "$2" != "" ] && pub_s="$2";169 [ "$3" != "" ] && pub_d="$3";170 #echo[$pub_str..$pub_s...$pub_d]171 pub_result=${pub_str//"$pub_s"/"$pub_d"}

172 }173

174 #指定月上月末175 getmonend()176 {177 pub_g_dt=$1

178 pub_i=$2;179 [ "$2" = "" ] && i=1;180 pub_g_dt=$(date +"%Y%m%d" -d "${pub_g_dt:0:6}01 $i months ago")181 pub_result=$(date +"%Y%m%d" -d "${pub_g_dt:0:6}01 1 days ago");182 }183

184 #echologdir is $logdir;185 getlogname()186 {187 pub_appnm="$1";188

189 [ "$pub_appnm" != "" ] && pub_appnm=$(basename "$pub_appnm");190 pub_appnm=${pub_appnm%.*};191 pub_result="$pub_logdir/$pub_appnm.log";192

193 [ "$pub_debug" = "Y" ] && pub_result="$pub_nul";194 }195

196 pub_sqlstate="SQLSTATE";197 #db2运行,需返回数据198 db2cmd()199 {200 #执行函数时建议加上此句;201 pub_old_debug=$pub_debug;202

203 pub_c_sql="$1";204 pub_result="";205 pub_sqlcode=0;206 [ "$pub_c_sql" = "" ] && pub_result="SQL语句为空" &&return;207 [ "$2" != "" ] && pub_debug="$2";208 #为了和前面的程序兼容,增加传入pub_debug和设置pub_debug参数都起作用!

209 [ "$pub_old_debug" = "" ] && pub_debug=$pub_old_debug;210 [ "$pub_debug" = "" ] && pub_debug="N";211

212 if [ "$pub_debug" != "Y"]213 then

214 db2 connect to zxjxdbs>$pub_nul;215

216 pub_result=$(db2 +w -x "$pub_c_sql");217

218 pub_result=$(echo "$pub_result" | tr -s " " "$pub_splt");219

220 pub_result=${pub_result# *};221

222 pub_result=${pub_result%*};223 [ "$pub_result" = "-" ] && pub_result="";224 pub_hd=$(echo "$pub_result" | head -n 1);225

226 pub_sqlinfo=${pub_hd%%"${pub_sqlstate}"*};227 if [ "$pub_sqlinfo" != "" -a "$pub_sqlinfo" != "$pub_hd"]228 then

229 pub_sqlcode=$(echo "$pub_hd" | cut -d " " -f 1);230

231 #若有错误,则把错误屏蔽232 #pub_cnt=$(echo $pub_result | wc -l )233

234 #pub_result=$(tail -$((pub_cnt-1)) $pub_result)235 fi;236

237 #echopub_result[$pub_result]238 db2 connect reset>$pub_nul;239 db2 terminate>$pub_nul;240 fi;241 }