Active Data Report

So me an a coworker have been hounding IBM for a way to generate an active data report for management. For various reasons they want to be able to see how much data a server would require for rebuild/restore and also to guage any growth. As many of you might be aware this has been something the TSM/Spectrum Protect community has been requesting for some time. So IBM created a perl script that when analyzed issues the following SQL select:

(I've added continuation dashes to make the select more readable)

select -
(sum(bk.bfsize )/1048576) as front_end_size_mega_byte, -
count(bk.bfsize ) as number_of_objects -
from -
 backups b, backup_objects bk -
where -
 b.state='ACTIVE_VERSION' -
 and -
 b.object_id=bk.objid -
 and -
 b.filespace_id in -
   ( select f.filespace_id -
     from filespaces f -
     where -
      b.node_name=f.node_name -
     and -
      f.filespace_id=b.filespace_id -
     and -
      f.filespace_type not like 'API:%' -
     and -
      f.filespace_type not like 'TDP%' -
   ) -
   and -
    b.node_name in -
   ( select node_name -
     from nodes -
     where repl_mode not in('RECEIVE','SYNCRECEIVE') -
    )

You will notice, however, that the select excludes node replicas and any API or TDP data. This is purely for file system backups. Supposedly they have something for TDP/API backups but I have not worked with it yet. I will post it as soon as I have a chance to review it and make it readable.

NOTE: This command can take a somewhat considerable amount of time to run. I have seen it take upwards of 10+ minutes to complete when run during our non-backup window times. Be patient!