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