DB2表空间管理及查看脚本(SHELL)等

任务管理:
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;
2.crontab -e后填写如下:
#对表空间进行管理
12,15 * * * * /home/db2app/tbop.sh>/dev/null
--更新版本
#125ESOP表空间管理SHELL
#版本1.0
#作者:张明伟
#!/bin/sh
#表空间管理作业,请不要删除,现操作系统已经在正常调用
#调用时间,每天的17:00
#crontab -e 打开,可编辑
. /home/db2app/.profile
#0.0脚本删除创建时间在31天前的表或总数大于400*0.5的表,保证数据量不能大于总空间的50%和创建日期不能大于31天的
#1.0版本对于分发过来的数据(主要DW和ODS及DW_INDI等ESOP表)的日期属性进行判断，如果此表是日的30天内或月的3个月内
#且按照序号排列，到此表空间战用不超过400.00*0.75的表会保留，其它的日期段的表会删除!
#20111111新增加对表的本身进行判断,月表存储周期为3个月,日表存储周期为31天
#如现在是2011年11月11日,则2011年8月前的

月表会删除,日表则会删除2011年10月11日前的
db2 connect to esopdb;
log=${0%.*}.log
res=${0%.*}res.txt
cp /dev/null $res
if [ ! -e $log ]
then
cp /dev/null $log
echo "create sucess"
fi
sql="
with a as
(
select rownumber() over (order by
length(trim(case
when substr(tabname,length(trim(tabname))-7+1,7) like '_'||substr(char(year(current_date)),1,2)||'%'
then substr(tabname,length(trim(tabname))-6+1,6)
when substr(tabname,length(trim(tabname))-9+1,9) like '_'||substr(char(year(current_date)),1,2)||'%'
then substr(tabname,length(trim(tabname))-8+1,8) end )) asc,
case
when substr(tabname,length(trim(tabname))-7+1,7) like '_'||substr(char(year(current_date)),1,2)||'%'
then substr(tabname,length(trim(tabname))-6+1,6)
when substr(tabname,length(trim(tabname))-9+1,9) like '_'||substr(char(year(current_date)),1,2)||'%'
then substr(tabname,length(trim(tabname))-8+1,8) end desc
) od,trim(tabschema)||'.'||trim(tabname) as tabname,decimal(sum(npages)*1.0000*32768/(1024*1024*1024),7,4) gusage,create_time ct,case
when substr(tabname,length(trim(tabname))-7+1,7) like '_'||substr(char(year(current_date)),1,2)||'%'
then substr(tabname,length(trim(tabname))-6+1,6)
when substr(tabname,length(trim(tabname))-9+1,9) like '_'||substr(char(year(current_date)),1,2)||'%'
then substr(tabname,length(trim(tabname))-8+1,8) end arg
from syscat.tables
where length(tabname)>10 and tabschema='DB2INFO' and tbspace='TBS_DW_USER' and type='T' and owner='DB2APP'
and
(
(
substr(tabname,length(trim(tabname))-7+1,7) like '_'||substr(char(year(current_date)),1,2)||'%'
and substr(tabname,length(trim(tabname))-6+1,6) between
substr(replace(char(current_date-100 month,ISO),'-',''),1,6) and substr(replace(char(current_date,ISO),'-',''),1,6)
)
or
(
substr(tabname,length(trim(tabname))-9+1,9) like '_'||substr(char(year(current_date)),1,2)||'%'
and substr(tabname,length(trim(tabname))-8+1,8) between
substr(replace(char(current_date-100 month,ISO),'-',''),1,8) and substr(replace(char(current_date,ISO),'-',''),1,8)
)
)
group by tabname,tabschema,create_time
),
b as
(
select a_s.od,a_s.tabname,a_s.gusage,a_s.arg,length(trim(a_s.arg)) ca
,case when length(trim(a_s.arg))=8 and trim(a_s.arg)
not between substr(replace(char(current_date-1 month,ISO),'-',''),1,8) and substr(replace(char(current_date,ISO),'-',''),1,8) then 1 end f1
,case when length(trim(a_s.arg))=6 and trim(a_s.arg)
not between substr(replace(char(current_date-3 month,ISO),'-',''),1,6) and substr(replace(char(current_date,ISO),'-',''),1,6) then 1 end f2
,sum(a_s.gusage) over(order by a_s.od) tot,a_s.ct
from a as a_s
)
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 dec

