ADSM-L

Re: Select for Tape Storage Pool Report

2005-02-21 16:23:41
Subject: Re: Select for Tape Storage Pool Report
From: Todd Lundstedt <Todd_Lundstedt AT VIA-CHRISTI DOT ORG>
To: ADSM-L AT VM.MARIST DOT EDU
Date: Mon, 21 Feb 2005 15:23:28 -0600
Here's something funny tho...
Sometimes, when I run the script, I get a duplicate (sort of) entry in the 
output...

Stg Pool                    TB PctUtl   Logi Run?  Recl   Max VolUsed  Diff
---------------------- ------- ------ ------ ---- ----- ----- ------- -----
..snipped..
L3_PRI_LTO                6.70   57.4   99.6 NO     100    35      29     6
L3_PRI_LTO                6.70   57.4   99.6 NO     100    35       1    34
...chopped..

The run before this one showed the VolUsed column at 17 and 13 (still totaling 
30).  When I do a count of the volumes in that stgpool, it does come out to 30.
The run after this one showed only a single entry for L3_PRI_LTO, with 30 tapes 
used.
Odd behavior.. If anyone has a clue why, it would sure help satisfy my 
curiosity...
Thx
Todd


-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of 
Sung Y Lee
Sent: Monday, February 21, 2005 9:47 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: [ADSM-L] Select for Tape Storage Pool Report

Very nice
Even added the difference in counts.  This select is very nice indeed.
I think you over estimated this group about  93 seconds thing..
I tried to combine two selects for over an hour and gave up.

Sung Y. Lee

"ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU> wrote on 02/21/2005
10:30:39 AM:

