The request here was simple, are there active jobs that had objects in QRPLOBJ on the stack? The solution was a little tricky, because jobs can end in the middle of doing the analysis.

Are programs in QRPLOBJ being used.sql

--
-- Subject: Are we running with *PGMs or *SRVPGMs that reside within QRPLOBJ?
-- Author: Scott Forstie
-- Date  : February, 2023
-- Features Used : This Gist uses qsys2.stack_info, CTEs, PIPE, SQL PL
--
-- Notes:
-- ===============================================
-- 1) Programs and Serice Programs get moved to QRPLOBJ when they are (re)created with replace(*YES).
-- 2) It is not safe to delete *PGMs or *SRVPGMs from QRPLOBJ, if you base your decision upon locks.
-- 3) QRPLOBJ resources:
--    https://www.ibm.com/support/pages/cleaning-qrplobj-library
--    https://www.ibm.com/support/pages/qrplobj-library 
--    https://www.ibm.com/support/pages/node/639147
-- 4) The following bit of SQL will check every stack in every active job, looking for PGMs or SRVPGMs that are located in QRPLOBJ.
--    Keep in mind that the SQL may take long enough to run that someone might have replaced a program during the query execution.
--
stop;
--  category:  Db2 for i Services
--  description: Look for QRPLOBJ on any stack in every active job
--
with all_JOBS (JOBNAME) as (
    select job_name
      from table (
          qsys2.active_job_info()
        )
  )
  select JOBNAME, program_library_name, program_name, module_name
    from all_JOBS, lateral (
           select *
             from table (
                 qsys2.stack_info(JOBNAME, thread_id => 'ALL')
               ) j
             where program_library_name = 'QRPLOBJ'
             order by ordinal_position desc
         ) x
    group by JOBNAME, program_library_name, program_name, module_name;
stop;
--
-- But, this query can fail if jobs are coming and going rapidly
--
--
-- For all active jobs, find any job that has a stack that includes a program
-- that resides within QRPLOBJ
--
create or replace function systools.find_QRPLOBJ_ON_stacks ()
  returns table (
    job_name varchar(28) for sbcs data, lib_name varchar(10) for sbcs data,
    pgm_name varchar(10) for sbcs data, mod_name varchar(10) for sbcs data
  )
  not deterministic
  external action
  modifies sql data
  not fenced
  set option COMMIT = *NONE
  begin
    declare local_sqlcode integer;
    declare local_sqlstate char(5);
    declare not_found condition for '02000';
    declare at_end integer default 0;
    declare at_end2 integer default 0;
    declare v_error integer default 0;
    declare v_message_text varchar(70);
    declare v_job_name varchar(28) for sbcs data;
    declare v_lib_name varchar(10) for sbcs data;
    declare v_pgm_name varchar(10) for sbcs data;
    declare v_mod_name varchar(10) for sbcs data;
    declare v_dsppgmref_command varchar(1000) for sbcs data;
    declare v_dsppgmref_CALL_count integer;
    declare find_stacks_stmt_text varchar(1000) for sbcs data default
    'select program_library_name, program_name, module_name
        from table (
            qsys2.stack_info(job_name => ?, thread_id => ''ALL'')
          )
        where program_library_name = ''QRPLOBJ''
        group by program_library_name, program_name, module_name';
    declare c_find_ajs cursor for
      select job_name
        from table (
            qsys2.active_job_info()
          );
    declare c_find_stacks cursor for find_stacks_stmt;
    declare continue handler for SQLEXCEPTION
    begin
      get diagnostics condition 1
          local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate;
      set v_message_text = 'systools.find_QRPLOBJ_ON_stacks() failed with: ' concat local_sqlcode
            concat '  AND ' concat local_sqlstate;
      signal sqlstate 'QPC01'
        set message_text = v_message_text;
    end;
    declare continue handler for not_found set at_end = 1;
    open c_find_ajs;
    set at_end = 0;
    fetch from c_find_ajs
      into v_job_name;
    prepare find_stacks_stmt from find_stacks_stmt_text;
    while (at_end = 0) do
      begin
         declare continue handler for not_found set at_end2 = 1;
        open c_find_stacks using v_job_name;
        call systools.lprintf('open for job: ' concat v_job_name);
        set at_end2 = 0;
        set v_error = 0;
        begin
        declare continue handler for sqlexception set v_error = 1;
        fetch from c_find_stacks
          into v_lib_name, v_pgm_name, v_mod_name;
        end;
        -- call systools.lprintf('after fetch ' );
        while (v_error = 0 and at_end2 = 0) do
          pipe (v_job_name, v_lib_name, v_pgm_name, v_mod_name);
          fetch from c_find_stacks
            into v_lib_name, v_pgm_name, v_mod_name;
          -- call systools.lprintf('after fetch in loop' );
        end while;
        close c_find_stacks;
      end;
      set at_end = 0;
      fetch from c_find_ajs
        into v_job_name;
    end while;
    close c_find_ajs;
    return;
  end;
stop;
 
SELECT *
   FROM TABLE (
         systools.find_QRPLOBJ_ON_stacks( )) ;  
stop;
-- for testing  
create procedure qrplobj.doit1()
begin
call qrplobj.doit2();
end;

create procedure qrplobj.doit2()
begin
call qsys2.qcmdexc('dlyjob dly(400)');
end;

create procedure qrplobj.doit()
begin
call qsys2.qcmdexc('dlyjob dly(400)');
end;

call qrplobj.doit();
call qrplobj.doit1();