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
