#!/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;