用shell处理db2数据库的程序

1、用shell处理db2数据库的程序用shell处理db2数据库的程序，用shell抽取db2的数据，并进行处理。 #sql文定义sql=select aaa, bbb, ccc from mytbl1#执行sqlsdata=db2 $sql#返回值判断if $? -ne 0 then#显示db2返回的错误信息echo $sdataexit 1fi#对取得的数据进行处理。echo $sdata | sed -e 4,/$/!d;/$/d |while read aaa bbb cccdo echo aaa is $aaa, bbb is $bbb, ccc is $cccdone#取得数据件数e

2、cho $sdata | sed -n -e /$/1,3d;n;s/0-9*(0-9*)0-9*/1/;p; | read cntecho the count of selected data is $cnt.exit 0更新db2的数据，并取得更新结果 sql=update mytbl1 set aaa=2005,bbb=05,ccc=12#执行sqlsdata=db2 -a $sql#取得sqlcodeecho $sdata | sed -n -e s/.*sqlcode: (-,0-90-9*).*/1/p | read sqlcodeecho sqlcode is $sqlcode.

3、#取得sqlstateecho $sdata | sed -n -e s/.*sqlstate: (-,0-90-9*).*/1/p | read sqlstateecho sqlstate is $sqlstate.#取得更新件数(即sqlerrd的第三个值)echo $sdata | sed -n -e /sqlerrd/s/.*(3) (-,0-90-9*).*/1/p | read updcntecho updated datas count is $updcnt.#取得sqlerrd的第五个值echo $sdata | sed -n -e /sqlerrd/n;s/.*(5) (-,

4、0-90-9*).*/1/;p; | read sqlerrd5echo sqlerrd(5) is $sqlerrd5.?第一种：#sql文定义sql=select aaa, bbb, ccc from mytbl1#执行sqlsdata=db2 $sql#返回值判断if $? -ne 0 then #显示db2返回的错误信息 echo $sdata exit 1fi#对取得的数据进行处理。echo $sdata | sed -e 4,/$/!d;/$/d | while read aaa bbb ccc do echo aaa is $aaa, bbb is $bbb, ccc is $c

5、cc done#取得数据件数echo $sdata | sed -n -e /$/1,3d;n;s/0-9*(0-9*)0-9*/1/;p; | read cntecho the count of selected data is $cnt.exit 0第二种：db2 select #,aaa, bbb, ccc from mytbl1|grep #|awk print $2,$3,$4|while read aaa bbb cccdo echo aaa is $aaa, bbb is $bbb, ccc is $cccdone?db2经常使用的shell脚本（学习）！ 1.forces al

6、l applications connected to the database,#!/bin/ksh# usage : # forces all applications connected to the database,set -xdbname=$1if $dbname = thenecho usage : $0 exitfilistcmd=db2 list applications for database $dbnamewhile truedo$listcmdif $? -eq 2 thendb2 deactivate database $dbnameexitfi$listcmd |

7、 tail +5 | nawk print $3 | while read appliddoif $applid != thendb2 force application($applid)fidonesleep 5done2.list tablespaces useage#!/bin/ksh# # # usage : db2_tspace.ksh -d -t -h -u -p dbname=thres=90noheaders=0userid=password=syntax()echo echo db2_tspace.ksh -d -t -h -u -p echo echo -d databas

8、e name. mandatoryecho -t threshold percentage echo -h do not print headers and footers . optional. default - prints headers and footerecho -u userid used to connect. optional.echo -p password for the userid. mandatory if -u is specified.echo while getopts d:t:hu:p: inoptdocase $inopt ind) dbname=$op

9、targ ; t) thres=$optarg ;h) noheaders=1 ;u) userid=$optarg ;p) password=$optarg ;esacdoneif -z $dbname # return error if dbname is nullthen syntaxreturn 12exitfiif -z $userid & -z $password # choosing the connection statementthendb2 connect to $dbname /dev/null elsedb2 connect to $dbname user $useri

10、d using $password /dev/nullfi#db2 connect to $dbname /dev/null if $? -ne 0 thenecho error when connecting to database $dbnamereturn 12exitfidb2 list tablespaces /dev/null # check if list tablespaces command is successfulif $? -ne 0 thenecho error occured when listing tablespacesreturn 12exitfidb2 li

