Richard Sims wrote:

Does anyone know a fast way to get a list of all volumes on which a node
has files of a specific subdirectory? A query of the volumeusage table
does the job on filespace level but not on file/directory level.
The only way I know is to search in the output of query content or the
corresponding select query which is very slow.

The Content query is the standard method - which has to wend its way through the
whole file inventory to gather the limited information you need.
There is another, "creative" approach: Perform a client restore of that
subdirectory to a trash area, and then see what tapes were mounted.  :-)
If you have ready access to the same type of system as that client, and know the
client's password, you can perform the restoral cross-node, and not actually
have to use that client.  Restore doesn't have to go through the SQL layers that
we have to when issuing server commands, and is certainly far faster.

In fact I often wondered why a restore of a specific file or directory
can start so fast if a simple query for the same things is so slow. I
understand that walking through the file inventory is time consuming as
well as the SQL layers. I hoped there is a method to do it the same way
the restore does it.

Although I agree perfectly that your approach would be much faster it is
not feasible in this case:
The reason behind the original question was to get in advance a list of
offsite tapes which have to be checked in for a file or directory
restore operation before starting the restore itself. If you have no
collocation and quite small tapes like 3570 the list you will get by a
simple volumeusage query can be fairly large and it would be nice to
shrink it to the really needed tapes.

Thanks for all answers,
Werner