ADSM-L

Re: restore times

1999-03-18 04:13:30
Subject: Re: restore times
From: Herve Chibois <Herve.Chibois AT FR.ABNAMRO DOT COM>
Date: Thu, 18 Mar 1999 10:13:30 +0100
Hi  John

try to add this :
adsm> select NODE_NAME, COUNT(distinct NODE_NAME) From volumeusage
           where node_name='MELMARJO' GROUP BY NODE_NAME

Hope this helps
Rv




        sorensen @ storsol.com
        17/03/99 22:16
To: ADSM-L @ VM.MARIST.EDU
cc:  (bcc: Herve Chibois/FR/ABNAMRO/NL)
Subject: Re: restore times

I do have a comment on the SQL query you used to determine
how many volumes contain data for any client node.  It turns
out that you may be in much better shape than you think!
The problem with the query is that the number returned
in count(*) will count every individual filespace
from a client node as a separate row.

Here is an example from my server.  Client node MELMARJO is
a Windows 95 client.  It backs up 3 filespaces; APPS, USER
and WINSYS (I have removed some whitespace from the output
but the text is untouched).  This query returns all columns
defined for the table, so that the upcoming query returning
the count(*) can be interpreted.

adsm> SELECT * FROM VOLUMEUSAGE WHERE NODE_NAME='MELMARJO'

NODE_NAME     COPY_TYPE     FILESPACE_NAME   STGPOOL_NAME    VOLUME_NAME
----------   ----------     ---------------  --------------  -----------
MELMARJO         BACKUP     APPS             COPYPOOL        B0008
MELMARJO         BACKUP     APPS             COPYPOOL        B0008
MELMARJO         BACKUP     APPS             COPYPOOL        B0016
MELMARJO         BACKUP     APPS             TAPEPOOL        B0009
MELMARJO         BACKUP     APPS             TAPEPOOL        B0010
MELMARJO         BACKUP     APPS             TAPEPOOL        B0012
MELMARJO         BACKUP     USER             COPYPOOL        B0008
MELMARJO         BACKUP     USER             COPYPOOL        B0016
MELMARJO         BACKUP     USER             TAPEPOOL        B0009
MELMARJO         BACKUP     USER             TAPEPOOL        B0010
MELMARJO         BACKUP     USER             TAPEPOOL        B0012
MELMARJO         BACKUP     WINSYS           COPYPOOL        B0008
MELMARJO         BACKUP     WINSYS           COPYPOOL        B0016
MELMARJO         BACKUP     WINSYS           TAPEPOOL        B0009
MELMARJO         BACKUP     WINSYS           TAPEPOOL        B0010
MELMARJO         BACKUP     WINSYS           TAPEPOOL        B0012

As you can see, files from this client are stored on both TAPEPOOL
tapes (primary pool) and COPYPOOL tapes (a copy storage pool).
(This was noted in the original message; namely that primary and
"DRM" tapes will counted separately).

But if you read down the list of volume names, you will see that
there are only three tapes in TAPEPOOL and two tapes in COPYPOOL
which contain data from MELMARJO.  Therefore the total number of
tapes, both primary and copypool, containing data from MELMARJO
is five.  But, the simple SELECT query gives a count of 15,
because there are 15 rows of output in the query:

adsm> select NODE_NAME, COUNT(*) From volumeusage
           where node_name='MELMARJO' GROUP BY NODE_NAME

NODE_NAME  Unnamed[2]
---------  ---------
MELMARJO   15
MELMARJO   15


You can therefore get a pretty reasonable number for the tape
volume count by dividing the number of volumes returned from
this query by the number of filespaces being backed up for
a client.  But that number is likely to change drastically from
client to client, and it's only valid when a given clients
filespaces are stored on the same group of tapes.

I think to get a true count of the volumes where a
client has stored data, and (something I have tried and failed
to get) a list of WHICH volumes have data for all clients,
is going to take some kind of subquery.  Any SQL gurus
out there interested in trying to construct such a query?

John Sorensen
SSSI


<Prev in Thread] Current Thread [Next in Thread>