db2 修改表空间自增长_使用SHELL对DB2数据库表空间进行自动扩容

#!/bin/sh. $(dirname $0)/public_s.shcd;

#获取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/1024TBSP_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

WHERE1=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 first10rows only

WITH UR";

echo "$sql" >>$log;

db2cmd"$sql";echosqlcode 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"|whileread linedo#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"|whileread slinedo

echo "[运行SQL]:$sline";

db2cmd"$sline";echo;echo "$pub_result,[$dt]!" | tee -a $log;done;done;