Re: Strickly for old timers

Thanks John

Yes,
The object of the exercise is to scan the program stacks of all active jobs
and find whether PGM NMFCTLLST is active.

Thanks Peter, your SQL seems to work alright!

It only took 30 years to accomplish a basic S/36 OCL feature in OS/400.
Oh well....

Gad




date: Sun, 6 Aug 2023 16:02:42 -0400

from: John Yeung <gallium.arsenide@xxxxxxxxx>
subject: Re: Strickly for old timers

On Sun, Aug 6, 2023 at 4:00?AM Gad Miron <gadmiron@xxxxxxxxx> wrote:

I thought I'd combine the two to finally get my IF_ACTIVE.
So here is an SQL that I think should accomplish it but does not:

SELECT PROGRAM_NAME, PROGRAM_LIBRARY_NAME
FROM TABLE(QSYS2.STACK_INFO((SELECT distinct JOB_NAME
FROM TABLE(QSYS2.ACTIVE_JOB_INFO(JOB_NAME_FILTER => '*ALL'))) ,'ALL')) A
where PROGRAM_NAME = 'NMFCTLLST'

I get a "Result of SELECT more than one row"

Any ideas, anyone?

It looks like you want to check the stack of every active job. But the
STACK_INFO table function only returns the rows for one job. Where it
expects the job name, you can't just pass it a set of job names.

You have to retrieve the set of job names, and then for *each* of
those, call STACK_INFO.

John Y.




from: Peter Dow <petercdow@xxxxxxxxx>
subject: Re: Strickly for old timers

Something like this:

SELECT?aj.JOB_NAME,?si.PROGRAM_NAME,?si.PROGRAM_LIBRARY_NAME
FROM TABLE(QSYS2.ACTIVE_JOB_INFO(JOB_NAME_FILTER *=>* '*ALL'))?aj
JOIN TABLE(QSYS2.STACK_INFO(JOB_NAME *=>*?aj.JOB_NAME))?si
on?si.PROGRAM_NAME?= 'NMFCTLLST';


On 8/6/2023 1:02 PM, John Yeung wrote:

On Sun, Aug 6, 2023 at 4:00?AM Gad Miron<gadmiron@xxxxxxxxx> wrote:

I thought I'd combine the two to finally get my IF_ACTIVE.
So here is an SQL that I think should accomplish it but does not:

SELECT PROGRAM_NAME, PROGRAM_LIBRARY_NAME
FROM TABLE(QSYS2.STACK_INFO((SELECT distinct JOB_NAME
FROM TABLE(QSYS2.ACTIVE_JOB_INFO(JOB_NAME_FILTER => '*ALL'))) ,'ALL')) A
where PROGRAM_NAME = 'NMFCTLLST'

I get a "Result of SELECT more than one row"

Any ideas, anyone?

It looks like you want to check the stack of every active job. But the
STACK_INFO table function only returns the rows for one job. Where it
expects the job name, you can't just pass it a set of job names.

You have to retrieve the set of job names, and then for *each* of
those, call STACK_INFO.

John Y.