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
|
