Help with 6.1

[chad_small]

I'm trying to convert the following select to work in TSM 6.1

select entity, CAST((START_TIME)AS DATE) AS "DATE", CAST((START_TIME)AS TIME) AS "START TIME", CAST((END_TIME) AS TIME) AS "END TIME", cast(SUBSTR(CHAR(START_TIME-END_TIME),3,9)as time) AS "ELAPSED TIME", affected AS Files_Copied, cast((bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP",successful from summary where cast((current_timestamp-start_time)hours as decimal)<24 and upper(activity)='STGPOOL BACKUP'

I have the following and the issue seems to be what is in bold:

select entity, CAST((START_TIME)AS DATE) AS "DATE", CAST((START_TIME)AS TIME) AS "START TIME", CAST((END_TIME) AS TIME) AS "END TIME", cast(SUBSTR(CHAR(START_TIME-END_TIME),3,9)as time) AS "ELAPSED TIME", affected AS Files_Copied, cast((bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP",successful from summary where timestampdiff(8, char(current_timestamp-end_time))<=24 and upper(activity)='STGPOOL BACKUP'

Anyone have an example on how I calculate the elapsed time in 6.1?

 

[nkovacs]

Use the timestampdiff( ) function to calculate the elapsed time like you have later on in your query. Here's an article that walks through a bunch of different time and date calculation functions.

http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html

So for example if you want the "Elapsed Time" to be in minutes then you would have the following for that column:

timestampdiff(4, char( END_TIME-START_TIME ) ) as "ELAPSED TIME"

Or if you put it together it'd be:

select entity, CAST((START_TIME)AS DATE) AS "DATE", CAST((START_TIME)AS TIME) AS "START TIME", CAST((END_TIME) AS TIME) AS "END TIME", timestampdiff(4, char( END_TIME-START_TIME ) ) as "ELAPSED TIME", affected AS Files_Copied, cast((bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP",successful from summary where timestampdiff(8, char(current_timestamp-end_time))<=24 and upper(activity)='STGPOOL BACKUP'

 

[chad_small]

The problem with the timstampdiff format is that it does not return in a time format like previous (5.x) versions did. 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

Results:

SCHEDULE_NAME: AS_BACK_V655
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
AFFECTED: 632

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.