ENTITY: STG_V0653 -> STG_T10K_C2
START_TIME: 2010-12-28 08:00:11.000000
END_TIME: 2010-12-28 22:39:29.000000
GB BACKED UP: 8814.00
You'll notice that the value given is actually in HHMMSS without the colon seperators. So my select formats the output to a more time based solution. You might ask why I didn't use timestampdiff, but the problem with the timstampdiff format is that it does not return in a time format like previous (5.x) versions. The timestampdiff would return the format calculation of either days, hours, minutes, or seconds only. So I actually worked with a friend who has a little more experience with DB2 and we came up with the following.
select schedule_name, entity, start_time, end_time, substr(cast((end_time-start_time) as char(22)),7,2) || ' ' || substr(cast((end_time-start_time) as char(22)),9,2) || ':' || substr(cast((end_time-start_time) as char(22)),11,2) || ':' || substr(cast((end_time-start_time) as char(22)),13,2) AS ELAPSED_TIME, affected from summary where activity='STGPOOL BACKUP' and timestampdiff(8, char(current_timestamp-start_time))<=24
ENTITY: STG_655 -> STG_10_C2
START_TIME: 2010-12-28 08:00:26.000000
END_TIME: 2010-12-29 07:40:46.000000
ELAPSED_TIME: 00 23:40:20
So now it shows the elapsed time as Days, Hours:Minutes:Seconds
You could eliminate the Day by removing the first calculation, but I have some storage pool backup processes that can run VERY long so I included it.