Clarification:
The first (long) sentence of my earlier response(below) should read:
> 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 ***for each volume*** consisting of the
est_capacity_mb field and the
> count (which is 1).
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:42:31 AM:
> 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.
|
