Hi,
you can try this one...
Checks volumes of a NODE_NAME per storage pool Primairy and Copy.
/* -------------------------------------------*/
/* Script Name: Q_NODE_VOL */
/* Description: Query node for volumes */
/* occupancy */
/* Parameter: NODENAME */
/* Example: run q_node_vol nodename */
/* -------------------------------------------*/
select distinct volumeusage.volume_name as "Online Vol.",
volumes.pct_utilized as "%", -
volumes.est_capacity_mb as "Est. Cap", volumes.PCT_RECLAIM as "%
Reclaim" from volumeusage, -
volumes where volumeusage.volume_name=volumes.volume_name and
node_name=upper('$1') -
and volumeusage.stgpool_name in ( select distinct stgpool_name from
volumes where -
stgpool_name in (select distinct stgpool_name from stgpools where
pooltype='PRIMARY' -
and devclass<>'DISK') ) order by volume_name
select distinct volumeusage.volume_name as "Offline Vol.",
volumes.pct_utilized as "%", -
volumes.est_capacity_mb as "Est. Cap", volumes.location as "Location"
from volumeusage, -
volumes where volumeusage.volume_name=volumes.volume_name and
node_name=upper('$1') and -
volumeusage.stgpool_name in (select distinct stgpool_name from volumes
where stgpool_name -
in (select distinct stgpool_name from stgpools where pooltype='COPY'
and devclass<>'DISK') ) -
order by volume_name
Robert.
> Yep, I am an SQL newbie, but some help would be nice.
> The script at the bottom terminates with this message:
> 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.
> and points to the left(volume_name, 8) portion of the query. I have
played
> with it
> and still get this message. Could someone please explain why this is
> occuring and
> how I can fix my script?
>
> TIA ... Jack
>
> /* Tape-Volumes */
> /* Listing is for all known nodes, and the tape volumes required to
do a
> restore */
> set sqldatetimeformat i
> set sqldisplaymode w
> set sqlmathmode r
> commit
> select distinct copy_type, left(node_name, 12), -
> left(volume_name, 8), left(stgpool_name, 16) -
> from adsm.volumeusage -
> group by node_name, copy_type
>
>
|