> Thanks,
> Here is the script in it's final form if any one else wants it.
> Adjust your devclass and char/decimal output and column titles as
needed..
>
> select -
>   cast(a.stgpool_name as char(22)) as "Stg Pool              ", -
>   cast((a.est_capacity_mb/1024/1024) as dec(5,2)) as "TB", -
>   a.pct_utilized as "PctUtl", -
>   a.pct_logical as "Logi", -
>   cast(a.recl_running as char(4)) as "Run?", -
>   cast(a.reclaim as dec(3)) as "Recl", -
>   cast(a.maxscratch as dec(3)) as "Max", -
>   cast(count(*) as dec(3)) as "VolUsed", -
>   cast(a.maxscratch - count(*) as dec(3)) as "Diff" - from -
>   stgpools a, -
>   volumes b -
> where -
>   devclass in ('LTO','LTO2') and -
>   a.stgpool_name=b.stgpool_name -
> group by -
>   a.stgpool_name, -
>   a.est_capacity_mb, -
>   a.pct_utilized, -
>   a.pct_logical, -
>   a.recl_running, -
>   a.reclaim, -
>   a.maxscratch
>
> -----Original Message-----
> From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf 
> Of Robert Ouzen
> Sent: Friday, February 18, 2005 11:44 PM
> To: ADSM-L AT VM.MARIST DOT EDU
> Subject: Re: [ADSM-L] Select for Tape Storage Pool Report
>
> Hi Todd
>
> This Script will give you the number of volumes per stg and maxscratch 
> allocated
>
> select a.stgpool_name,a.maxscratch,count(*)  as "Number of Vols"
> from stgpools a, volumes b where a.stgpool_name = b.stgpool_name and 
> a.devclass = 'SCALARCLASS' group by a.stgpool_name,a.maxscratch
>
> Regards Robert Ouzen
> Haifa University
> Israel
>
> -----Original Message-----
> From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf 
> Of Todd Lundstedt
> Sent: Saturday, February 19, 2005 12:00 AM
> To: ADSM-L AT VM.MARIST DOT EDU
> Subject: Select for Tape Storage Pool Report
>
> I created this SQL select statement...
> select -
>   cast(stgpool_name as char(22)) as "Stg Pool              ", -
>   cast((est_capacity_mb/1024/1024) as decimal(5,2)) as "TB", -
>   pct_utilized as "Util", -
>   pct_logical as "Logi", -
>   cast(recl_running as char(4)) as "Run?", -
>   cast(reclaim as dec(3)) as "Recl", -
>   cast(maxscratch as dec(3)) as "Max" - from stgpools - where devclass 
> in ('LTO','LTO2')
>
> It outputs exactly what I think it should...
>
> Stg Pool                       TB      Util      Logi    Run?
> Recl      Max
> ----------------------    -------    ------    ------    ----
> -----    -----
> L1_CPY_DBVL_LTO2_OFF1       19.06      14.5     100.0    NO
> 100       55
> L1_CPY_DB_LTO_OFF1          18.18       0.8     100.0    NO
> 100      100
> L1_CPY_DB_LTO_ON             0.00       0.0     100.0    NO
> 100       25
> L1_CPY_DSKIMG_LTO2_OFF       0.00       0.0     100.0    NO
> 100       30
> L1_CPY_LTO2_OFF1            10.91       3.9      99.9    NO
> 100       30
> L1_CPY_LTO_OFF1              9.50      35.9      99.5    YES
> 60       50
> L1_CPY_LTO_ON                0.00       0.0     100.0    NO
> 100       20
> L1_PRI_DBVL_LTO2            16.65      16.6     100.0    NO
> 100       40
> L1_PRI_DB_LTO                5.43       2.7     100.0    NO
> 100       24
> L1_PRI_DSKIMG_LTO2           0.00       0.0     100.0    NO
> 60       10
> L1_PRI_LTO                   5.85      58.5      99.1    NO
> 100       29
> L1_PRI_LTO2                  3.00      14.4      99.9    NO
> 100        8
> L2_CPY_DB_LTO_OFF1           0.00       0.0     100.0    NO
> 100       20
> L2_CPY_DSKIMG_LTO2_OFF       0.00       0.0     100.0    NO
> 100       30
> L2_CPY_LTO_OFF1              5.07      22.2      99.8    NO
> 60       30
> L2_PRI_DB_LTO                0.00       0.0     100.0    NO
> 100        1
> L2_PRI_DSKIMG_LTO2           0.00       0.0     100.0    NO
> 60       10
> L2_PRI_LTO                   2.32      48.8      99.6    NO
> 100       15
> L3_CPY_DB_LTO_OFF1           3.69      15.7     100.0    YES
> 60       20
> L3_CPY_DOM_LTO_OFF1          4.54      11.1     100.0    YES
> 60       25
> L3_CPY_DSKIMG_LTO2_OFF       0.00       0.0     100.0    NO
> 100       30
> L3_CPY_LTO_OFF1              6.25      61.0      99.8    NO
> 60       35
> L3_CPY_MAIL_LTO_OFF1         3.56       5.7     100.0    NO
> 100       20
> L3_PRI_DB_LTO                2.64      22.1     100.0    NO
> 100       10
> L3_PRI_DOM_LTO               1.74      29.0     100.0    NO
> 100       10
> L3_PRI_DSKIMG_LTO2           0.00       0.0     100.0    NO
> 60       10
> L3_PRI_LTO                   5.68      67.3      99.6    NO
> 100       30
> L3_PRI_MAIL_LTO              1.77      17.0     100.0    NO
> 100       10
> LTOTAPE                     11.54      25.7      99.7    NO
> 60       50
> LTOTAPE_SQL                  1.81       3.6     100.0    NO
> 100       10
> OFFSITE                     19.16      15.8      99.9    YES
> 60      100
> SPACEMGPOOL                  0.00       0.0     100.0    NO
> 60        0
>
>
> But, I want to add yet one more column.. the count of volumes 
> currently assigned to that storage pool.  I can find that information 
> with the following select statement...
> select -
>   count(*) -
> from volumes -
> where stgpool_name='SOME_STGPOOL_NAME'
>
> or..
> select -
>   stgpool_name, -
>   count(*) -
> from volumes -
> group by stgpool_name
>
> I know I can select from multiple tables select a.col5, a.col20, b.
> col7 from table1 a, table2 b, blah blah..
>
> But I am unsure how to code the "count" portion and relate it to the 
> stgpool_name of that line...
>
> I also can't seem to get subselects to work.. I thought I had done 
> this before, but I am not sure on that one...
> select * from (select col1, col2, col3 from tableA)
>
> I bet one of YOU can do it in 93 seconds, or less...
> The clock is ticking..
> =)
> TIA
> Todd
>

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