Hi George

I have a copy of the interesting bits of a TSM database in sqlite3 for a
side project I'm doing while "resting".  On that database I can use

select count(*), cast(abs(pct_utilized-.01)/10 as integer) as decile,
stgpool_name
from volumes
where devclass_name != 'DISK'
group by stgpool_name, cast(abs(pct_utilized-.01)/10 as integer)

to give something like what you are looking for.  The main issue is that
deciles with no members are not listed.  You may need to adjust the SQL
for the TSM database engine, but I can't see anything that I know isn't
valid TSM sql.

Regards

Steve

Steven Harris
TSM Admin, "resting" in Sydney Australia

Huebschman, George J. wrote:

No, just the command line

-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
Lindsay Morris
Sent: Wednesday, July 29, 2009 4:40 PM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: [ADSM-L] Summarizing Tape Utilization

George, aren't you a Servergraph user? Doesn't it give you what you
need?   I don't know what you're trying to accomplish here, but
Servergraph handles the usual culprits...

--------------------
Lindsay Morris
Principal
TSMworks, Inc.
1-919-403-8260
www.tsmworks.com

On Jul 29, 2009, at 2:52 PM, Bob Levad <blevad AT WINNEBAGOIND DOT COM> wrote:

I think you'll need a separate query for each utilization range.
At least, I haven't thought of a good way to iterate.



select stgpool_name, count(*) as "60% < utilized < 70%" - from volumes

- where devclass_name='LTOCLASS4' -
  and pct_utilized>=60 -
  and pct_utilized<70 -
group by stgpool_name -
order by stgpool_name

Etc...


You could maybe nest several of these selects inside another select,
but that can be pretty cumbersome.

Bob





-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf
Of Huebschman, George J.
Sent: Wednesday, July 29, 2009 10:59 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: [ADSM-L] Summarizing Tape Utilization

Greetings everyone,
I have a/an SQL Select question.

Most of my TSM Servers are at 5.5.1.0, one is at 5.5.2.0

I am trying to count the number of tapes with a percentage utilization

in brackets of 10 percent.  In other words, how many tapes with
utilization between 100 and 90, 90 and 80, and so forth.

I first tried:
select count(volume_name), pct_utilized, stgpool_name from volumes -
where (pct_utilized between 100 and 90) or (pct_utilized between 90
and
80) or (pct_utilized between 70 and 60) or (pct_utilized between 60
and
50) or (pct_utilized>50)-
group by stgpool_name, pct_utilized

Although the statement functions and returns valid data,  it is not
what I expected.  I mistakenly expected it to count all the tapes
within each given range.  What it really does is count tapes with
distinct pct_util.
I might as well not have specified the ranges.  The only tapes it
counted cumulatively were of the exact same pct_util.

Unnamed[1]     PCT_UTILIZED     STGPOOL_NAME
-----------     ------------     ------------------
         1             59.4     C_TSMSERVER_TAPE
         1             68.7     C_TSMSERVER_TAPE
         1             78.2     C_TSMSERVER_TAPE
         1             79.5     C_TSMSERVER_TAPE
         1             99.9     C_TSMSERVER_TAPE
         1            100.0     C_TSMSERVER_TAPE
         1             66.0     NASPOOL

If I select for a count for greater than or less than a particular
value, I get the kind of count I expect.  I had expected "between" to
do similar work but be less klunky.

select count(volume_name) from volumes where pct_utilized<100 and
pct_utilized>89


The other option I tried was CASE, WHEN, THEN:

select count(case when pct_utilized between 100 and 91 then 1 else
0) from
volumes and, select count(case when (pct_utilized between 100 and
91) then 1
else 0) from volumes

Those failed for syntax errors.

Is there a clean way to do this?

George Huebschman