Results 1 to 11 of 11
  1. #1
    Member
    Join Date
    Jul 2007
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Time Taken for back up

    Code:
    Hi i have a nice script that gives me start and stop times for all my nodes on back up.But i would like to add the time taken in as well. Can any one please add to this script to give me the desired result.
    
    SELECT entity AS "Node name", CAST(sum(bytes/1024/1024/1024)
                               AS decimal(8,2)) AS "GB xfer", 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 "end     
                               date", SUBSTR (CAST(max (end_time) AS char(29)),12,8) AS   
                               "end time"  FROM summary  WHERE activity='BACKUP' AND      
                               start_time> current_timestamp - 24 hours GROUP BY entity 
    
    thanks in advance
    Treds

  2. #2
    Senior Member
    Join Date
    Jul 2008
    Location
    Birmingham, UK
    Posts
    537
    Thanks
    0
    Thanked 5 Times in 4 Posts

    Default

    Hi

    Try adding this in somewhere

    substr(char(start_time-end_time,11),4, as Duration

    Cheers

  3. #3
    Senior Member
    Join Date
    Jul 2008
    Location
    Birmingham, UK
    Posts
    537
    Thanks
    0
    Thanked 5 Times in 4 Posts

    Default

    grrr it shouldn't have put a smiley in there...
    that should be

    , number eight ) as Duration

    Cheers

  4. #4
    Member
    Join Date
    Jul 2007
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default script

    Code:
    it fails
    
    tsm: TAG_TSM1>SELECT entity AS "Node name", CAST(sum(bytes/1024/1024/1024) AS decimal(8,2)) AS "GB xfer", SUBSTR (CAST(min (star
                  t_time) AS char(29)),1,10) AS "start date", SUBSTR (CAST(min (start_time) AS char(29)),12,8) AS "start time",  SUB
                  STR (CAST(max (end_time) AS char(29)),1,10) AS "end date", SUBSTR (CAST(max (end_time) AS char(29)),12,8) AS "end 
                  time"  FROM summary  WHERE activity='BACKUP' AND start_time> current_timestamp - 24 hours GROUP BY entity substr(c
                  har(start_time-end_time,11),4,8,) as Duration
    ANR2905E Unexpected SQL identifier token - 'SUBSTR'.
    
                                         |
             ............................V..................................
              - 24 hours GROUP BY entity substr(char(start_time-end_time,11)

  5. #5
    Senior Member toxy13's Avatar
    Join Date
    Sep 2003
    Location
    Leipzig
    Posts
    739
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    try:

    tsm: TSM_SERVER1>SELECT entity AS "Node name", CAST(sum(bytes/1024/1024/1024) AS decimal(8,2)) AS "GB xfer",(e
    nd_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 "end
    date", SUBSTR (CAST(max (end_time) AS char(29)),12,8 ) AS "end time" FROM summary WHERE activity='BACKUP' AND
    start_time> current_timestamp - 24 hours GROUP BY entity,start_time,end_time
    edit: typing errors ...

  6. #6
    Senior Member
    Join Date
    Jul 2008
    Location
    Birmingham, UK
    Posts
    537
    Thanks
    0
    Thanked 5 Times in 4 Posts

    Default

    put

    start_time> current_timestamp - 24 hours GROUP BY entity substr(char(start_time-end_time,11),4,8 ) as Duration

    before the "FROM SUMMARY" part of the sql statement

  7. #7
    Member
    Join Date
    Jul 2007
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default script

    Code:
    Sorry still getting error
    tsm: TAG_TSM1>SELECT entity AS "Node name", CAST(sum(bytes/1024/1024/1024) AS decimal(8,2)) AS "GB xfer", SUBSTR (CAST(min (start_time) AS char(29)),1,10) AS "start dat              e", SUBSTR (CAST(min (start_time) AS char(29)),12,8) AS "start time",  SUBSTR (CAST(max (end_time) AS char(29)),1,10) AS "end date", SUBSTR (CAST(max (end              _time) AS char(29)),12,8) AS "end time"  start_time> current_timestamp - 24 hours GROUP BY entity substr(char(start_time-end_time,11),4,8 ) as Duration FR              OM summary  WHERE activity='BACKUP' AND start_time> current_timestamp - 24 hours GROUP BY entity
    ANR2905E Unexpected SQL identifier token - 'START_TIME'.
    
                                       |
             ..........................V....................................
             (29)),12,8) AS "end time" start_time> current_timestamp - 24 ho
    
    Also this wont allow me to save in the scripts section on the TSM console getting this error
    Single and double quotation marks cannot be used in the same line of a scripted command. Use the continuation character (-) to split the command so that a line contains only single or double quotation marks.
    
    For example, split this line:
    
    select node_name as "Locked Node" from nodes where locked='YES'
    
    into these lines:
    
    select node_name as "Locked Node"-
    from nodes where locked='YES'
    
    Can you add this without going through console?

  8. #8
    Senior Member toxy13's Avatar
    Join Date
    Sep 2003
    Location
    Leipzig
    Posts
    739
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    SELECT entity AS "Node name", CAST(sum(bytes/1024/1024/1024) AS decimal(8,2)) AS "GB 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' AND -
    start_time> current_timestamp - 24 hours GROUP BY entity,start_time,end_time
    copy & paste this

  9. #9
    Member
    Join Date
    Jul 2007
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default script

    Code:
    Toxy
    thanks thats works great!! but i still cant save through the TSM console  get the error i posted how do i add to scripts manualy please
    Treds

  10. #10
    Member
    Join Date
    Jul 2007
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default script

    thanks worked it out

  11. #11
    Member
    Join Date
    Jun 2010
    Posts
    84
    Thanks
    2
    Thanked 1 Time in 1 Post

    Default

    Hello ,

    The statement output is for one node. it is raely work.

    select entity AS \"Node name\",CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS \" SIZE MB \",SUBSTR (CAST(min (start_time) AS char(29)),1,10) AS \"start date\", SUBSTR (CAST(min (start_time) AS char(29)),12, AS \"start time\", SUBSTR (CAST(max (end_time) AS char(29)),1,10) AS \"end date\", SUBSTR (CAST(max (end_time) AS char(29)),12, AS \"end time\" FROM summary WHERE (activity='BACKUP' AND start_time >= current_timestamp - 24 hours)and entity='NODENAMEXXX' group by entity

Similar Threads

  1. Point in time restore taking extremely long time to complete
    By mketcham in forum Restore / Recovery Discussion
    Replies: 3
    Last Post: 11-03-2008, 03:45 PM
  2. admin sched start time end time
    By influx in forum Scripting
    Replies: 16
    Last Post: 03-14-2008, 10:00 AM
  3. Does hsm only recall files back one at a time?
    By SANMAN in forum Hierarchical Storage Management
    Replies: 0
    Last Post: 03-31-2007, 05:11 PM
  4. Replies: 3
    Last Post: 05-08-2006, 01:03 PM
  5. Point in time restore bringing back directories and files that have been deleted
    By sean.m.stack in forum Backup / Archive Discussion
    Replies: 3
    Last Post: 06-03-2003, 10:35 AM

Posting Permissions

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