imal(7,4)) as char(10))||',percent:'||cast(cast(b.tot/400 as decimal(7,4)) as char(10))
||',created before '||cast(days(current_date)-days(ct) as char(2))||' days '||'--','00.','0.') as varchar(700)) as sql
from b where ((value(f1,0)=1 or value(f2,0)=1)) or tot>400.0000*0.75 order by od desc
"
#执行不出现标题类的内容
db2 -x $sql>>$res;
#分行并读取每行
tr ";" "\n" <${res} | while read line
do
echo ` date +"%Y%m%d %H:%M:%S" `>>$log
echo $line>>$log
db2 -tvx $line | tee -i -a $log
echo "">>$log
done
db2 connect reset;
db2 terminate;
－－－－－－－－－－－－－－－－－－
查看语句：
with a as
(
select rownumber() over (order by
length(trim(case
when substr(tabname,length(trim(tabname))-7+1,7) like '_'||substr(char(year(current_date)),1,2)||'%'
then substr(tabname,length(trim(tabname))-6+1,6)
when substr(tabname,length(trim(tabname))-9+1,9) like '_'||substr(char(year(current_date)),1,2)||'%'
then substr(tabname,length(trim(tabname))-8+1,8) end )) asc,
case
when substr(tabname,length(trim(tabname))-7+1,7) like '_'||substr(char(year(current_date)),1,2)||'%'
then substr(tabname,length(trim(tabname))-6+1,6)
when substr(tabname,length(trim(tabname))-9+1,9) like '_'||substr(char(year(current_date)),1,2)||'%'
then substr(tabname,length(trim(tabname))-8+1,8) end desc
) od,trim(tabschema)||'.'||trim(tabname) as tabname,decimal(sum(npages)*1.0000*32768/(1024*1024*1024),7,4) gusage,create_time ct,case
when substr(tabname,length(trim(tabname))-7+1,7) like '_'||substr(char(year(current_date)),1,2)||'%'
then substr(tabname,length(trim(tabname))-6+1,6)
when substr(tabname,length(trim(tabname))-9+1,9) like '_'||substr(char(year(current_date)),1,2)||'%'
then substr(tabname,length(trim(tabname))-8+1,8) end arg
from syscat.tables
where length(tabname)>10 and tabschema='DB2INFO' and tbspace='TBS_DW_USER' and type='T' and owner='DB2APP'
and
(
(
substr(tabname,length(trim(tabname))-7+1,7) like '_'||substr(char(year(current_date)),1,2)||'%'
and substr(tabname,length(trim(tabname))-6+1,6) between
substr(replace(char(current_date-100 month,ISO),'-',''),1,6) and substr(replace(char(current_date,ISO),'-',''),1,6)
)
or
(
substr(tabname,length(trim(tabname))-9+1,9) like '_'||substr(char(year(current_date)),1,2)||'%'
and substr(tabname,length(trim(tabname))-8+1,8) between
substr(replace(char(current_date-100 month,ISO),'-',''),1,8) and substr(replace(char(current_date,ISO),'-',''),1,8)
)
)
group by tabname,tabschema,create_time
),
b as
(
select a_s.od,a_s.tabname,a_s.gusage,a_s.arg,length(trim(a_s.arg)) ca
,case when length(trim(a_s.arg))=8 and trim(a_s.arg)
not between substr(replace(char(current_date-2 month,ISO),'-',''),1,8) and substr(replace(char(current_date,ISO),'-',''),1,8) then 1 end f1
,case when length(trim(a_s.arg))=6 and trim(a_s.arg)
not between substr(re

place(char(current_date-5 month,ISO),'-',''),1,6) and substr(replace(char(current_date,ISO),'-',''),1,6) then 1 end f2
,sum(a_s.gusage) over(order by a_s.od) tot,a_s.ct
from a as a_s
)
select b.tabname,b.od,b.gusage,
b.tot,b.tot*1.000/400 per,cast(days(current_date)-days(ct) as char(2)) cd
from b --where ((value(f1,0)=1 or value(f2,0)=1)) or tot>400.0000*0.75
order by od desc
－－－－－－－－－－－－
TBS_ST空间检查
with a as
(
select rownumber() over (order by tabname ) od
,trim(tabschema)||'.'||trim(tabname) as tabname,decimal(sum(npages)*1.0000*32768/(1024*1024*1024),7,4) gusage,create_time ct
from syscat.tables
where tbspace='TBS_ST' and type='T' and owner='DB2APP'
group by tabname,tabschema,create_time
),
b as
(
select a_s.od,a_s.tabname,a_s.gusage,sum(a_s.gusage) over(order by a_s.od) tot,a_s.ct
from a as a_s
)
select b.tabname,b.od,b.gusage,
b.tot,b.tot*1.000/400 per,cast(days(current_date)-days(ct) as char(2)) cd
from b --where ((value(f1,0)=1 or value(f2,0)=1)) or tot>400.0000*0.75
where b.gusage>0.5 --大于500M的表
order by od desc
---------------------------------
select TBSP_NAME as s, sum(TBSP_USABLE_PAGES)*32/1024/1024 as total_GB, max(
tbsp_used_pages)*count(1)*32/1024/1024 as use_GB, min(TBSP_FREE_PAGES)*
count(1)*32/1024/1024 as free_GB
from SYSIBMADM.SNAPTBSP_PART
where TBSP_NAME not in ('SYSTOOLSTMPSPACE','SYSCATSPACE','SYSTOOLSPACE',
'TEMPSPACE1','USERSPACE1')
group by TBSP_NAME;