TSM 6.2 Query–Time Calculation

Apparently they redid a couple of things with IBM TSM 6.2 – which allowed a good half of a day to understand the DB2 query language and howto manipulate it.

Apart from the node-name (which should be fairly basic) – the following is the end-result for my finalized query which shows the elapsed time of a job (backups in this specific case) – and providing it in a D HH:MM: SS format. Its so much easier to read.

SELECT entity AS "Node name", 
CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS "MB xfer",
SUBSTR (CAST(min (start_time) AS char(29)),12,8) AS "start time", 
SUBSTR (CAST(max (end_time) AS char(29)),12,8) AS "end time",
DAY(end_time-start_time) || ' ' || HOUR(end_time-start_time) || ':' || MINUTE(end_time-start_time) || ':' || substr(DIGITS(SECOND(end_time-start_time))8,10) AS "ELAPSED TIME"
 FROM summary 
WHERE activity='BACKUP' 
AND start_time>=current_timestamp - 24 hours GROUP BY end_time, start_time,entity