任务管理:
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;
buickbgd2015-03-21已下载，资源不错，感谢上传。
