DB2表空间优化脚本：清理非关键表空间

任务管理:
1.sh文件
#!/bin/sh
#表空间管理作业,请不要删除,现操作系统已经在正常调用
#调用时间,每天的17:00
#crontab -e 打开,可编辑
. /home/db2app/.profile
#脚本删除创建时间在22天前的表或总数大于400*0.5的表,保证数据量不能大于总空间的50%和创建日期不能大于22天的
db2 connect to esopdb;
res=tbopres.txt
sql="
with a as
(
        select
        rownumber() over(order by npages desc) as od,
        'DB2INFO.'||tabname tabname,
        decimal(sum(npages)*1.00*32768/(1024*1024*1024),4,2) GUSAGE,
        create_time ct
        from syscat.tables
        where tbspace='TBS_DW_USER' and type='T'
        and OWNER='DB2APP' and TABSCHEMA='DB2INFO'
        and
        (
          tabname not like 'DIM%' and tabname not like '%LOG%' and tabname not like '%ESOP%' and tabname not like '%PROC%'
          and tabname not like '%DM%' and tabname not like '%MM%'
        )
        group by tabname,npages,create_time
        order by GUSAGE desc
),
b as

(
        select a_s.od,a_s.tabname,a_s.gusage,
        case a_s.od
        when 1 then a_s.gusage
        else (select sum(a_d.gusage) from a as a_d where a_d.od<=a_s.od) end as tot,
        a_s.ct
        from a as a_s
        order by a_s.od
)
select cast('alter table '||b.tabname||' activate not logged initially with empty table ; drop table '
||b.tabname||replace(' ;--od:'||cast(b.od as char(3))||',gusage:'||cast(b.gusage as char(10))||',total:'
||cast(cast(b.tot as decimal(5,2)) as char(10))||',percent:'||cast(cast(b.tot/400 as decimal(5,2)) as char(10))
||',created before '||cast(days(current_date)-days(ct) as char(2))||' days '||'--','00.','0.') as varchar(300)) as sql
from b
where b.tot>=400*0.5 or days(current_date)-days(ct)>=22
order by b.od,days(current_date)-days(b.ct) desc
"
#执行不出现标题类的内容
db2 -x $sql>$res
#分行并读取每行
tr ";" "\n" <${res} | while read line
do
 echo ` date +"%Y%m%d %H:%M:%S" `>>${res}.log
 db2 -tvx $line | tee -i -a ${res%".txt"}.log
done

db2 connect reset;
db2 terminate;