Try this one:
select node_name, sum(num_files) as NUM_FILES,
sum(logical_mb) as STORAGE_MB from occupancy
group by node_name
Richard.
Benhayoune Khalid <Benhayoune AT CIH.CO DOT MA>
Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
21-06-2004 12:05
Please respond to "ADSM: Dist Stor Manager"
To: ADSM-L AT VM.MARIST DOT EDU
cc:
Subject: Storage Pool Occupancy for each node report
Hi all,
I'm new in TSM SQL and I have a problem with the following statement :
select stgpools.stgpool_name as "STORAGE POOL", -
stgpools.est_capacity_mb, -
occupancy.node_name, -
sum(occupancy.Physical_Mb) as "OCCUPANCY (MB)", -
cast(sum(occupancy.Physical_Mb)/stgpools.est_capacity_mb*100 as
decimal(3,2)) as "%OCCUPED" -
from stgpools,occupancy -
where stgpools.stgpool_name = occupancy.stgpool_name -
group by
stgpools.stgpool_name,stgpools.est_capacity_mb,occupancy.node_name
which returns 0 (wrong result as you can see below) for the column
"%OCCUPED" for all rows.
STORAGE POOL: ARCH2004
EST_CAPACITY_MB: 5722020.0
NODE_NAME: MON1
OCCUPANCY (MB): 324.34
%OCCUPED: 0.00
STORAGE POOL: ARCH2004
EST_CAPACITY_MB: 5722020.0
NODE_NAME: PC-MARSAOUI
OCCUPANCY (MB): 10108.61
%OCCUPED: 0.00
STORAGE POOL: ARCH2004
EST_CAPACITY_MB: 5722020.0
NODE_NAME: PROD1
OCCUPANCY (MB): 230535.52
%OCCUPED: 0.00
STORAGE POOL: ARCH2004
EST_CAPACITY_MB: 5722020.0
NODE_NAME: RAVEN2
OCCUPANCY (MB): 0.11
%OCCUPED: 0.00
...
Should I rewrite the SQL ?
Any suggestion ?
Thank you very much.
|