For start of job -
DSPLOG OUTPUT(*PRINT) MSGID(CPF1124)
CRTPF FLASTR
CPYSPLF QPDSPLOG FLASTR
For end of job -
DSPLOG OUTPUT(*PRINT) MSGID(CPF1164)
CRTPF FLAEND
CPYSPLF QPDSPLOG FLAEND
SELECT substr(FLASTR,31,58) , substr(FLAend,58,30) ,
time(substr(flaend,79,8)) -
time(substr(flastr,81,8))
FROM qgpl.flastr
JOIN qgpl.flaend on substr(FLASTR,31,26) = substr(flaend,31,26)
where substr(FLASTR,38,3) = 'FLA'
and substr(FLASTR,47,4) = 'QPAD'
SUBSTR SUBSTR Numeric Expression
793387/FLAGBRIG/QPADEV004F started on 04/01/17 at 08:59:00 ended on 04/01/17 at 17:30:07; 83,107
793377/FLAAKHAL/QPADEV0040 started on 04/01/17 at 08:56:39 ended on 04/01/17 at 17:30:14; 83,335
793376/FLACWILS/QPADEV003Z started on 04/01/17 at 08:56:27 ended on 04/01/17 at 17:30:14; 83,347
794215/FLASBJOR/QPADEV005J started on 04/01/17 at 10:28:44 ended on 04/01/17 at 17:30:57; 70,213
794268/FLADWILL/QPADEV005S started on 04/01/17 at 10:33:22 ended on 04/01/17 at 18:58:58; 82,536
Can the difference be shown in hours, minutes, seconds?
I tried using timestampdiff, not a valid command.
Paul
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Friday, April 21, 2017 12:28 PM
To: Midrange Systems Technical Discussion
Subject: Re: Need to find job start, job end, calculate total job time for a group of users, TST*, for a selected date range
select x.message_timestamp as start_time,
y.message_timestamp as end_time,
y.message_timestamp - x.message_timestamp as job_duration,
x.from_job, y.from_job
from table(qsys2.history_log_info(current date - 2 days, timestamp((current date - 1 days)) - 1 seconds)) x full outer join table(qsys2.history_log_info(current date - 2 days, timestamp((current date - 1 days)) - 1 seconds)) y using (from_job) where x.message_id = 'CPF1124' and y.message_id = 'CPF1164';
There are rare occasions where the end time is before the start type, by milliseconds.
I'll leave it up to you to convert this decimal
y.message_timestamp - x.message_timestamp as job_duration, to a time or timestamp field.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: Rob Berendt <rob@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 04/21/2017 11:57 AM
Subject: Re: Need to find job start, job end, calculate total
job time for a group of users, TST*, for a selected date range
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Yes. Upgrade to a more recent level of the OS and then look at the
"Message Handling Services" at
http://ibm.biz/DB2foriServices
Rob Berendt
midrange.com
