Thanks Nick, that's what I was looking for.
        I'm not sure why I couldn't figure out that SQL query on my own....

        I'll now start to make the wild guesses management is looking for... 
<sigh>

Ben

-----Original Message-----
From: Nicholas Cassimatis [mailto:nickpc AT US.IBM DOT COM]
Sent: Wednesday, April 02, 2003 12:43 PM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: Sum of inactive versions


This request is a statistical nightmare - don't promise results to be
identical to what you come up with...

You're missing single quotes around 'INACTIVE_VERSION', so the statement
looks like:

select * from backups where node_name='TSMHOST6' and
filespace_name='/export/home' and state='INACTIVE_VERSION'

(Not sure why you were using "like" references instead of "=")

But the number of objects won't help you for how many tapes you'll save -
you need the average size of an object, too.  I can think of two ways to
guesstimate that value:

1.  For the average size of an object on a tape:

select avg(filesize) from contents where volume_name='XXXXXXX'

And do a random selection of volumes.

2.  Average size of an object for a particular node:

select sum(physical_mb)/sum(num_files) from occupancy where
node_name='NODENAME' and type='Bkup'

That, times the number of objects you think you can get rid of, is the
approximate amount of data space you'll get back.

And some more things to think about:

Not all objects will have the same number of inactive versions - some will
have 0, some will have your retain_extra +1 (depending on if expiration has
run or not).
TDP nodes won't be effected - the application on the client controls the
versions, not TSM.
Do you have archives?  They don't play by versions, either.

Have fun - I tend to cringe when I get projects like this one.

Nick Cassimatis
nickpc AT us.ibm DOT com

Think twice, type once.