ADSM-L

Re: Storage Pools/Volumes SQL

2004-06-21 04:26:32
Subject: Re: Storage Pools/Volumes SQL
From: Paul Ripke <stix AT STIX.HOMEUNIX DOT NET>
To: ADSM-L AT VM.MARIST DOT EDU
Date: Mon, 21 Jun 2004 18:25:29 +1000
On Monday, Jun 21, 2004, at 06:04 Australia/Sydney, Benhayoune Khalid
wrote:

Hi all,

The following SQL gives me a wrong result for the column NB_VOLUMES :


tsm: TSM>select stgpool_name,maxscratch,est_capacity_mb,pct_utilized, -
cont> (select count(*) from volumes where
stgpool_name=stgpools.stgpool_name) as NB_VOLUMES -
cont> from stgpools -
cont> where devclass <> 'DISK' -
cont> order by stgpool_name

STGPOOL_NAME            MAXSCRATCH          EST_CAPACITY_MB
PCT_UTILIZED
NB_VOLUMES
------------------     -----------     --------------------
------------
-----------
ARCH2004                        15                5722020.0
  4.2
2
COP2004                         30               11444040.0
  2.1
2

Not sure why the sub-query is not working, but an ordinary inner join
seems to work:

tsm: FRED>select stgpools.stgpool_name,stgpools.maxscratch,count(*) as
nb_volumes -
cont> from stgpools, volumes -
cont> where stgpools.stgpool_name = volumes.stgpool_name and devclass
<>'DISK' -
cont> group by stgpools.stgpool_name, stgpools.maxscratch -
cont> order by stgpools.stgpool_name
ANR2963W This SQL query may produce a very large result table, or may
require a significant amount of time to compute.

Do you wish to proceed? (Yes (Y)/No (N)) y

STGPOOL_NAME            MAXSCRATCH      NB_VOLUMES
------------------     -----------     -----------
OFFSITE                       1000             306
TAPE1                          500              92
TAPE2                          500              69
TAPE3                          500              81

Cheers,
--
Paul Ripke
Unix/OpenVMS/TSM/DBA
I love deadlines. I like the whooshing sound they make as they fly by.
-- Douglas Adams

<Prev in Thread] Current Thread [Next in Thread>