Finally an Active Data Report

[chad_small]

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. 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 active_data_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!