#!/bin/sh . $(dirname $0)/public_s.sh cd; #获取log文件名,必须调用getlogname函数; getlogname $0; log="$pub_result"; touch $log; #自动扩展百分比,可为小数 limitpert=95; #阀值百分比 autopert=10; #自动增加原容量的百分比 [ "$debug" = "Y" ] && log=/dev/null; echo "">>"$log"; echo "$dt">>"$log"; #获取表空间占用大于指定百分比的表空间信息 sql=" SELECT a.TBSPACE,a.OWNER,a.OWNERTYPE,a.DATATYPE,a1.TBSP_TYPE ,(b.TBSP_USED_PAGES/(b.TBSP_USED_PAGES+b.TBSP_FREE_PAGES+b.TBSP_PENDING_FREE_PAGES+0.000000001)*100.0000) USEDBL ,a.CREATE_TIME,a.TBSPACEID,a.TBSPACETYPE ,round((b.TBSP_TOTAL_PAGES+b.TBSP_PENDING_FREE_PAGES)*c.PAGESIZE*1.00/1024/1024,2) TBSP_TOTAL_PAGES_M ,round((b.TBSP_USABLE_PAGES+b.TBSP_PENDING_FREE_PAGES)*c.PAGESIZE*1.00/1024/1024,2) TBSP_USABLE_PAGES_M ,round(b.TBSP_USED_PAGES*c.PAGESIZE*1.00/1024/1024,2) TBSP_USED_PAGES_M ,round((b.TBSP_FREE_PAGES+b.TBSP_PENDING_FREE_PAGES)*c.PAGESIZE*1.00/1024/1024,2) TBSP_FREE_PAGES_M ,round(b.TBSP_PENDING_FREE_PAGES*c.PAGESIZE*1.00/1024/1024,2) TBSP_PENDING_FREE_PAGES_M ,b.TBSP_PAGE_TOP ,b.TBSP_NUM_RANGES,b.TBSP_NUM_CONTAINERS ,b.TBSP_INITIAL_SIZE TBSP_INITIAL_SIZE ,b.TBSP_CURRENT_SIZE*1.00/1024/1024 TBSP_CURRENT_SIZE_M ,b.TBSP_TOTAL_PAGES FROM SYSCAT.TABLESPACES a LEFT JOIN SYSIBMADM.SNAPTBSP A1 ON a.TBSPACEID=a1.TBSP_ID JOIN SYSIBMADM.SNAPTBSP_PART b ON a.TBSPACEID=b.TBSP_ID JOIN SYSCAT.BUFFERPOOLS c ON a.BUFFERPOOLID=c.BUFFERPOOLID WHERE 1=1 AND a.DATATYPE NOT IN (‘T‘,‘A‘) AND a.OWNERTYPE NOT IN (‘S‘) AND a.TBSPACETYPE NOT IN (‘S‘) AND (b.TBSP_USED_PAGES/(b.TBSP_USED_PAGES+b.TBSP_FREE_PAGES+b.TBSP_PENDING_FREE_PAGES+0.000000001)*100.0000)>1.00*$limitpert ORDER BY USEDBL DESC fetch first 10 rows only WITH UR "; echo "$sql" >>$log; db2cmd "$sql"; echo sqlcode is [$pub_sqlcode] if [ "$pub_sqlcode" != "0" ] then echo "$pub_result" | tee -a "$log"; exit; fi; echo [$pub_result]... splt=$pub_splt; echo "$pub_result"|while read line do #echo "lineis $line"; tbspace=$(echo "$line" | cut -d "$splt" -f 1 ); owner=$(echo "$line" | cut -d "$splt" -f 2 ); ownertype=$(echo "$line" | cut -d "$splt" -f 3 ); datatype=$(echo "$line" | cut -d "$splt" -f 4 ); tbsp_type=$(echo "$line" | cut -d "$splt" -f 5 ); usedbl=$(echo "$line" | cut -d "$splt" -f 6 ); create_time=$(echo "$line" | cut -d "$splt" -f 7 ); tbspaceid=$(echo "$line" | cut -d "$splt" -f 8 ); tbspacetype=$(echo "$line" | cut -d "$splt" -f 9 ); tbsp_total_pages_m=$(echo "$line" | cut -d "$splt" -f 10); tbsp_usable_pages_m=$(echo "$line" | cut -d "$splt" -f 11); tbsp_used_pages_m=$(echo "$line" | cut -d "$splt" -f 12); tbsp_free_pages_m=$(echo "$line" | cut -d "$splt" -f 13); tbsp_pending_free_pages_m=$(echo "$line" | cut -d "$splt" -f 14); tbsp_page_top=$(echo "$line" | cut -d "$splt" -f 15); tbsp_num_ranges=$(echo "$line" | cut -d "$splt" -f 16); tbsp_num_containers=$(echo "$line" | cut -d "$splt" -f 17); tbsp_initial_size=$(echo "$line" | cut -d "$splt" -f 18); tbsp_current_size_m=$(echo "$line" | cut -d "$splt" -f 19); tbsp_total_pages=$(echo "$line" | cut -d "$splt" -f 20); if [ "$tbspace" = "" ] then echo "无需扩展表空间!退出" | tee -a $log; exit; fi; date >>$log; echo "tablespace limit percent is [${limitpert}%] and autoextet percent is [${autopert}%]" | tee -a $log; echo "tbspace is [$tbspace]" | tee -a $log; echo "owner is [$owner]" | tee -a $log; echo "ownertype is [$ownertype]" | tee -a $log; echo "datatype is [$datatype ]" | tee -a $log; echo "tbsp_type is [$tbsp_type]" | tee -a $log; echo "usedbl is [$usedbl ]" | tee -a $log; echo "create_time is [$create_time]" | tee -a $log; echo "tbspaceid is [$tbspaceid]" | tee -a $log; echo "tbspacetype is [$tbspacetype]" | tee -a $log; echo "tbsp_total_pages_m is [$tbsp_total_pages_m ]" | tee -a $log; echo "tbsp_usable_pages_m is [$tbsp_usable_pages_m]" | tee -a $log; echo "tbsp_used_pages_m is [$tbsp_used_pages_m]" | tee -a $log; echo "tbsp_free_pages_m is [$tbsp_free_pages_m]" | tee -a $log; echo "tbsp_pending_free_pages_m is [$tbsp_pending_free_pages_m]" | tee -a $log; echo "tbsp_page_top is [$tbsp_page_top]" | tee -a $log; echo "tbsp_num_ranges is [$tbsp_num_ranges]" | tee -a $log; echo "tbsp_num_containers is [$tbsp_num_containers]" | tee -a $log; echo "tbsp_initial_size is [$tbsp_initial_size]" | tee -a $log; echo "tbsp_current_size_m is [$tbsp_current_size_m]" | tee -a $log; echo "tbsp_total_pages is [$tbsp_total_pages ]" | tee -a $log; #计算每个容器按总容量的百分比平均扩展值,并生成sql扩展语句 sql2=" SELECT ‘ALTER TABLESPACE ‘||a.TBSP_NAME||‘ EXTEND (File ‘‘‘ ||a.CONTAINER_NAME||‘‘‘ ‘ ||varchar(ceiling($tbsp_total_pages_m*$autopert*1.00/100000/$tbsp_num_containers))||‘G) ON DBPARTITIONNUM (0)‘ sql /* ,a.TBSP_NAME,a.TBSP_ID,a.CONTAINER_NAME,a.CONTAINER_ID,a.CONTAINER_TYPE ,a.TOTAL_PAGES*b.PAGESIZE*1.00/1024/1024 TOTAL_PAGES_SIZE_M ,a.USABLE_PAGES*b.PAGESIZE*1.00/1024/1024 USABLE_PAGES_SIZE_M ,b.BUFFERPOOLID */ FROM SYSIBMADM.CONTAINER_UTILIZATION a LEFT JOIN SYSCAT.TABLESPACES b ON a.TBSP_ID=b.TBSPACEID WHERE a.TBSP_NAME IN (‘$tbspace‘)"; #echo "$sql2" | tee -a $log; #执行 db2cmd "$sql2"; if [ "$sqlcode" != "0" ] then echo "$pub_result,[$dt]退出!" | tee -a $log; exit; fi; echo "$pub_result"|while read sline do echo "[运行SQL]:$sline"; db2cmd "$sline"; echo ; echo "$pub_result,[$dt]!" | tee -a $log; done; done;