Re: SQL-query for list of tapes with active-versions

The BACKUPS table contains the info which object is active and which is
inactive. The CONTENTS table knows where is stored each object.
If you dare you can use a select performing join between the two (but this
would mean to search almost the whole DB minus the archives).

VOLUMEUSAGE does not have any column to distinguish actives/inactives.
Subquery returns all filespaces (all contain at least one active file,
right) thus second select is functionally equivalent to first one with
more burden on the SQL engine.

Zlatko Krastev
IT Consultant






brian welsh <brianwelsh3 AT HOTMAIL DOT COM>
Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
14.03.2003 15:19
Please respond to "ADSM: Dist Stor Manager"


        To:     ADSM-L AT VM.MARIST DOT EDU
        cc:
        Subject:        SQL-query for list of tapes with active-versions


Hello,

I want to make a sql_query to find out the amount of tapes with
active-version, so I can find out how many tape mounts will be necessary
in
case of a full-system restore.

I tried the following query, but it counts all the tapes, so with active
and
inactive versions:
select node_name as System_name,count(DISTINCT volume_name) as Total_tapes
from volumeusage where stgpool_name in ('TAPEPL','TAPEPLA') AND node_name
like '$1%' group by node_name

I tried also the following query:
select node_name as System_name,count(DISTINCT volume_name) as Total_tapes
from volumeusage where filespace_name in (select filespace_name from
backups
where state='ACTIVE_VERSION' and node_name like '$1' AND stgpool_name in
('TAPEPL','TAPEPLA') AND node_name like '$1' group by node_name

But the last one also gave me the total amount of tapes, same result as
first query. Can someone give me a hint?

Or is there another way I can see which tapes are necessary for a
particular
node in case of a full-system restore?

Thanx,

Brian.