11、st tablespaces show detail | awk -v tpers=$thres -v dbn=$dbname -v nohead=$noheaders begin i=0 # reset counterstatewarning=0 # reset tablespace warning flagwarnts=0 # reset number of non-normal tablespaces# print headersif (nohead=0)print print database : dbn print print threshold % : tpers nprintf(

12、%3s %-20s %-4s %6s %12s %12s %7sn,id,tablespace,type,pgsize,pages alloc,pages used,%used)printf(%3s %-20s %-4s %6s %12s %12s %7sn,-,-,-,-,-,-,-)if ($1=tablespace & $2=id) tsidi = $nf # tablespace idif ($1=name & $2=) tsnamei = $nf # tablespace nameif ($1=type & $2=)if ($3=database) tstypei = dms # d

13、atabase typeelse tstypei=smsif ($1=state & $2=)if ($nf!=0x0000) # database statestatewarning=1warnts=warnts+1if ($1=total & $2=pages)tsalloc_pagesi = $nf # allocated pagesif ($1=used & $2=pages)tsused_pagesi = $nf # used pagesif ($1=page & $2=size)tspage_sizei = $nf # get pagesize and calculate valu

14、es# allocation - mbtsalloc_mbi = tsalloc_pagesi * (tspage_sizei/1024/1024)# usage - mbtsused_mbi = tsused_pagesi * (tspage_sizei/1024/1024)# free - mbtsfree_mbi = tsalloc_kbi - tsused_kbi# used percentagetsused_pci = tsused_mbi*100/tsalloc_mbi if (tsused_pci tpers & tstypei=dms ) tsthresi = # thresh

15、old excedeed - indicator else tsthresi = # used - mbtsused=tsused+tsused_mbi# allocated - mb tsalloc=tsalloc+tsalloc_mbii+ # increment counterend for (j=0;ji;j+)printf(%3d %-20s %4s %6d %12d %12d %7.2f %2sn,tsidj,tsnamej,tstypej,tspage_sizej,tsalloc_pagesj,tsused_pagesj,tsused_pcj,tsthresj)if (nohea

16、d=0)printprintprint total allocated (mb) : tsallocprint total used (mb) : tsusedprintprintf(percentage used : %5.2f %1sn ,(tsused/tsalloc)*100,%)printif (statewarning=1) print warning : warnts tablespace(s) not in normal stateprint ps: print 1. sms tablespace usage is always 100%. for this reason, t

17、he threshold percentage is not applied for sms.3.lists the tablespaces and their information#!/bin/ksh# usage : db2_tspace_cont.ksh -d -h -u -p dbname=noheaders=0userid=password=syntax()echo echo db2_tspace_cont.ksh -d -h -u -p echo echo -d database name. mandatoryecho -h do not print headers and fo

18、oters . optional. default - prints headers and footerecho -u userid used to connect. optional.echo -p password for the userid. mandatory if -u is specified.echo while getopts d:hu:p: inoptdocase $inopt ind) dbname=$optarg ;h) noheaders=1 ;u) userid=$optarg ;p) password=$optarg ;esacdoneif -z $dbname

19、 # return error if dbname is nullthen# echo enter a valid database name using the -d optionsyntaxreturn 12exitfiif -z $userid & -z $password # choosing the connection statementthendb2 connect to $dbname /dev/nullelsedb2 connect to $dbname user $userid using $password /dev/nullfiif $? -ne 0 thenecho

20、error when connecting to database $dbnamereturn 12exitfidb2 list tablespace containers for 0 /dev/null # check if list tablespace containers command is successfulif $? -ne 0 thenecho error occured when listing tablespace containersreturn 12exitfiif -z $userid & -z $password # choosing the connection

21、 statementthenlisttsp_cmd=db2_tspace.ksh -d $dbname -helselisttsp_cmd=db2_tspace.ksh -d $dbname -h -u $userid -p $passwordfiif $noheaders -eq 0 then echo 1 | awk -v dbn=$dbname print print printf(%10s %s,tablespace containers for database : dbn) ; print print printf(%4s %-20s %-6s %6s %8s %8s %sn,tsid,tablespace,tstype,contid,conttype,pagesalloc,container name) ;printf(%4s %-20s %-6s %6s %8s %8s %sn,-,-,-,-,-,-,-)fi$listtsp_cmd | awk print $