ok, thanks ... i replaced all "sum" with "count" and this is what i got ...
ACTIVITY Attempts Failed % Success
------------------ ----------- ----------- ----------
ARCHIVE 112 112 0.00
BACKUP 765 765 0.00
EXPIRATION 8 8 0.00
FULL_DBBACKUP 25 25 0.00
MIGRATION 78 78 0.00
RECLAMATION 270 270 0.00
RESTORE 70 70 0.00
RETRIEVE 2 2 0.00
STGPOOL BACKUP 47 47 0.00
TAPE MOUNT 720 720 0.00
ithe numbers are prob ok for last 7/8 days ... but something in sql is wrong
obviously. nah ? it's friday and the spring has come .... lets enjoy the
weekend and bother with this next week ...
have a nice weekend.
g.
----- Original Message -----
From: "P Baines" <paul.baines AT ECB DOT INT>
To: <ADSM-L AT VM.MARIST DOT EDU>
Sent: Friday, March 19, 2004 4:08 PM
Subject: Re: SQL / big numbers ?
Hello Goran,
you named the column "attempts" but it's actually called affected. The
number of attempts (for the db backup) would be a count() rather than a
sum() fuction. The number you summed probably refers to the number of
database pages backed up.
Paul.
-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU]On Behalf Of
goran
Sent: 19 March 2004 15:59
To: ADSM-L AT VM.MARIST DOT EDU
Subject: SQL / big numbers ?
hi all,
i have a script which shows some kinda percentage of weekly activity from
summary table but i'm confused by big numbers that come out, heres the sql >
select activity, cast(sum(affected) as integer)as "Attempts",
cast(sum(failed) as integer) as "Failed", cast((1-(cast(sum(failed) as
decimal(18,3))/sum(affected)))*100 as decimal(8,2)) as "% Success" from
summary where start_time>timestamp(current_date-8 day,'00:00:00') group by
activity
my output looks like that >
ACTIVITY Attempts Failed % Success
------------------ ----------- ----------- ----------
ARCHIVE 48918 0 100.00
BACKUP 1423353 55 99.99
EXPIRATION 1223316 0 100.00
FULL_DBBACKUP 51254126 0 100.00
MIGRATION 754404 0 100.00
RECLAMATION 349309 189 99.94
RESTORE 15325 0 100.00
RETRIEVE 2 0 100.00
STGPOOL BACKUP 649593 0 100.00
ANR2947E Division by zero was detected for operator '/'.
|
...............................V...............................
t(sum(failed) as decimal(18,3))/sum(affected)))*100 as decimal(
the error is okay. but for instance FULL_DBBACKUP to have 51254126 attempts
? hmmm .... surely the sql has to be rewritten ... huh ?
thanks on answers !
goran konjich
senior unix systems admin
vipnet
-----------------------------
TSM 5.2.2.2
AIX 5.2 ML02
LTO3584
2109 FC
Any e-mail message from the European Central Bank (ECB) is sent in good
faith but shall neither be binding nor construed as constituting a
commitment by the ECB except where provided for in a written agreement.
This e-mail is intended only for the use of the recipient(s) named above.
Any unauthorised disclosure, use or dissemination, either in whole or in
part, is prohibited.
If you have received this e-mail in error, please notify the sender
immediately via e-mail and delete this e-mail from your system.
|
