Re: [ADSM-L] Help on Tsm 6 script

Hi Rainer
Just what I needed ...

Thanks, Regards

Robert

-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of 
Rainer Sch?pf
Sent: Friday, October 21, 2011 10:52 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: [ADSM-L] Help on Tsm 6 script

Hi Robert,

I believe the difference of two timestamps is given in seconds. To convert that 
to minutes or hours, you can use the timestampdiff function:

  
http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/admin/r0000861.htm

E.g.,

  select -
   timestampdiff(4,char(current_timestamp-start_time)) -
   from processes

gives you the elapsed time in minutes.

 Rainer


----------------------------------------------------------------
On Thu, 20 Oct 2011, Robert Ouzen wrote:

 > Hi to all
 >
 > This week I upgrade one  my Tsm server from V5.5.4.0 to V6.2.3.0 in Windows 
 > platform 2008R2, everything work fine ….
 >
 > Now trying to update some scripts by example this one (Report of backup / 
 > archive in specific period).
 >
 > set sqlmathmode round
 > set sqldatetimeformat eur
 > select entity as nodename, -
 >     date(start_time) as "Date (Y/M/D)", -
 >     time(start_time) as "Begin time", -
 >     time(end_time) as "End time", -
 >     cast(sum(affected) as varchar(10)) as "Num Obj", -
 > cast(sum(failed) as varchar(10)) as "Num Obj Failed", -
 >   case -
 >     when sum(bytes)>1073741824 then cast(sum(bytes)/1073741824 as 
 > varchar(10)) || ' Gb' -
 >     when sum(bytes)>1048576 then cast(sum(bytes)/1048576 as varchar(10)) || 
 > ' Mb' -
 >     when sum(bytes)>1024 then cast(sum(bytes)/1024 as varchar(10)) || ' Kb' -
 >     else cast(sum(bytes) as varchar(10)) -
 > end as "Bytes" -
 >   from summary -
 >   where (activity='BACKUP' or activity='ARCHIVE') and -
 >      start_time>=timestamp(current_date-$1 day,'10:00:00') and -
 >      start_time<=timestamp(current_date,'09:00:00') and -
 >      successful='YES' -
 >      and entity  like upper('%%$2%%') -
 >    group by entity,start_time,end_time
 >
 > How can I calculate the elapsed time between the begin and end time this 
 > line in my previous version work fine but here I got:  ELAPSED: 7.000000  >  
 > > Here the line I used:
 > substr(cast(end_time-start_time as varchar(17)),3,8) as elapsed, -  >  > By 
 > the way it is a possibility to got the output as a table now I got the 
 > output in few lines ?
 >
 > Regards  Robert
 > 

--------------------------------------------------------
ProteoSys AG
Carl-Zeiss-Straße 51
55129 Mainz

Dr. Rainer Schöpf
Leiter Software/Softwareentwicklung

Mail:   rainer.schoepf AT proteosys DOT com
Phone:  +49-(0)6131-50192-41
Fax:    +49-(0)6131-50192-11
WWW:    http://www.proteosys.com/
--------------------------------------------------------
ProteoSys AG - Carl-Zeiss-Str. 51 - D-55129 Mainz Amtsgericht Mainz HRB 7508 - 
USt.-Id Nr.: DE213940570
Vorstand: Helmut Matthies (Vorsitzender), Prof. Dr. André Schrattenholz 
Vorsitzender des Aufsichtsrates: Dr. Werner Zöllner