A query for db backup throughput rate

sra006

ADSM.ORG Member
Joined
Apr 21, 2005
Messages
40
Reaction score
1
Points
0
Website
http
PREDATAR Control23

I found the following query to check the db backup throughput
select activity, ((bytes/1048576)/cast((end_time - start_time) seconds as decimal(18,13))*3600) "MB/Hr" from summary where activity='FULL_DBBACKUP' and days(end_time) - days(start_time)=0

Under tsm v 6 I am getting

ANR0162W Supplemental database diagnostic information: -1:42911:-419 ([IBM][CLI Driver][DB2/LINUXX8664] SQL0419N A decimal divide operation is not valid because the result would

have a negative scale. SQLSTATE=42911

Any suggestion on either fixing it or another query to check the throughput will be appreciated.
Thank you
Al
 
PREDATAR Control23

You're missing an AS for your column alias:
Code:
select activity, ((bytes/1048576)/cast((end_time - start_time) seconds as decimal(18,13))*3600) as "MB/Hr" from summary where activity='FULL_DBBACKUP' and days(end_time) - days(start_time)=0
 
PREDATAR Control23

I tried , adding "as" with the same result
ANR0162W Supplemental database diagnostic information: -1:42911:-419 ([IBM][CLI Driver][DB2/LINUXX8664] SQL0419N A decimal divide operation is not valid because the result would have

a negative scale. SQLSTATE=42911

).

ANR0516E SQL processing for statement select activity , ( ( bytes/1048576 ) /cast ( SECOND ( end_time - start_time) as decimal ( 18 , 13 ) ) *3600 ) as "MB/Hr" from summary where

activity = 'FULL_DBBACKUP' and days ( end_time ) - days ( start_time ) = 0 failed.

Regards,
Al
 
PREDATAR Control23

the "as" is optional. try this.

Code:
select start_time, bytes/1024/1024 mb, bytes/timestampdiff(2,end_time-start_time)/1024/1024 mb_sec from summary where activity='FULL_DBBACKUP' and start_time>current_timestamp-1 day
 
PREDATAR Control23

Thank a lot !
It works now.
I can also vary the number of days to go back i.e
'select start_time, bytes/1024/1024 mb, bytes/timestampdiff(2,end_time-start_time)/1024/1024 mb_sec from summary where activity='FULL_DBBACKUP' and start_time>current_timestamp-2 day' 2 or any number of days to go back .
the output looks like this

START_TIME MB MB_SEC

--------------------------- --------------------- ---------------------

2015-10-22 05:00:16.000000 1428837 82


select start_time, bytes/1024/1024 mb, bytes/timestampdiff(2,end_time-start_time)/1024/1024 mb_sec from summary where activity='FULL_DBBACKUP' and start_time>current_timestamp-2 day


START_TIME MB MB_SEC

--------------------------- --------------------- ---------------------

2015-10-21 05:00:06.000000 1422919 95

2015-10-22 05:00:16.000000 1428837 82
 
Top