Time Taken for back up

[treds]

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

[ChrisRees]

Hi

Try adding this in somewhere

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

Cheers

[ChrisRees]

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

, number eight ) as Duration

Cheers

[treds]

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)

[toxy13]

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

[ChrisRees]

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

[treds]

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?

[toxy13]

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

[treds]

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

[treds]

thanks worked it out

[unpaired]

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