Re: SQL to determine what offsite volumes are needed for move dat a

Bill,

I also have only 2 tape drives and was running in to the problem of tapes
going off sight and never coming back.  I solved it by creating a
reclamation script that does the following (pseudo code):
for stgpool in TAPEPOOL COPYPOOL
do
  while [count(stgpool tapes > 51% reclaimable) -gt 0]
  do
    find percent_reclaimable of most reclaimable tape in stgpool
    update stg stgpool recl=percent_reclaimable
    while reclaimation in process
    do
      sleep 5 minutes
    done
    update stg stgpool recl=100
  done
done

With this approach I have brought the count of COPYPOOL tape >51%
reclaimable down from ~130 to ~6.  I was pleasantly surprised by the impact
this approach had.

Thanks,
Milton Johnson
Voice: 210-677-6728
Email: milton.johnson AT citigroup DOT com



-----Original Message-----
From: Bill Kelly [mailto:kellywh AT auburn DOT edu]
Sent: Thursday, October 16, 2003 9:54 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: SQL to determine what offsite volumes are needed for move data


Hi,

We're running TSM server 5.1.6.2 on z/OS.

One of the (many) resources we're short on is tape drives.  Consequently,
I'm always looking to streamline processes that use these drives.  Here's
the problem I'm currently looking at:

Our tape copy pools are kept offsite.  Reclamation for these pools is
handled by leaving the TSM reclamation threshhold at 100% so that he never
does reclaims on his own.  On a regular basis, we run a job that queries the
server for copy pool tapes with a reclamation threshhold greater than 'n'
percent.  This list of tapes is used by the operators to go and fetch the
tapes to be brought onsite.  They then run a job that updates those tapes to
access=readwrite, and issues a 'move data' command for each tape.

Now the problem.  Some of these 'move data' processes treat the volume that
is being emptied as 'offsite', even though the volume has been loaded into
the library and its access updated to readwrite.  I'm pretty sure the reason
for this is that the volumes in question have files at the beginning and/or
end that span to other copy pool volumes which are themselves still offsite.

If the volume being emptied is treated as 'onsite', then the move data runs
pretty quickly - the copy pool volume is mounted for input, the data is
copied, and the volume goes pending.  However, if the volume being emptied
is treated as 'offsite', TSM will perform the move data by mounting for
input *all* of the primary pool tapes that have data on this copy pool tape.
Since our primary tape pools are collocated, while our copy pools are not,
this results in dozens of tape mounts for primary pool volumes to use as
input.  The move data process can take hours in this case, tying up tape
drives much longer than necessary.

For the moment, I'll ignore the question of whether TSM should be smart
enough to mount only the one or two primary pool volumes that contain the
spanned files, and use the single copy pool volume that's being emptied for
all the other data.

The way I've been handling this is rather cumbersome.  These are the steps I
take:

- after the move data's have started, issue a 'q proc' command
- cancel all of the move data's that are treating their input volume as
  'offsite'
- issue an 'audit v' command for each of the copy pool volumes being
  treated as 'offsite'
- each audit volume process fails with the following message:
  ANR2456E AUDIT VOLUME: Unable to access associated volume NNNNNN -
  access mode is set to "offsite".
- this tells me which additional copy pool volume needs to be brought
  onsite in order to make a move data process treat the original volume to
  be emptied as an onsite volume
- go get the additional offsite volumes needed, load them into the
  library, update their access to readwrite, and issue move data commands
  for the original volumes being treated as offsite by TSM

Then, of course, the entire process has to be repeated because the 'audit
volume' command will only tell me *one* offsite volume that might be needed;
if a volume has files that span from both the beginning and end of the tape,
I won't know that until the 2nd round of 'move data' commands is issued.

As you can see, this is a laborious, difficult-to-automate process. Things
would be greatly simplified if we could tell right up front which copy pool
volumes were going to be treated as offsite, and which additional copy pool
volumes would be needed to be brought onsite in order to make the move
data's all run as 'onsite'.  Having this information up front would allow me
to build a list of *all* tapes needing to be brought onsite, requiring only
one trip to the offsite location, saving all the hassle of
canceling/auditing/etc.

I *know* that the information about which offsite volumes are needed must be
easy/quick to retrieve, because the 'audit volume' commands fail instantly
with the message telling me what offsite volume is needed.

So, here's my question (finally!): can anyone provide SQL that could be used
to tell me, given a copy pool volume, are there files on that volume that
span to other copy pool volumes, and, if so, what are those other copy pool
volumes?

Or does anyone have a better approach to solving this problem?

Thanks,
Bill

Bill Kelly
Auburn University
334-844-9917