Here's a query that will give you a list of volumes and their status for a 
particular node :

select distinct -
    volumeusage.volume_name, -
    volumes.pct_utilized,status, -
    date(volumes.LAST_WRITE_DATE) as "Last write" -
from volumeusage,volumes -
where volumeusage.node_name=upper('$1') and -
    volumeusage.stgpool_name='stgpoolname' -
    and volumeusage.volume_name=volumes.volume_name -
order by volumeusage.volume_name

Of course replace stgpoolname with the name of your stroage pool. If you want 
the number of volumes for one node, issue this query :

select count(distinct(volume_name)) from volumeusage where node_name='nodename'

Guillaume Gilbert
CGI Canada




Justin Derrick <jderrick AT CANADA DOT COM>@VM.MARIST.EDU> on 2002-08-05 
12:31:36

Veuillez répondre à "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>

Envoyé par :   "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>


Pour :    ADSM-L AT VM.MARIST DOT EDU
cc :
Objet :   Re: SQL results

count(*) means 'count the number of records returned'.

*** Disclaimer ***  I haven't tested this SQL (I'm building my server
as I write this), and I've never queried the volumeusage table -- I'm
guessing here.  =)

You probably want to issue this command to simply get a number:

select count(volume_name) from volumeusage where node_name=xxx
   ... to get the number of volumes a node has files on.

Or, if you want the names of the volume, nix the 'count' function:
select volume_name from volumeusage where node_name=xxx

Or, if you want to run this for everyone...
select node_name,count(volume_name) from volumeusage group by node_name


If I might ask...  What are you trying to use these numbers for?

-JD.

>Hi all,
>
>We're trying to issue this
>
>tsm> select volume_name,count(*) from volumeusage where node_name=xxx group
>by volume_name
>
>VOLUME_NAME             Unnamed[2]
>------------------     -----------
>020259                          12
>020266                           4
>030298                          12
>030357                           4
>
>to find out how many volumes contain data from a particular node.
>
>What do these results tell me? From querying the contents of the vols, it
>looks like number of filespaces per volume.
>
>And my unnamed column seems to have the answer to my original question -
>'2'.
>Anyone seen this before or it is WAD?
>
>Regards
>
>Matthew Large
>TSM Infrastructure Engineer
>Lavington Street
>Int: 7430 4995
>Ext: 07736 448 808
>
>
>
>-----------------------------------------------------------------------------------------------------------------------
>This e-mail is intended only for the above addressee.  It may contain
>privileged information. If you are not the addressee you must not copy,
>distribute, disclose or use any of the information in it.  If you have
>received it in error please delete it and immediately notify the sender.
>
>evolvebank.com is a division of Lloyds TSB Bank plc.
>Lloyds TSB Bank plc, 71 Lombard Street, London EC3P 3BS.  Registered in
>England, number 2065.  Telephone No: 020 7626 1500
>Lloyds TSB Scotland plc, Henry Duncan House, 120 George Street,
>Edinburgh EH2 4LH.  Registered in Scotland, number 95237.  Telephone
>No: 0131 225 4555
>
>Lloyds TSB Bank plc and Lloyds TSB Scotland plc are regulated by the
>Financial Services Authority and represent only the Scottish Widows
>and Lloyds TSB Marketing Group for life assurance, pensions and
>investment business.
>
>Signatories to the Banking Codes.
>-----------------------------------------------------------------------------------------------------------------------