Need help with SQLRPGLE program to obtain active job details

[mgarczynski]

I have written a program using the new SQL based features to obtain status from the Active jobs on my system. My routine is to check for active subsystems before a shutdown. If they are found and active, I want to know if there are any jobs running in that subsystem as sometimes the shutdown, ends all jobs in the subsystem, but leaves the subsystem still running. I have written the code below. The check for active subsystems using the job_name_filter and $count works fine. If there are none found, the routine exists as I want. However, the second check never exits because the $count_jobs which I was expecting to be 0 also contains the subsystem jobs in the count. I guess there must be another value to select. Any feedback is appreciated.

$finish = 0;
//
// * Check for active SAP subsystems allowing 10 minutes to end.
dou $finish = 10;
//
// * check for any active SAP Subsystems.
exec sql
SELECT count(*) INTO :$count
FROM TABLE(QSYS2.ACTIVE_JOB_INFO(JOB_NAME_FILTER => '*SBS')) A WHERE
(SUBSYSTEM LIKE '%PRD' or SUBSYSTEM LIKE '%PIP');
//
//
// * if there are still active SAP subsystems, delay job another 120 seconds.
If sqlcod = 0;
if $count > 0;
//
// * If active SAP Subsystems, if no jobs, then proceed to end
exec sql
SELECT COUNT(*) INTO :$count_jobs
FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) A
WHERE (SUBSYSTEM LIKE '%PRD' or SUBSYSTEM LIKE '%PIP');
If sqlcod = 0;
// * if subsystems are active and vary check is 'C', exit and submit shutdown jobs.
if $count > 0 and $count_jobs = 0;
$vryyn = 'Y';
leave;

[Brian Rusch]

In your second SQL statement, change the WHERE clause to be WHERE (SUBSYSTEM LIKE '%PRD' or SUBSYSTEM LIKE '%PIP') and JOB_TYPE <> 'SBS'

[mgarczynski]

That was the missing piece. Thank you very much.