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.

UPDATE: Weird Query Results From 6.1.4

So the problem has been identified and listed in an APAR. In the document it states that SELECT commands on multiple tables not working as expected on a 6.x server.  The table list is quite large and it looks like the problem is resolved in 6.1.5 and 6.2.2.  Which means I have no fix unless I upgrade the servers.

Weird Query Results From 6.1.4

So we have a couple servers we upgraded from 5.3 to 6.1.4.2 and since the upgrade the following select statement does not work. I posted it on ADSM.org and others report their 6.x servers run it without issue.  Here is the statement and my results:

select server_name, node_name, PLATFORM_NAME || ' ' || cast(CLIENT_OS_LEVEL as char(20)) as OS_Version, cast(client_version as char) || '.' || cast(client_release as char) || '.' || cast(client_level as char) || '.' || cast(client_sublevel as char(2)) as TSM_Version from nodes, status


here is the return code I get:


ANR2034E SELECT: No match found using this criteria.
ANS8001I Return code 11.

I run this through a while loop and append the data to an comma-delimited output file so I can pull it into a spreadsheet and organize clients by version. I can then more easily prioritize my client upgrades.  I really need this to work so I can sort and identify the clients location by server_name. Any ideas?