Andrew, Thanks. That fixed the issue.
-----Original Message----- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Raibeck Sent: Saturday, January 26, 2008 10:43 AM To: ADSM-L@VM.MARIST.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/[EMAIL PROTECTED] Internet e-mail: [EMAIL PROTECTED] 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@VM.MARIST.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.
