ENTITY: STG_V0653 -> STG_T10K_C2
START_TIME: 2010-12-28 08:00:11.000000
END_TIME: 2010-12-28 22:39:29.000000
ELAPSED_TIME: 143918.000000
AFFECTED: 258
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
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 so I included it.
How does the change affect tsm manager?
ReplyDeleteHi Chad,
ReplyDeleteFor me, it's not working because the cast funtion converts into a left-justified chararcter string, without leading zeros. So the substr function is impossible to use..
See :
SCHEDULE_NAME ENTITY START_TIME END_TIME ELAPSED_TIME AFFECTED
------------------ ------------------ ------------------ ------------------ ------------- ------------------
MAINTENANCE DISKPOOL -> 2011-03-17 2011-03-17 00 00: : 23853
COPYPOOL 10:00:05.000000 10:06:52.000000
MAINTENANCE FILEPOOL -> 2011-03-17 2011-03-17 00 00:0 : 836636
COPYPOOL 10:00:05.000000 10:54:25.000000
The TSM server is 6.2.2 on Windows
Hi Chad,
ReplyDeleteI'm using the select from your post above :
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
Strange behavior...
DB2 is problematic. I had this script work on one server then another it didn't. I'll look to refine it.
ReplyDelete