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/[EMAIL PROTECTED] Internet e-mail: [EMAIL PROTECTED] 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@VM.MARIST.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/[EMAIL PROTECTED] > Internet e-mail: [EMAIL PROTECTED] > > 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@VM.MARIST.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.