Hi to all
Need a little help with this script below , need to get the summary amount
backup result for the node .
Now I got a lot of entries from this script as:
Protect: POSTBACK>run q_obj_0 IHPPROD_ORA
Nodename Date (D/M/Y) Begin time End time
Elapsed time in Minutes Num Obj Num Obj Failed Bytes
----------------- ------------- ------------- -------------
------------------------ ----------- --------------- -------
IHPPROD_ORA 2017-07-18 10.00.20 10.04.21 4
1 0 5 Gb
IHPPROD_ORA 2017-07-18 10.00.23 10.05.14 4
1 0 6 Gb
IHPPROD_ORA 2017-07-18 10.05.23 10.06.22 0
1 0 822 Mb
IHPPROD_ORA 2017-07-18 10.00.22 10.07.00 6
1 0 10 Gb
IHPPROD_ORA 2017-07-18 10.07.06 10.08.59 1
1 0 3 Gb
IHPPROD_ORA 2017-07-18 10.06.29 10.09.26 2
1 0 4 Gb
IHPPROD_ORA 2017-07-18 10.00.19 10.11.17 10
1 0 17 Gb
IHPPROD_ORA 2017-07-18 10.04.29 10.11.23 6
1 0 9 Gb
IHPPROD_ORA 2017-07-18 10.09.10 10.12.01 2
1 0 4 Gb
IHPPROD_ORA 2017-07-18 10.09.36 10.12.33 2
1 0 4 Gb
IHPPROD_ORA 2017-07-18 10.11.21 10.13.47 2
1 0 4 Gb
Want to get:
Nodename Date (D/M/Y) Begin time End time
Elapsed time in Minutes Num Obj Num Obj Failed Bytes
----------------- ------------- -------------
------------- ------------------------ -----------
--------------- -------
IHPPROD_ORA 2017-07-18 10.00.20 10.13.47
39 11
0 66.8 Gb
The script is:
set sqlmathmode round
set sqldatetimeformat eur
select cast(entity as varchar(16)) as "Nodename", -
cast(date(start_time) as varchar(12)) as "Date (D/M/Y)", -
cast(time(start_time) as varchar(12)) as "Begin time", -
cast(time(end_time) as varchar(12)) as "End time", -
cast(timestampdiff(4,char(end_time-start_time)) as varchar(16)) as "Elapsed
time in Minutes" , -
cast(sum(affected) as varchar(10)) as "Num Obj", -
cast(sum(failed) as varchar(10)) as "Num Obj Failed", -
case -
when sum(bytes)>1073741824 then cast(sum(bytes)/1073741824 as varchar(10))
|| ' Gb' -
when sum(bytes)>1048576 then cast(sum(bytes)/1048576 as varchar(10)) || '
Mb' -
when sum(bytes)>1024 then cast(sum(bytes)/1024 as varchar(10)) || ' Kb' -
else cast(sum(bytes) as varchar(10)) -
end as "Bytes" -
from summary -
where (activity='BACKUP' or activity='ARCHIVE') and -
start_time>=timestamp(current_date,'07:00:00') and -
start_time<=timestamp(current_date,'23:59:00') and -
successful='YES' -
and entity like upper('%%$1%%') -
group by entity,start_time,end_time
T.I.A Best Regards
Robert
|
