没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
任务管理:
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
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;
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;
剩余8页未读,继续阅读
资源评论
- buickbgd2015-03-21已下载,资源不错,感谢上传。
zhangmingwei1221
- 粉丝: 3
- 资源: 36
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功