Re: Calculating MaxScratch vs Allocate Volume

        OOOOH, I like that better than the other script I have. 

        Nice job. I wish my SQL skills were a little better. I'm in the
process of creating some reports for the NDMP backups (Last full, Last
Differential, # of differentials, Volume of data in differentials, etc)
and spend most of my time playing around with the SQL queries..

Ben


-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
Mueller, Ken
Sent: Friday, April 22, 2005 10:20 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: Calculating MaxScratch vs Allocate Volume

You should be able to just add another output column doing the math...
so for your query:

SELECT STGPOOLS.STGPOOL_NAME, STGPOOLS.MAXSCRATCH,
Count(STGPOOLS.MAXSCRATCH) as "Allocated_SCRATCH",
STGPOOLS.MAXSCRATCH-count(STGPOOLS.MAXSCRATCH) as "Remaining_SCRATCH"
FROM STGPOOLS STGPOOLS, VOLUMES VOLUMES WHERE VOLUMES.STGPOOL_NAME =
STGPOOLS.STGPOOL_NAME AND ((STGPOOLS.DEVCLASS='3592TAPE')) GROUP BY
STGPOOLS.STGPOOL_NAME, STGPOOLS.MAXSCRATCH

-Ken Mueller

-----Original Message-----
From: Hart, Charles [mailto:charles.hart AT MEDTRONIC DOT COM]
Sent: Friday, April 22, 2005 11:58 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Calculating MaxScratch vs Allocate Volume


When I run the following slect

SELECT STGPOOLS.STGPOOL_NAME, STGPOOLS.MAXSCRATCH,
Count(STGPOOLS.MAXSCRATCH) as "Allocated_SCRATCH" FROM STGPOOLS
STGPOOLS, VOLUMES VOLUMES WHERE VOLUMES.STGPOOL_NAME =
STGPOOLS.STGPOOL_NAME AND
((STGPOOLS.DEVCLASS='3592TAPE')) GROUP BY STGPOOLS.STGPOOL_NAME,
STGPOOLS.MAXSCRATCH

I get the following out put

Server STGPOOL_NAME                     MAXSCRATCH
Allocated_SCRATCH
 MSPSTG3 TAPE_APP_BACKUP                 20                     58
 MSPSTG3 TAPE_ARCHIVE                   40                      30
 MSPSTG3 TAPE_ARCHIVE_COPY              80                          39
 MSPSTG3 TAPE_BACKUP_COPY               250                       193
 MSPSTG3 TAPE_DB_BACKUP                 18                          18



My question is how do I subtract "Allocated_SCRATCH Col from the
MaxScratch Col so I can see what the Acutal Scratch Remaining is.  The
plan is to have this be out put to a txt file that can be picked up by
our monitoring system so we can auto genertae a help desk ticket.  I
have searched around (here and the web) and can't seem to find a good
example.

Any insight would be great!!!!!!