Command to check Filespace in a particular volume

[nikhil_1707]

Hi Team,

I am looking for a select command which can tell the name of the volume in which a particular file(filespace) is stored.

 

[chad_small]

The easiest way to do this is to query the contents table.

SELECT NODE_NAME, VOLUME_NAME, FILESPACE_NAME, FILE_NAME, FILE_SIZE FROM CONTENTS WHERE NODE_NAME='<NAME HERE>' AND FILESPACE_NAME='<NAME HERE>' and FILE_NAME='<NAME_HERE>'

You can play with this and use different filters, but this is going to take awhile to return data if you don't narrow it down as much as possible. You can narrow it down even more if you know which node you are querying, then you can run a SELECT VOLUMEUSAGE sub-query on it and get the volume names the client uses and filter the select even more.

SELECT NODE_NAME, VOLUME_NAME, FILESPACE_NAME, FILE_NAME, FILE_SIZE FROM CONTENTS WHERE VOLUME_NAME IN (SELECT VOLUME_NAME FROM VOLUMEUSAGE WHERE NODE_NAME='<NAME HERE>') and NODE_NAME='<NAME HERE>' AND FILESPACE_NAME='<NAME HERE>' and FILE_NAME='<NAME_HERE>'

Now if all you need is the volumes with specific filespaces stored on them you can easily get that from the VOLUMEUSAGE table. (Note: I always identify filespaces using the FSID, it's just a lot easier.)

SELECT * FROM VOLUMEUSAGE WHERE NODE_NAME='<NAME HERE>' AND FILESPACE_ID=<NUMBER HERE NO QUOTES>

 

[Justin]

I found an example here: http://www.lascon.co.uk/d005104.htm#sqlfvol
But be warned, I ran it on a test server and it was slow to return.