To check defined collocation groups:
select COLLOCGROUP_NAME, count(*) from collocgroup group by collocgroup_name
To get a list of COPY_ARCH tapes present in ATL
select volume_name,pct_utilized from volumes where stgpool_name='COPY_ARCH' and
volume_name in (select volume_name from libvolumes)
To get a count/list of tapes with errors
select count(*) as error_tapes_count from volumes where access not in
('READWRITE','OFFSITE','READONLY') or write_errors<>0 or read_errors<>0 and status
<> 'EMPTY'
select volume_name,stgpool_name,access,read_errors,write_errors,pct_utilized from
volumes where access not in ('READWRITE','OFFSITE','READONLY') or write_errors<>0
or read_errors<>0 and status <> 'EMPTY' order by stgpool_name
HEALTH CHECK:
show time
select server_name,restart_date from status
select date_time as dbbackup_date,type,volume_name as dbbackup_tape from volhistory
where date_time>=current_timestamp - 24 hours and type='BACKUPFULL'
select cast(sum(USED_DB_SPACE_MB/tot_file_system_mb)*100 as decimal(8,2)) as
db_pct_utilized from db
select cast(sum(USED_SPACE_MB/TOTAL_SPACE_MB)*100 as decimal(8,2)) as
log_pct_utilized from log
select count(*) as scratches_available from libvolumes where status='Scratch'
select count(*) as offsite_tapes_arch from VOLUMES where STGPOOL_NAME='COPY_ARCH'
and volume_name in (select volume_name from libvolumes)
select count(*) as vaultretrieve_tapes from drmedia where state='VAULTRETRIEVE'
select count(*) as unavailable_tapes from libvolumes where status='Unavailable'
select count(*) as error_tapes from volumes where access not in
('READWRITE','OFFSITE','READONLY') or write_errors<>0 or read_errors<>0 and status
<> 'EMPTY' select count(*) as tapes_reclamation_greaterthan_90percent from volumes
where pct_reclaim>=90
select count(*) as expiration_processes_in_last24hrs from actlog where msgno='812'
and date_time>=current_timestamp - 24 hours
To get a list of scratch tapes with status "private"
select volume_name from libvolumes where status='Private' and last_use is Null and
volume_name not in (select volume_name from volumes) and volume_name not in (select
volume_name from volhistory where type in ('BACKUPFULL', 'BACKUPINCR',
'DBSNAPSHOT', 'DBDUMP'))
To get a count of tapes with percentage reclamation range up to 90
select stgpool_name,count(case when STATUS not in ('EMPTY','PENDING') and
PCT_RECLAIM < 50 then volume_name end) as LESSFIFTY,count(case when STATUS not in
('EMPTY','PENDING') and PCT_RECLAIM>=50 and PCT_RECLAIM < 60 then volume_name
end) as FIFTY,count(case when STATUS not in('EMPTY','PENDING') and PCT_RECLAIM
>=60 and PCT_RECLAIM < 70 then volume_name end) as SIXTY,count(case when STATUS
not in ('EMPTY','PENDING') and PCT_RECLAIM >= 70 and PCT_RECLAIM < 80 then
volume_name end) as SEVENTY,count(case when STATUS not in ('EMPTY','PENDING') and
PCT_RECLAIM >= 80 and PCT_RECLAIM < 90 then volume_name end) as EIGHTY,count(case
when STATUS not in ('EMPTY','PENDING') and PCT_RECLAIM >=90 and PCT_RECLAIM <
100 then volume_name end) as NINTY from volumes where DEVCLASS_NAME not in ('DISK')
group by stgpool_name
To get a list of schedules defined
SELECT domain_name, schedule_name, count(*) FROM associations GROUP BY domain_name,
schedule_name
scratch tape count in ATL
select count(*) as Scratch from libvolumes where status='Scratch'
To check data stored in GB for each storage pool
select STGPOOL_NAME,sum(PHYSICAL_MB)/1024 as TOTAL_GB from OCCUPANCY group by
STGPOOL_NAME
Nodes registered before the requested date:
select node_name,reg_time from nodes where reg_time>'2015-10-14 00:00:00'
Label command:
label libvol 3584ATL volrange=FL2789JB,FL2808JB checkin=scr labels=barc search=bulk
label libvol FL2789JB libraryname checkin=scr labels=barc search=bulk
select cast( entity as char(16)) as NODE,START_TIME , END_TIME
,bytes/1024/2014/1024 as DATA_GB from summary where activity='BACKUP' and
end_time>current_timestamp - 250 hours and entity='WWPPSLORAD1'
select cast( entity as char(16)) as NODE,START_TIME , END_TIME
,bytes/1024/2014/1024 as DATA_GB from summary where activity='ARCHIVE' and
end_time>current_timestamp - 250 hours and entity='WWPPSLORAD1_10Y'
select cast( entity as char(16)) as NODE , START_TIME , END_TIME
,bytes/1024/2014/1024 as DATA_GB, timestampdiff(4,char(timestamp(end_time) -
(start_time))) as Backup_Duration_Minutes from summary where activity='BACKUP'
and end_time>current_timestamp - 240 hours and entity='WWPPSLORAD1'
select cast( entity as char(16)) as NODE,START_TIME , END_TIME
,bytes/1024/2014/1024 as DATA_GB from summary where entity='EXPIRATION' and
end_time>current_timestamp - 250 hours and entity='WWPPSLORAD1_10Y'
select cast( entity as char(16)) as NODE,START_TIME , END_TIME ,bytes/1024/2014
as DATA_GB,ACTIVITY from summary where activity<>'EXPIRATION' and
end_time>current_timestamp - 6000 hours and entity='WWPPSLORAD1_10Y'
select cast( entity as char(16)) as NODE , START_TIME , END_TIME
,bytes/1024/2014 as DATA_GB, timestampdiff(4,char(timestamp(end_time) -
(start_time))) as Backup_Duration_Minutes from summary where activity='BACKUP'
and end_time>current_timestamp - 240 hours and entity='WWPPSLORAD1_ORA'
select node_name,REG_TIME where REG_TIME >{'2015-05-01 00:00:00'}
scp inavgyu3@10.172.200.119:/tmp/IBM_TSM_gb02qad010bfsxm_20Nov2015.mef3
/home/sruthi/Desktop
select count (*) as offsite_tapes_arch from volumes where stgpool_name='COPY_ARCH'
and volume_name in (select volume_name from libvolumes)
select volume_name as offsite_tape from volumes where stgpool_name='COPY_ARCH' and
volume_name in (select volume_name from libvolumes)
restore
/exchangeProdAPTP/g700pd/ftpfiles/CLIENT_COLLECT/sg_sgldn_gloss_aptp_jtrn_20151228_
2230.xml /sybdump/g700pd/SAVED_DUMPS/G700/CLIENT_COLLECT/ -ina
restore /tmp/sg_aptp_tlmrec_rec_stmt_MT536_SOGEGB22XXX-DAKVDEFFDOM-
8696_20160113_1805018.txt /home/aptp/TLM/datafeeds/waiting/ -ina
in TLM RP (10.172.0.34) in Prod
cp /tmp/sg_aptp_tlmrec_rec_stmt_MT536_SOGEGB22XXX-DAKVDEFFDOM-
8696_20160113_1805018.txt /home/aptp/TLM/datafeeds/waiting/
cp: cannot stat `sg_aptp_tlmrec_rec_stmt_MT536_SOGEGB22XXX-DAKVDEFFDOM-
8696_20160113_1805018.txt'
select volume_name from libvolumes where status='Private' and last_use is Null and
volume_name not in (select volume_name from volumes) and volume_name not in (select
volume_name from volhistory where type in ('BACKUPFULL','BACKUPINCR', 'DBSNAPSHOT',
'DBDUMP'))
