自己工作用到的几个TSM Server查询SQL

1，长期不活动磁带的数量-3年内
select count(VOLUME_NAME) as VOLUME_NUM from VOLUMEs -
where STATUS='FULL' and LAST_WRITE_DATE<current_timestamp-3 years -
and LAST_READ_DATE <current_timestamp-3 years

2.1，TSM5版本，长期不活动磁带的基本信息
select VV.VOLUME_NAME,VV.STGPOOL_NAME,DD.LIBRARY_NAME,date(VV.LAST_WRITE_DATE) as LAST_WRITE_DATE -
from VOLUMEs VV, DEVCLASSES DD -
where VV.DEVCLASS_NAME=DD.DEVCLASS_NAME -
and VV.STATUS='FULL'  -
and VV.LAST_WRITE_DATE<current_timestamp-3 years -
and VV.LAST_READ_DATE <current_timestamp-3 years -
order by LAST_WRITE_DATE

2.2，TSM 6以上版本，长期不活动磁带的基本信息
select char(VV.VOLUME_NAME,20) as VOLUME_NAME,  VV.STGPOOL_NAME,DD.LIBRARY_NAME,date(VV.LAST_WRITE_DATE) as LAST_WRITE_DATE -
from VOLUMEs VV, DEVCLASSES DD -
where VV.DEVCLASS_NAME=DD.DEVCLASS_NAME -
and VV.STATUS='FULL'  -
and VV.LAST_WRITE_DATE<current_timestamp-3 years -
and VV.LAST_READ_DATE <current_timestamp-3 years -
order by LAST_WRITE_DATE

3，需要回收的磁带
select char(volume_name,30),char(stgpool_name,10),est_capacity_mb,pct_utilized,char(status,10),char(access,10) \
from volumes  where status='FULL' and pct_utilized<20 order by pct_utilized

4，不可用磁带的数量
select count(*) as UNAVA_VOLUMEs_NUM from  VOLUMEs where ACCESS='UNAVAILABLE'

5.1，TSM5版本，不可用磁带的信息

select VV.VOLUME_NAME,VV.STGPOOL_NAME,VV.ACCESS,DD.LIBRARY_NAME,date(VV.LAST_WRITE_DATE) as LAST_WRITE_DATE -
from VOLUMEs VV, DEVCLASSES DD -
where VV.DEVCLASS_NAME=DD.DEVCLASS_NAME -
and VV.ACCESS='UNAVAILABLE'  

5.2 TSM 版本，不可用磁带的信息
select char(VV.VOLUME_NAME,20) as VOLUME_NAME, char(VV.STGPOOL_NAME,20) as STGPOOL_NAME, -
char(VV.ACCESS,16) as ACCESS, char(DD.LIBRARY_NAME,20) as LIBRARY_NAME,date(VV.LAST_WRITE_DATE) as LAST_WRITE_DATE -
from VOLUMEs VV, DEVCLASSES DD -
where VV.DEVCLASS_NAME=DD.DEVCLASS_NAME -
and VV.ACCESS='UNAVAILABLE' 

手头有点事暂时整理到这里，谁需要可以QQ我：630250568