This is not really possible without using subqueries which TSM does not
support. Just pipe it through perl, something like this:
#!/usr/bin/perl
use POSIX qw(floor);
my %tapes;
while (<STDIN>)
{
chomp;
(my $pct, my $pool) = split /\s+/, $_;
$tapes{$pool}{floor (($pct - .01) / 10)}++;
}
for my $pool (sort keys %tapes)
{
for my $pct (sort keys %{$tapes{$pool}})
{
print "$tapes{$pool}{$pct}\t" . $pct*10 ."-". ($pct*10+10) . "%\t$pool\n";
}
}
-km
On 29/07, Huebschman, George J. wrote:
> 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
>
> IMPORTANT: E-mail sent through the Internet is not secure. Legg Mason
> therefore recommends that you do not send any confidential or sensitive
> information to us via electronic mail, including social security numbers,
> account numbers, or personal identification numbers. Delivery, and or timely
> delivery of Internet mail is not guaranteed. Legg Mason therefore recommends
> that you do not send time sensitive
> or action-oriented messages to us via electronic mail.
>
> This message is intended for the addressee only and may contain privileged or
> confidential information. Unless you are the intended recipient, you may not
> use, copy or disclose to anyone any information contained in this message. If
> you have received this message in error, please notify the author by replying
> to this message and then kindly delete the message. Thank you.
|