tsm常用SQL

这些都是我日常中为了方便自己使用写的，SQL语句学的不好，写的不好，见笑了，贴出来方便大家使用，我会持续更新的。

select VOLUME_NAME,PCT_UTILIZED,STATUS,ACCESS,PCT_RECLAIM from volumes where STGPOOL_NAME='ECCDBPOOLA'
select VOLUME_NAME,PCT_UTILIZED,STATUS,ACCESS,PCT_RECLAIM from volumes where DEVCLASS_NAME='3584CLASS' order by PCT_RECLAIM desc
select VOLUME_NAME,access,status,PCT_UTILIZED,PCT_RECLAIM from volumes where STGPOOL_NAME='NONECCDBPOOL' and status!='FULL'
select volume_name from volumes where access!='READWRITE'
select DOMAIN_NAME,SET_NAME,CLASS_NAME,COPYGROUP_NAME,DESTINATION from BU_COPYGROUPS where DESTINATION like 'VTL%'
select distinct a.node_name,a.domain_name,a.TCP_NAME,a.TCP_ADDRESS,b.DESTINATION from nodes a,bu_copygroups b where a.domain_name in (select DOMAIN_NAME from BU_COPYGROUPS where DESTINATION like 'VTL%') and a.domain_name=b.domain_name

select Drive_name,DRIVE_SERIAL,Drive_State,ELEMENT,ALLOCATED_TO,volume_name from drives where DRIVE_NAME like '3584DR%'
select Drive_name,Drive_State,ALLOCATED_TO,volume_name from drives where DRIVE_NAME like '3584DR%'
select * from backups where node_name='xxxxxx235_ORA' order by BACKUP_DATE desc
select * from backups where node_name LIKE 'xxxxx105%' and ll_name like '%20110305131758%'
select * from contents where volume_name='B00024L4'
show bfobject 0 909548448
select SCHEDULE_NAME,NODE_NAME,STATUS,RESULT,SCHEDULED_START from events where STATUS!='Completed'
select session_id,STATE,CLIENT_NAME from sessions where CLIENT_NAME like '%227%'
select distinct NODE_NAME from contents where volume_name='B00067L4'
select count(volume_name) from libvolumes where library_name='3584LIB' and status='Scratch'
select distinct node_name,volume_name,stgpool_name from volumeusage where node_name='xxxx115_NGD'
select distinct node_name from volumeusage where node_name='xxx15_NGD'
select * from contents where FILE_NAME like '%FULL_BACKUP.20110307163656.3' and node_name='xxxx' and VOLUME_NAME in (select distinct node_name from volumeusage where node_name='xxxx')
select distinct a.VOLUME_NAME,a.NODE_NAME,b.LAST_WRITE_DATE from volumeusage a, volumes b where a.volume_name=b.volume_name and a.NODE_NAME='xxxx'
select count(DISTINCT volume_name) as volumes, node_name, stgpool_name from volumeusage group by node_name, stgpool_name order by volumes desc
select * from backups where node_name='xxxxx' order by BACKUP_DATE descselect distinct a.volume_name,a.node_name,a.stgpool_name,b.PCT_UTILIZED,b.PCT_RECLAIM,b.access from volumeusage a, volumes b where a.node_name='xxxx' and a.volume_name=b.volume_name

select VOLUME_NAME,NODE_NAME,FILE_NAME,FILE_SIZE from contents where FILE_NAME like '%20110220181834%' and NODE_NAME='xxxx'

SELECT * from actlog where date_time>=current_timestamp-1 days and SEVERITY='E' and ORIGINATOR='CLIENT'
select date_time,severity,message from actlog where nodename like 'xxxx%' and date_time>'2011-03-11 00:00:00.000000'
select * from actlog where nodename='CRMDB1';
select date_time,severity,message,nodename from actlog where nodename='X00026L4' and date_time>'2011-03-11 00:00:00.000000' and SEVERITY!='I'

select * from backups where node_name='xxxx' AND LL_name='%S0008%'

checkout libvolume 3584lib B00000L4 checklabel=yes remove=bulk force=yes
checkin libvolume 3584lib  B00000L4  status=scratch checklabel=barcode SWAP=yes
select node_name as NODENAME,sum(case when substr(stgpool_name,1,3) in ('ARC') then logical_mb else 0 end) as ARC_OCC,sum(case when substr(stgpool_name,1,3) in ('TAP') then logical_mb else 0 end) as BCK_OCC,sum(case when substr(stgpool_name,1,3) in ('TDP') then logical_mb else 0 end) as TDP_OCC from occupancy group by node_name

select node_name,sum(PHYSICAL_MB) as PHYSICAL_MB,sum(logical_mb) as logical_mb from occupancy group by node_name order by logical_mb desc
select node_name,sum(PHYSICAL_MB) as PHYSICAL_MB,sum(logical_mb) as logical_mb from occupancy where node_name in (select distinct a.node_name from nodes a,bu_copygroups b where a.domain_name in (select DOMAIN_NAME from BU_COPYGROUPS where DESTINATION like 'VTL%') and a.domain_name=b.domain_name) group by node_name

select * from contents where FILE_NAME like '%FULL_BACKUP.20110307163656.3' and node_name='xxxxxx' and VOLUME_NAME in (select distinct node_name from volumeusage where node_name='xxxxx')

select count(*) from paths where DESTINATION_NAME='3584DR6'
select SOURCE_NAME from paths where DESTINATION_NAME='3584DR6'