Friday, December 31, 2010

TSM 6.x Time Calculation

I use the summary table frequently and like to calculate the elapsed time by subtracting the END_TIME from the START_TIME. In TSM 5.x you could easily do this because the TSM DB was setup for returning a day/time when calculating those two fields, but TSM 6.x's DB2 database does not work the same way. When calculating the difference in 6.x the result looks like this:


      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.

7 comments:

  1. Ronnie Lupson1/26/11, 11:50 AM

    How does the change affect tsm manager?

    ReplyDelete
  2. Not sure, but since you designate which version of TSM you are accessing when defining them I would suppose it had modified select statements for 6.x.

    ReplyDelete
  3. Hi Chad,

    For 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

    ReplyDelete
  4. Post your select so it can be reviewed. Maybe someone has a fix.

    ReplyDelete
  5. Hi Chad,

    I'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...

    ReplyDelete
  6. DB2 is problematic. I had this script work on one server then another it didn't. I'll look to refine it.

    ReplyDelete
  7. Take a look at the timestampdiff-function in the Tivoli Storage Manager v6 .1 Technical Guide (select statements for time calculation)

    ReplyDelete