1. Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING) Click the link to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This message will disappear after you have made at least 12 posts. Thank you for your cooperation.

Need help converting Select statement to useable TSM 6.2 format

Discussion in 'Scripting' started by pbrukner, Aug 14, 2012.

  1. pbrukner

    pbrukner New Member

    Joined:
    Aug 14, 2012
    Messages:
    3
    Likes Received:
    0
    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
     
  2.  
  3. chad_small

    chad_small Moderator

    Joined:
    Dec 17, 2002
    Messages:
    2,197
    Likes Received:
    43
    Occupation:
    AIX/SAN/TSM
    Location:
    Gilbert, AZ
    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!
     
  4. pbrukner

    pbrukner New Member

    Joined:
    Aug 14, 2012
    Messages:
    3
    Likes Received:
    0
    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
     
  5. rmazzon

    rmazzon Senior Member

    Joined:
    Dec 6, 2004
    Messages:
    206
    Likes Received:
    17
    Occupation:
    TSM Managed Services Lead
    Location:
    NC
    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
     
  6. pbrukner

    pbrukner New Member

    Joined:
    Aug 14, 2012
    Messages:
    3
    Likes Received:
    0
    Thanks for the reply, but I can't get that to work at all.
     

Share This Page