Iain, I don't think there is a way to do this, as there is no real
correlation between the SUMMARY and FILESPACES table. Certainly one issue
is that your FILESPACES query produces one row for each file space, while
the SUMMARY table does not have records by file space.

An alternative would be to issue both selects and then have some code that
massages them together into whatever report you want. Either that, or
leave them as separate reports.

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: storman AT us.ibm DOT com

The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.

"ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU> wrote on 2005-04-13
05:52:03:

> I use this query to get a report of the backups by node & filespace,
> showing the start & end times.
>
> select -
>      node_name as "Node", -
>      filespace_name as "Filespace", -
>      substr(char(filespaces.backup_start), 1, 10) as "Backup Date" ,-
>      substr(char(filespaces.backup_start), 12, 8) as "Start Time" ,-
>      substr(char(filespaces.backup_end), 12, 8) as "End Time"-
> from filespaces -
> order by node_name,filespace_name
>
>
>
> What would I have to add to this to get the corresponding node's 'bytes'
> from the summary table, formatted to 2x decimal places in Gb's.
>
> I've tried using this:
>
> select cast(float(sum(bytes))/1024/1024/1024 as decimal(14,2)) as GbBkUp
> from summary where (end_time between '2005-04-12 07:45:02' and
> '2005-04-13 07:45:01') and activity='BACKUP'
>
>
> But I'm not sure how to integrate that into the SQL query above.
>
> Any help greatly appreciated.
>
> Iain