Need help converting Select statement to useable TSM 6.2 format

[pbrukner]

Hi,
Can anyone help me with this script? It worked well under 5.x but does not format the DURATION properly in TSM 6.2. (hours:minutes:seconds is whats required.) ANy help would be much appreciated. Thanks, PB

SELECT entity AS "Nodes", CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS "MB xfer", -
(end_time-start_time) as Duration,SUBSTR (CAST(min (start_time) AS char(29)),1,10) AS "start date", -
SUBSTR (CAST(min (start_time) AS char(29)),12,8 ) AS "start time", -
SUBSTR (CAST(max (end_time) AS char(29)),1,10) AS "enddate", SUBSTR (CAST(max (end_time) AS char(29)),12,8 ) -
AS "end time" FROM summary WHERE (activity='BACKUP' or activity='ARCHIVE') AND -
start_time >(timestamp '2012-08-07 17:00:00') AND end_time <(timestamp '2012-08-08 17:00:00') GROUP BY entity,start_time,end_time

[chad_small]

Welcome to 6.x! DB2 handles time differently and duration has to be either minutes or hours. There's no easy way to get the old output style. Trust me, tried!

[pbrukner]

thank you for the reply. How about getting it into a minute or hour format rather than the output produced by this select.(example below)

DURATION
2310.000000

Would like something like 23.1 minutes or something similar.
Thanks again

[rmazzon]

I don't have any TSM 6.x experience but I can offer something that I think could work in 6.x, albeit it is extremely ugly and convoluted, and perhaps not even worth the effort. It was tested out on 5.5 and does not depend on internal date/time conversions, instead it manipulates the timestamps very deliberately. And this example simply replaces the "(end_time-start_time) as Duration" portion of your original select. Also, it is not quite an exact match to the 5.5 default output.

select char(cast((end_time-start_time)days as integer),2) || ' ' || \
right('0' || trim(char(cast((end_time-start_time)hours as integer),2)),2) || ':' || \
right('0' || trim(char(cast((end_time-start_time)minutes as integer),2)),2) || ':' || \
right('0' || trim(char(mod(cast((end_time-start_time)seconds as integer),60),2)),2) from summary

Original as in 5.5
select end_time-start_time from summary
Unnamed[1]
------------------------
0 00:06:07.000000
0 00:06:05.000000

Using the select

Unnamed[1]
-----------
0 00:06:07
0 00:06:05

[pbrukner]

Thanks for the reply, but I can't get that to work at all.