使用SHELL对DB2数据库表空间进行自动扩容

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