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@VM.MARIST.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:[EMAIL PROTECTED] On
> Behalf Of Robert Ouzen
> Sent: Friday, February 18, 2005 11:44 PM
> To: ADSM-L@VM.MARIST.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:[EMAIL PROTECTED] On
> Behalf Of Todd Lundstedt
> Sent: Saturday, February 19, 2005 12:00 AM
> To: ADSM-L@VM.MARIST.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
>
