Trevor,

Russell Street gave me the same idea, I've already made some scripts but I was 
hoping ADSM had a better way to get this kind of information.

Thanks a lot for your effort
Regards
Dick



Trevor Foley wrote:

> Hi Dick,
>
> My understanding is that when a file has been marked as inactive, ADSM makes 
> no differentiation as to why is was made inactive ie because a newer version 
> was backed up or the file was deleted. The split the two you would have to 
> work out, for every file that is inactive, where there was an active version 
> for the same file. If so, then it was deactiviated due to a new version being 
> backed up. If not, then it was deactivated due to it being deleted from the 
> client.
>
> Trevor
>
> -----Original Message-----
> From: Dick Nieuwenhuizen [mailto:dnieuwen AT SOLAIR1.INTER.NL DOT NET]
> Sent: Wednesday, April 07, 1999 8:49 PM
> To: ADSM-L AT VM.MARIST DOT EDU
> Subject: Re: SQL query ??
>
> Russell,
>
> Thank for your reply, but I still got not the information I need to satisfy my
> manager. He likes to know what impact it has when we decrease expiration 
> dates and
> number of versions. There must be some way to find out what inactive files 
> are deleted
> files. ADSM does.
>
> Regards
> Dick
>
> Russell Street wrote:
>
> > I do no think there is any easy way to do it directly using SQL.
> >
> > You could do it by using the the output of the commands
> >
> >         export server filedata=all preview=yes wait=yes
> > and
> >         export server filedata=activefiles preview=yes wait=yes
> >
> > or you could use export node domain=...
> >
> > The differences between the outputs of the two commands will be the
> > inactive/deleted file count.
> >
> > Yes these will take some time to run.
> >
> > The first one could be optimised to...
> >
> >   select sum(num_files), sum(logical_mb), sum(physical_mb) -
> >     from occupancy -
> >     where -
> >     stgpool_name in (select stgpool_name from stgpools where pooltype = 
> > 'PRIMARY')
> >
> > [You need to exclude the copy pools or you will double count.]
> >
> > There is no way to optimise the second.  A join on the contents and
> > backups table using ADSM SQL would be a bad thing.  Also, the files
> > are named different ways in the two tables.  May be you could export
> > the records to a real database, massage the fields and then do the
> > matching.
> >
> > Hope this is of some help
> >
> > Russell
> >
> > > Hi all,
> > >
> > > My management asked me too find out how much tapepool space is occupied
> > > by deleted and inactive files. I looked at the SQL Interface and tried
> > > some select commands, but I can't find how I can get the size of the
> > > files that are being backed up.
> > >
> > > Can someone help me with a SQL query that will display the size of
> > > deleted and inactive files, or any other solution to get this
> > > information.
> > >
> > > Thanks in advance
> > >
> > > Dick