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
Todd Lundstedt
<Todd_Lundstedt@V
IA-CHRISTI.ORG> To
Sent by: "ADSM: ADSM-L AT VM.MARIST DOT EDU
Dist Stor cc
Manager"
<[email protected] Subject
.EDU> Re: Select for Tape Storage Pool
Report
02/21/2005 10:30
AM
Please respond to
"ADSM: Dist Stor
Manager"
<[email protected]
.EDU>
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
|
