Hi Rainer Just what I needed ... Thanks, Regards
Robert -----Original Message----- From: ADSM: Dist Stor Manager [mailto:[email protected]] On Behalf Of Rainer Sch?pf Sent: Friday, October 21, 2011 10:52 AM To: [email protected] 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: [email protected] 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
