Well, the sum() would be correct for the DB backup "attempts", however what is stored in the summary table has a different context depending on what the activity is. It isn't really documented as far as I know, but affected in the DB backup is probably pages backed up, whilst affected in the client backup records is probably number of files backed up. Failed for client backups is probably failed files, but for the DB backup this doesn't make any sense and this column is probably zero in all cases for DB backup. I think you'll have to treat different activities in different ways with the summary table.
-----Original Message----- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] Behalf Of goran Sent: 19 March 2004 16:27 To: [EMAIL PROTECTED] Subject: Re: SQL / big numbers ? 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" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED] Behalf Of goran Sent: 19 March 2004 15:59 To: [EMAIL PROTECTED] 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.
