Still fine tuning this SQL.
Need to add a break on the date, get rid of the comma for the time duration field, HHMMSS, and sum the time duration field, HHMMSS.
I'm not sure this is possible, since these are all substr.
Any thoughts from the group.
SELECT substr(FLASTR,69,8) as "Date" , substr(FLASTR,31,35) as
"Start of Job" , substr(FLASTR,78,11) as " Time" ,
substr(FLAend,58,29) as "End of Job" , (time(substr(flaend,79,8)) -
time(substr(flastr,81,8))) as " HHM MSS " ,
substr(flaend,89,5) as "Total CPU" 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' ORDER
BY substr(FLASTR,69,8)
"Date" "Start of Job" " Time" "End of Job" " HHM MSS" "Total CPU"
04/01/17 793376/FLACWILS/QPADEV003Z started at 08:56:27 ended on 04/01/17 at 17:30:14 83,347 6.299
04/01/17 793377/FLAAKHAL/QPADEV0040 started at 08:56:39 ended on 04/01/17 at 17:30:14 83,335 4.402
04/01/17 793387/FLAGBRIG/QPADEV004F started at 08:59:00 ended on 04/01/17 at 17:30:07 83,107 7.284
04/02/17 802259/FLAGBRIG/QPADEV0026 started at 08:55:06 ended on 04/02/17 at 17:30:36 83,530 5.250
04/02/17 802270/FLAWCOLL/QPADEV0028 started at 08:57:24 ended on 04/02/17 at 17:30:40 83,316 3.347
04/02/17 802289/FLAHDOUG/QPADEV0027 started at 09:00:09 ended on 04/02/17 at 17:30:42 83,033 3.832
Paul
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Steinmetz, Paul
Sent: Saturday, April 22, 2017 11:01 AM
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
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
--
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: "Steinmetz, Paul" <PSteinmetz@xxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
Date: 04/21/2017 11:48 AM
Subject: 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>
I need to find job start, job end, calculate total job time for a group of
users, TST*, for a selected date range.
From QHST, I was planning on dumping CPF1164 to a spoolfile/db file, then
select out the needed data.
Is there a better tool for this task?
Any thoughts from the group?
Thank You
_____
Paul Steinmetz
IBM i Systems Administrator
Pencor Services, Inc.
462 Delaware Ave
Palmerton Pa 18071
610-826-9117 work
610-826-9188 fax
610-349-0913 cell
610-377-6012 home
psteinmetz@xxxxxxxxxx
http://www.pencor.com/
midrange.com
