I've scheduled this script to run every day to keep an eye on our volumes.
I don't look at all of it every day, but if there is a problem, I can
usually find the cause in the output. NOTE: YMMV and PLEASE test the
sections in your environment to see how much cpu and time the queries may
take.
Bob.
**********************************************
/* total volumes in the library */
select count(*) as "Total volumes in the LTO Library " from libvolumes
/* total scratch */
select count(*) as "Scratch volumes in the LTO Library " from libvolumes
where status='Scratch'
/* newest scratch */
select max(volume_name) as "Newest Volume" from volhistory
/* Total DR Media volumes */
select COUNT(*) as "DRMedia Volumes" from drmedia
/* Total onsite volumes */
select COUNT(*) as "Media Volumes" from media
/* Writable volumes - watch for too few writable in a collocated
pool */
select stg.collocate as "Collocated", -
vol.stgpool_name as "Storage Pool",-
count(*) as "Writable Vols" -
from stgpools stg, volumes vol -
where stg.stgpool_name=vol.stgpool_name and -
vol.devclass_name='LTOCLASS1' and -
(vol.status='EMPTY' or vol.STATUS='FILLING') and -
vol.access<>'OFFSITE' -
group by stg.collocate, -
vol.stgpool_name
/* Allocated and available Scratch pools - watch for 0 vols scratch
*/
select stgpools.stgpool_name, -
stgpools.maxscratch, -
count(stgpools.maxscratch) as "allocated_scratch", -
stgpools.maxscratch-count(stgpools.maxscratch) as
"remaining_scratch" -
from stgpools,volumes -
where (volumes.stgpool_name=stgpools.stgpool_name) and -
((stgpools.devclass='LTOCLASS1')) -
group by stgpools.stgpool_name, -
stgpools.maxscratch
/* Find LRU volumes in the library */
select libv.volume_name, -
vol.stgpool_name, -
vol.status, -
date(current_timestamp)-date(vol.last_read_date) as "Read", -
date(current_timestamp)-date(vol.last_write_date) as "Written" -
from libvolumes libv, -
volumes vol -
where libv.volume_name=vol.volume_name -
order by "Read" desc, -
"Written" desc
/* find offline drives */
q drive
/* find offline paths */
q path
/* show Cleaners, Scratch, DB vols, and improperly checked in
volumes */
select volume_name, -
status, -
last_use, -
home_element -
from libvolumes -
where volume_name not in (select volume_name from volumes)
/* Show details of library volumes - old, full volumes are eject
candidates */
/* old, full volumes are eject candidates */
/* small, volumes w/o recent write are move data candidates */
select lib.volume_name as "Vol Name", -
vol.stgpool_name as "Stg Pool", -
vol.status, -
vol.pct_utilized as "Util", -
date(vol.last_write_date) as "Last Write" -
from volumes vol, -
libvolumes lib -
where lib.volume_name=vol.volume_name -
order by "Last Write", -
vol.status, -
vol.pct_utilized
/* filling volumes */
/* look for small tapes that haven't been recently written for move
candidates */
select vol.stgpool_name as "Stg Pool", -
vol.volume_name as "Vol Name", -
vol.pct_utilized as "Util", -
date(vol.last_write_date) as "Last Write", -
vol.location -
from volumes vol -
where devclass_name='LTOCLASS1' and -
status='FILLING' -
order by vol.stgpool_name, -
vol.pct_utilized
/* query number of volumes a node occupies - large numbers = painful
restores */
/* these are candidates for a full backup */
select distinct stgpool_name, node_name, -
count(distinct volume_name) as "Volumes" -
from volumeusage -
group by stgpool_name, -
node_name -
order by stgpool_name, -
"Volumes" desc
/* query number of volumes a filespace occupies - large numbers =
painful restores */
select distinct stgpool_name, node_name, filespace_name, -
count(distinct volume_name) as "Volumes" -
from volumeusage group by stgpool_name, -
node_name, filespace_name -
order by stgpool_name, node_name, -
filespace_name
/* volume inventory section - lists all known volumes and location
for tape vault reconciliation */
/* list all scratch volumes */
select distinct volume_name, -
max(date(date_time)) as "Date Scratched" -
from volhistory -
where type='STGDELETE' and -
devclass='LTOCLASS1' and -
volume_name not in(select volume_name from volumes) and -
volume_name not in(select volume_name from drmedia) -
group by volume_name -
order by volume_name
/* list all database backups */
select volume_name, -
location, -
voltype, -
state -
from drmedia -
where voltype<>'CopyStgPool' -
order by location, -
volume_name
/* list all TSM volumes */
select volume_name, -
location, -
status, -
access -
from volumes -
where devclass_name='LTOCLASS1' -
order by location, -
volume_name
**********************************************
-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
Johnny Lea
Sent: Tuesday, November 27, 2007 8:21 PM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: [ADSM-L] HELP!! TIPS on freeing up library slots
I used the "overflow location" once with success when I was in this same
situation. Just long enough to put in some scratch tapes and move data.
I wouldn't recommend it for normal occasions though.
Johnny Lea CNE
Data Center Manager
Division of Information Systems
University of MS Medical Center
601-984-6398
fax 601-815-3222
jlea AT dis.umsmed DOT edu
>>> Nicholas Rodolfich <nrodolfich AT EJGH DOT ORG> 11/27/07 9:13 AM >>>
Hi All,
Thanks for your help!!
I have TSm 5.3.5 on AIX 5.3.5 using an IBM 3584 w/16 LTO drives. I have run
out of slots in the library and can't seem to get enough scratch volumes in
to get any decent move data commands flowing based on pct_reclaim values.
Does anyone have any "ideas" on how I could consolidate some volumes
quickly?
IMPORTANT NOTICE: This message and any included attachments are from East
Jefferson General Hospital, and is intended only for the addressee(s), and
may include Protected Health (PHI) or other confidential information. If
you are the intended recipient, you are obligated to maintain it in a secure
and confidential manner and re-disclosure without additional consent or as
permitted by law is prohibited. If you are not the intended recipient, use
of this information is strictly prohibited and may be unlawful. Please
promptly reply to the sender by email and delete this message from your
computer. East Jefferson General Hospital greatly appreciates your
cooperation.
Individuals who have received this information in error or are not
authorized to receive it must promptly return or dispose of the information
and notify the sender. Those individuals are hereby notified that they are
strictly prohibited from reviewing, forwarding, printing, copying,
distributing or using this information in any way.
This electronic transmission and any documents accompanying this electronic
transmission contain confidential information belonging to the sender. This
information may be legally privileged. The information is intended only for
the use of the individual or entity named above. If you are not the intended
recipient, you are hereby notified that any disclosure, copying, distribution,
or the taking of any action in reliance on or regarding the contents of this
electronically transmitted information is strictly prohibited.
|
