Re: Joblogs for jobs in jobq: Not sure this is the right group, but if not I am sure Mr Gibbs will point me to the right one.

On 4/18/2017 10:59 AM, Rob Berendt wrote:

While distracted I joined those two services together
SELECT X.JOB_NAME, msg_timestamp, msg_text
FROM TABLE(QSYS2.JOB_INFO(JOB_STATUS_FILTER => '*JOBQ',
JOB_USER_FILTER => '*ALL')) X
,lateral (select y.message_timestamp
from table(qsys2.joblog_info(x.job_name)) y
where y.message_text like '%ERPCSTROL1DARREN%') L1 (msg_timestamp)
,lateral (select y.message_text
from table(qsys2.joblog_info(x.job_name)) y
where y.message_text like '%ERPCSTROL1DARREN%') L2 (msg_text)
WHERE JOB_QUEUE_LIBRARY = 'ERPLXEC'
AND JOB_QUEUE_NAME = 'BPCSJOBQ'
;

Very nice example of LATERAL, thanks!
When I find myself doing these repetitive constants, I use a variable:

create variable buck.submitted_job_parm varchar(80);

set buck.submitted_job_parm = '%ALB170401ALB053O0000%';

SELECT ...
FROM ...
lateral
(select ...
from...
where y.message_text like buck.submitted_job_parm) L1 (msg_timestamp),
lateral
(select...
from...
where y.message_text like buck.submitted_job_parm) L2 (msg_text)
WHERE...