The problem is that unless est_capacity_mb matches exactly between
multiple volumes, the results of the "from" will return a single record
from the VOLUMES table consisting of the est_capacity_mb field and the
count (which is 1). After the math is done on the column to compute the
ratio, you have the ratio plus the count of 1. If, after the math, more
than one record has a matching ratio and count, DISTINCT will show only
one of those records. So if two volumes have close (but not identical)
est_capacity_mb fields such that the ratios round to the same value (e.g.,
1.61 and 1.62 both round to 1.6) then you'll get a single output record
showing a ratio of 1.6 and a count of 1.
I am thinking that you'll need to just get the raw columnar data for each
volume record, then feed it into another program (e.g., Perl), to get the
calculations you want.
Best regards,
Andy
Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Product Development
Level 3 Team Lead
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: storman AT us.ibm DOT com
IBM Tivoli Storage Manager support web page:
http://www.ibm.com/software/sysmgmt/products/support/IBMTivoliStorageManager.html
The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.
"ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU> wrote on 06/02/2008
11:08:34 AM:
> I'm trying to get a handle around the compression ratio on our
> 3494 tapes. I'm trying to generate a table showing each
> compression ratio and the number of volumes of that ratio.
> All the tapes in the lib are 60gb. It should look simething
> like this . . .
>
> ratio count
> ----- -----
> 1.2 50
> 1.3 100
> 1.4 99
> (etc, etc, etc)
>
> I've tried all kinds of sql variations, but can't get what I want.
>
>
> This attempt gives one line per volume with a ratio and count of 1.
>
> dsmadmc -se=${i} -id=<id> -password=<pwd> -noc -tab <<EOD
> select -
> cast((est_capacity_mb / 600 / 100) as decimal(2,1)) as
compratio,
> -
> count(*) as count -
> from volumes -
> where status = 'FULL' -
> and stgpool_name like '%3494%' -
> group by est_capacity_mb
> EOD
>
>
> This attempt is close, but the counts are bad.
>
> dsmadmc -se=${i} -id=<id> -password=<pwd> -noc -tab <<EOD
> select -
> distinct cast((est_capacity_mb / 600 / 100) as decimal(2,1))
as
> compratio, -
> count(*) as count -
> from volumes -
> where status = 'FULL' -
> and stgpool_name like '%3494%' -
> group by est_capacity_mb
> EOD
>
> 0.8 1
> 0.9 1
> 0.9 2
> 1.0 1
> 1.1 1
> 1.2 1
> 1.3 1
> 1.4 1
> 1.5 1
> 1.6 1
> 1.7 1
> 1.8 1
> 1.9 1
> 2.0 1
> 2.1 1
> 2.2 1
> 2.3 1
> 2.4 1
> 2.7 1
> 2.8 1
> 2.9 1
> 3.0 1
> 3.1 1
>
>
> Any help is appreciated!!
>
> Rick
>
>
> -----------------------------------------
> The information contained in this message is intended only for the
> personal and confidential use of the recipient(s) named above. If
> the reader of this message is not the intended recipient or an
> agent responsible for delivering it to the intended recipient, you
> are hereby notified that you have received this document in error
> and that any review, dissemination, distribution, or copying of
> this message is strictly prohibited. If you have received this
> communication in error, please notify us immediately, and delete
> the original message.
|
