I took Bill's query a little further, with this function it gives me the
number of primary pool volumes for a given node. I am working on being able
to run this for all nodes.

select count(distinct volume_name) from volumeusage where node_name='NODE'
and stgpool_name='POOL'

You could easily make this a script or macro by replacing NODE with $1 and
POOL with $2. As a script it would be run like this:
        Run scriptname NODENAME POOLNAME

Thank You,

Stefan Dusedau
infoWorks
A Viacom technology service
(212)258-6739
stefan.dusedau AT viacom DOT com <mailto:stefan.dusedau AT viacom DOT com>


                -----Original Message-----
                From:   John M. Sorensen [mailto:sorensen AT STORSOL DOT COM]
                Sent:   Wednesday, March 17, 1999 5:52 PM
                To:     ADSM-L AT VM.MARIST DOT EDU
                Subject:        Re: restore times

                Actually, the query I would like to see has output
                that looks like this (for example):

                NODE_NAME               Count of Volumes
                ---------               ----------------
                MELMARJO                3
                NODEA                   1
                NODEB                   32

                ... and so forth.

                On a server with a hundred or so clients, having
                output like this would enable me to quickly spot
                any node with a problem.

                Then, for a particular node, like NODEB with
                32 volumes, I would execute Bill's query to find
                WHICH volumes have the data, and move the data if
                I need to.

                -----Original Message-----
                From:   Bill Colwell [SMTP:bcolwell AT DRAPER DOT COM]
                Sent:   Wednesday, March 17, 1999 2:58 PM
                To:     ADSM-L AT VM.MARIST DOT EDU
                Subject:        Re: restore times

                A better select statement to list the volumes a node is on
without
                getting duplicates for each filespace is --

                /*                                              */
                /* macro file to select the volumes that a node */
                /* is stored on.                                */
                /*                                              */
                set sqldatetimeformat i
                set sqldisplaymode w
                set sqlmathmode r
                commit
                select distinct volume_name -
                   from adsm.volumeusage  -
                   where stgpool_name = '9840APOOL' -
                     and node_name = %1 -
                  > e:\adsmcv3\sqllists\voluse3

                To run this, store it as a fill in the directory where
dsmadmc is,
                and then enter "macro <filename> 'NODENAME_IN_CAPS' ".
                Then edit the output file \voluse3.  Adjust the redirection
name
                as necessary.


                To comment on the thread in general, a Consolidate command
would be
                a good thing to have.  I submitted a SHARE requirement for
one 4 years ago when
                I wasn't doing collocation.  I have since switched to
collocation so there are other
                thing I would like development to do first.

                I think there is a lot of misconceptions about collocation
and how to implement it.
                It will always buy you something on the restore time, but
you pay on the migration
                time.  You don't need 1 or more tapes for every node.  ADSM
will double up, triple up,
                etc.  as Wanda pointed out.

                --
                --------------------------
                Bill Colwell
                C. S. Draper Lab
                Cambridge, Ma.
                bcolwell AT draper DOT com
                --------------------------

                In <01BE707E.1928FF60.sorensen AT storsol DOT com>, on 03/17/99
                   at 01:57 PM, "John M. Sorensen" <sorensen AT STORSOL DOT COM>
said:

                >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'