Rob,
A few more follow-up items on this.
1) My time logic did not handle jobs crossing midnight. In my selection I did not have any, but other selections will. To solve that issue I probably should use timestamp instead of time. Timestamp would include the date, which in turn I should then be able to use Timestampdiff.
2) I was unsuccessful in getting a break by date. Tried all combinations Group Sets, Rollup, Cube, etc.
3) I took the group's suggestions, split this into two parts. Outputted the SQL to a file.
4) Then used Query400 for the reporting, which easily handled the date break, time formatting and summing. (time summing has issues)
5) I did find an issue with Query400 time subtotals and grand totals. Subtotal minutes incorrect, GT 59. Subtotal and Grand total dropping the hours over 99. I increased the field size from 6 to 10, no impact.
SELECT substr(FLASTR,69,8) as Date ,
substr(FLASTR,31,34) as Job , substr(FLASTR,81,8) as Start
, substr(FLAend,58,29) as End_of_Job ,
(time(substr(flaend,79,8)) - time(substr(flastr,81,8))) as HMMSS
, 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'
GROUP BY substr(FLASTR,69,8),
substr(FLASTR,31,34) ,
substr(FLASTR,81,8) , substr(FLAend,58,29) ,
(time(substr(flaend,79,8)) - time(substr(flastr,81,8)))
, substr(flaend,89,5)
ORDER BY substr(FLASTR,69,8)
04/26/17 10:20:22 Call Center Summary PAGE 1
DATE JOB START END_OF_JOB HHMMSS TOTAL_CPU
04/01/17 793376/FLACWILS/QPADEV003Z started 08:56:27 ended on 04/01/17 at 17:30:14 8:33:47 6.299
793377/FLAAKHAL/QPADEV0040 started 08:56:39 ended on 04/01/17 at 17:30:14 8:33:35 4.402
793387/FLAGBRIG/QPADEV004F started 08:59:00 ended on 04/01/17 at 17:30:07 8:31:07 7.284
794176/FLAHDOUG/QPADEV005N started 10:26:31 ended on 04/01/17 at 19:00:10 8:33:39 4.881
794215/FLASBJOR/QPADEV005J started 10:28:44 ended on 04/01/17 at 17:30:57 7:02:13 6.259
794218/FLADGONG/QPADEV005Q started 10:29:25 ended on 04/01/17 at 19:00:07 8:30:42 4.477
794232/FLACELGH/QPADEV005M started 10:31:17 ended on 04/01/17 at 19:00:05 8:28:48 3.729
794268/FLADWILL/QPADEV005S started 10:33:22 ended on 04/01/17 at 18:58:58 8:25:36 3.792
794556/FLAASTEW/QPADEV005X started 11:04:28 ended on 04/01/17 at 19:00:09 7:55:41 5.112
TOTAL 72:73:08
04/02/17 802259/FLAGBRIG/QPADEV0026 started 08:55:06 ended on 04/02/17 at 17:30:36 8:35:30 5.250
802270/FLAWCOLL/QPADEV0028 started 08:57:24 ended on 04/02/17 at 17:30:40 8:33:16 3.347
802289/FLAHDOUG/QPADEV0027 started 09:00:09 ended on 04/02/17 at 17:30:42 8:30:33 3.832
803265/FLADWILL/QPADEV002N started 10:32:00 ended on 04/02/17 at 18:59:23 8:27:23 2.681
803271/FLASBJOR/QPADEV002L started 10:33:15 ended on 04/02/17 at 12:59:34 2:26:19 2.514
803339/FLAKISAA/QPADEV0020 started 10:42:38 ended on 04/02/17 at 19:02:10 8:19:32 3.609
804158/FLASBJOR/QPADEV003C started 12:55:51 ended on 04/02/17 at 19:00:04 6:04:13 3.375
FINAL TOTALS
TOTAL 91:34:17
Paul
-----Original Message-----
From: Steinmetz, Paul
Sent: Tuesday, April 25, 2017 4:18 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
Rob,
This is one off, will only need for a few weeks.
The assumptions are valid, all our user profiles are 8 characters, QPADEV* are the virtual deices.
Hope to be on V7R3 by end of 3rd quarter, tasks like these should be simpler.
Paul
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Tuesday, April 25, 2017 2:52 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
I think you're making some bad assumptions here.
One, will the job users always be 8 characters? FLA* Two, will the device names always be 10 characters? QPADEV* Three, will IBM never change the layout of this printout at a release, ptf, etc, level?
I believe the standard clause of the MTU applies.
<snip>
Output(*PRINT) changes
Release to release Output(*PRINT) considerations Applications specifying OUTPUT(*PRINT) to generate spooled output from commands must be able to tolerate changes to the record layout in the spooled file. From release to release, commands that support that option can add, change, or remove records from the output. Any application that has a dependency on the record layout for a specific command might have to change.
</snip>
I have been burned by this before. And, it's a real hoot when you support multiple versions of the OS.
If you must code this at 7.1, prior to QSYS2.HISTORY_LOG_INFO(), then you might be best to use the APIs.
Birgitta gives a sample of coding an API to make a UDTF out of it.
https://www.ibm.com/developerworks/ibmi/library/i-power-of-udtf/
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/25/2017 02:26 PM
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>
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
