Results 1 to 5 of 5
  1. #1
    Newcomer
    Join Date
    Aug 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Need help converting Select statement to useable TSM 6.2 format

    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. #2
    Moderator chad_small's Avatar
    Join Date
    Dec 2002
    Location
    Gilbert, AZ
    Posts
    2,194
    Thanks
    1
    Thanked 26 Times in 25 Posts

    Default

    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!
    Chad Small
    IBM Certified Deployment Professional
    chadsmal@gmail.com
    http://www.tsmadmin.com

  3. #3
    Newcomer
    Join Date
    Aug 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    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

  4. #4
    Senior Member
    Join Date
    Dec 2004
    Location
    NC
    Posts
    200
    Thanks
    0
    Thanked 12 Times in 11 Posts

    Default

    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
    "If we knew what it was we were doing, it would not be called research, would it?" -- Albert Einstein

  5. #5
    Newcomer
    Join Date
    Aug 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Thanks for the reply, but I can't get that to work at all.

Similar Threads

  1. Need some help converting select to TSM 6.1
    By MrStormy in forum Scripting
    Replies: 0
    Last Post: 11-06-2009, 01:47 PM
  2. Possible bug with select statement on TSM 5.1.1?
    By oridium in forum TSM Reporting & Monitoring
    Replies: 10
    Last Post: 06-10-2009, 10:30 AM
  3. Need output of select statement in unicode format
    By raydifede in forum Scripting
    Replies: 1
    Last Post: 04-24-2008, 04:06 AM
  4. text format - select statement output
    By friesguy in forum Scripting
    Replies: 5
    Last Post: 10-15-2006, 11:09 PM
  5. TSM Database Select Statement
    By rtclouse in forum Scripting
    Replies: 0
    Last Post: 10-15-2004, 06:09 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •