Off the top of my head, wouldn't that be:

/*                                              */
/* macro file to select the volumes that a node */
/* is stored on.                                */
/*                                              */
set sqldatetimeformat i
set sqldisplaymode w
set sqlmathmode r
commit
  select node_name, count (distinct volume_name) Numvols -
    from adsm.volumeusage  -
   where stgpool_name = '9840APOOL' -
group by node_name
  > e:\adsmcv3\sqllists\voluse3



On Wed, 17 Mar 1999, John M. Sorensen wrote:

>Actually, the query I would like to see has output
>that looks like this (for example):
>
>NODE_NAME               Count of Volumes
>---------               ----------------
>MELMARJO                3
>NODEA                   1
>NODEB                   32
>
>... and so forth.
>
>On a server with a hundred or so clients, having
>output like this would enable me to quickly spot
>any node with a problem.
>
>Then, for a particular node, like NODEB with
>32 volumes, I would execute Bill's query to find
>WHICH volumes have the data, and move the data if
>I need to.
>
>-----Original Message-----
>From:   Bill Colwell [SMTP:bcolwell AT DRAPER DOT COM]
>Sent:   Wednesday, March 17, 1999 2:58 PM
>To:     ADSM-L AT VM.MARIST DOT EDU
>Subject:        Re: restore times
>
>A better select statement to list the volumes a node is on without
>getting duplicates for each filespace is --
>
>/*                                              */
>/* macro file to select the volumes that a node */
>/* is stored on.                                */
>/*                                              */
>set sqldatetimeformat i
>set sqldisplaymode w
>set sqlmathmode r
>commit
>select distinct volume_name -
>   from adsm.volumeusage  -
>   where stgpool_name = '9840APOOL' -
>     and node_name = %1 -
>  > e:\adsmcv3\sqllists\voluse3
>
>To run this, store it as a fill in the directory where dsmadmc is,
>and then enter "macro <filename> 'NODENAME_IN_CAPS' ".
>Then edit the output file \voluse3.  Adjust the redirection name
>as necessary.
>
>
>To comment on the thread in general, a Consolidate command would be
>a good thing to have.  I submitted a SHARE requirement for one 4 years ago when
>I wasn't doing collocation.  I have since switched to collocation so there are 
>other
>thing I would like development to do first.
>
>I think there is a lot of misconceptions about collocation and how to 
>implement it.
>It will always buy you something on the restore time, but you pay on the 
>migration
>time.  You don't need 1 or more tapes for every node.  ADSM will double up, 
>triple up,
>etc.  as Wanda pointed out.
>
>--
>--------------------------
>Bill Colwell
>C. S. Draper Lab
>Cambridge, Ma.
>bcolwell AT draper DOT com
>--------------------------
>
>In <01BE707E.1928FF60.sorensen AT storsol DOT com>, on 03/17/99
>   at 01:57 PM, "John M. Sorensen" <sorensen AT STORSOL DOT COM> said:
>
>>I do have a comment on the SQL query you used to determine
>>how many volumes contain data for any client node.  It turns
>>out that you may be in much better shape than you think!
>>The problem with the query is that the number returned
>>in count(*) will count every individual filespace
>>from a client node as a separate row.
>
>>Here is an example from my server.  Client node MELMARJO is
>>a Windows 95 client.  It backs up 3 filespaces; APPS, USER
>>and WINSYS (I have removed some whitespace from the output
>>but the text is untouched).  This query returns all columns
>>defined for the table, so that the upcoming query returning
>>the count(*) can be interpreted.
>
>>adsm> SELECT * FROM VOLUMEUSAGE WHERE NODE_NAME='MELMARJO'
>