Andrew,
Thanks. That fixed the issue.
-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
Andrew Raibeck
Sent: Saturday, January 26, 2008 10:43 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: [ADSM-L] Basic SQL debugging
You are on the right track. A couple of things to consider:
- The values you are operating on are integral values, so you get
integral results. In order to get fractional parts, you need to force
the math to floating point mode. This can be done by bringing an
otherwise ineffective floating point value into the mix. For example,
instead of doing this:
numscratchused / maxscratch
you can do this:
1.0 * numscratchused / maxscratch
by having the floating point value on the left, the rest of the math is
forced to floating point.
This will also work:
numscratchused / (1.0 * maxscratch)
but this will not work:
numscratchused / maxscratch * 1.0
This is because of operator precedence. In the first two cases, operator
precedence will force the result to floating point; but in the third
case, the integer division will occur first, yielding 0, which is then
multiplied by 1.0. The first case above is arguably the best way to go.
You will need to do the same in the WHERE clause where you test for a
value greater than 0.9.
- If you want a percentage, you need to multiply the results by 100. You
can take care of this and force floating point math thus:
100.0 * numscratchused/maxscratch
- Lastly, if you want to be fussy about the least significant digit, the
SQL math truncates rather than rounds. For example, "2.0 / 3" cast to
two decimal places yields 0.66 rather than 0.67. You can correct this
with the following command:
set sqlmathmode round
Note that this sets the SQLMATHMODE for that admin session only, so
every time you start a new admin session, you need to reissue the
command. For your purposes, that last decimal place is probably not a
big deal, but I figured I'd mention it anyway.
Putting it all together, try these commands:
set sqlmathmode round
select stgpool_name,
numscratchused as "Scratch Used",
maxscratch,
cast(100.0 * numscratchused/maxscratch as
decimal(5, 2)) as "PCT MaxScratch"
from stgpools
where numscratchused <> 0 and
maxscratch <> 0 and
1.0 * numscratchused/maxscratch>=0.9
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/IBMTivoliStorageMan
ager.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 01/25/2008
06:49:15 PM:
> Time for me to ask a stupid question.
> I have lots of them...
>
> TSM Server 5.4.3 OS is AIX 5.3
>
> I am trying to utilize the Hourly Monitor in Daily Operational
> Reporting to notify me if I reach 90% of maxscratch.
> Before I went to the reporting utility, I tried to write a select
query.
> Only pieces of it work. It seems to be rounding. Obviously I have an
> incomplete understanding of something I am trying to use.
> I have been nosing around the web and learned some things, but I have
> not answered my question.
>
> This is a partial query and the result:
>
> tsm: AIXPROD29>select stgpool_name,numscratchused as "Scratch
> Used",maxscratch,cast(numscratchused/maxscratch
> as decimal(3,2)) as "PCT MaxScratch" from stgpools where
> numscratchused<>0 and maxscratch<>0
>
> STGPOOL_NAME Scratch Used MAXSCRATCH PCT MaxScratch
> ------------------ ------------ ----------- --------------
> COPYTAPE 425 2000 0.00
> C_ARCHIVE_TAPE 3 10 0.00
> C_DRBACKUP_TAPE 240 1165 0.00
> OFFSITE 1432 10000 0.00
> ONSITE 4 48 0.00
> P_ARCHIVE_TAPE 3 10 0.00
> P_DRBACKUP_TAPE 229 300 0.00
> P_DRBACKUP_VTL 30 30 1.00
> P_NODR_TAPE 32 50 0.00
> P_NODR_VTL 14 20
0.00I
>
> It seems to convert anything less than a whole number to the next
> lower number
>
> The entire statement yields this result:
> tsm: AIXPROD29>select stgpool_name,numscratchused as "Scratch
> Used",maxscratch as "Scratch Used",cast(numscrat chused/maxscratch as
> decimal(3,2)) as "PCT MaxScratch" from stgpools where
> numscratchused<>0 and maxscratch<>0 and
> cast(numscratchused/maxscratch as decimal(3,2))> .90
>
> STGPOOL_NAME Scratch Used Scratch Used PCT
MaxScratch
> ------------------ ------------ ------------
--------------
> P_DRBACKUP_VTL 30 30
1.00
>
>
> If I deliberatly get the math wrong, I get this result:
>
> tsm: AIXPROD29>select stgpool_name,maxscratch, numscratchused as
> "Scratch Used",cast((maxscratch/numscratchuse
> d) as decimal(5,2)) as "PCT MaxScratch" from stgpools where
> numscratchused<>0 and maxscratch<>0
>
> STGPOOL_NAME MAXSCRATCH Scratch Used PCT MaxScratch
> ------------------ ----------- ------------ --------------
> COPYTAPE 2000 425 4.00
> C_ARCHIVE_TAPE 10 3 3.00
> C_DRBACKUP_TAPE 1165 240 4.00
> OFFSITE 10000 1432 6.00
> ONSITE 48 4 12.00
> P_ARCHIVE_TAPE 10 3 3.00
> P_DRBACKUP_TAPE 300 229 1.00
> P_DRBACKUP_VTL 30 30 1.00
> P_NODR_TAPE 50 32 1.00
> P_NODR_VTL 20 14 1.00
>
> I tried using "float" and got the same results.
>
> 2,000/425 is not 4.00, what do I not see?
>
> This may seem basic to you, but I was a printer for years. I have
> been paddling in the shallow end of TSM for 2+ years now. I have
> mostly been concerned with making my clients (that I can't touch) run.
>
> I visited Richard Sims Quick Facts, but did not see what I was looking
> for.
>
> ***People are going to ask*** "Why don't you just make maxscratch a
> huge number and go home to dinner?"
>
> Two answers:
> Someone else set the levels in a low to mid range. We don't currently
> collocate so I don't know why...which is the answer, I don't want to
> change what I don't fully understand.
>
> The second answer is... I have fallen and I can't get up. This is not
> working and I don't know why. I can't let go of it.
>
> 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.
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.
|
