I do it this way.
select distinct COLLOCGROUP_NAME,VOLUME_NAME  from COLLOCGROUP,VOLUMEUSAGE 
where STGPOOL_NAME='LOCALTAPE' and COLLOCGROUP.NODE_NAME=VOLUMEUSAGE.NODE_NAME 
group by COLLOCGROUP_NAME,VOLUME_NAME
To generate a list of volumes by collocation group.
I actually combine that information with this:
select COLLOCGROUP_NAME,sum(PHYSICAL_MB) from NODES,OCCUPANCY where 
OCCUPANCY.NODE_NAME=NODES.NODE_NAME and STGPOOL_NAME='LOCALTAPE' group by 
COLLOCGROUP_NAME
It's handy for identifying those collocgroups that mostly expire in FILE and 
are wasting tapes by being separate groups.


-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of 
stg admin
Sent: Thursday, September 17, 2009 2:00 PM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: [ADSM-L] SELECT FOR DISTINCT VOLUMES IN A COLLOCATION GROUP

Does anyone have a script that will show the volumes associated with a 
collocation group?

I'm trying to run this but receive an error;

select distinct vu.volume_name, cg.collocgroup_name from volumeusage vu, 
collocgroup cg where vu.node_name=cg.node_name and vu.stgpool_name='DRMC'
group by cg.collocgroup

ANR2938E The column 'VOLUME_NAME' is not allowed in this context; it must

either be named in the GROUP BY clause or be nested within an aggregate

function.

Thank you in advance!

Laura.