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
