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.