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:ads...@vm.marist.edu] On Behalf Of stg admin Sent: Thursday, September 17, 2009 2:00 PM To: ADSM-L@VM.MARIST.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.
