Who is locking my database resources

job_is_waiting_for.sql

-- Returns the job name that is locking resources for another job
--
-- Simply paste this gist into ACS SQL and step through the example.
--
-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
--
-- It is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2021
--
-- This gist is distributed on an "as is" basis, without warranties 
-- or conditions of any kind, either express or implied.
----------------------------------------------------------------------------------------------
create or replace function qusrsys.job_is_waiting_for (
    job_id varchar(26) default '*'
)
returns varchar (1024)
begin  

    return  
        with job_is_waiting_for as (
            select *
            from qsys2.record_lock_info
            where  (table_schema , table_name) in (  
                select object_library, object_name  
                from table(qsys2.job_lock_info(job_id))
                where object_type = '*FILE'
            )
            and lock_status = 'WAITING'
            and job_name = job_id
        ),
        who_is_it_waiting_for as (
            select *
            from qsys2.record_lock_info
            left join lateral (
                select *
                from table(qsys2.get_job_info(job_name))
            ) on 1=1  
            where  (table_schema , table_name) in (
                select table_schema, table_name  
                from job_is_waiting_for
            )
            and job_name <> job_id
        )
        select
          'Your application is waiting for user '
          concat rtrim(v_authorization_name)
          concat ' job ' concat  job_name
          concat ' to release lock on table '  
          concat rtrim(table_schema) concat '/' concat rtrim(table_name)
        from who_is_it_waiting_for
        limit  1;
end;    

-- Test case;
values (
    qusrsys.job_is_waiting_for ( job_id => '906699/NLI/QPADEV0004')
); 
 
-- Will generate something like:
-- "Your application is waiting for user NLI job 906699/NLI/QPADEV0004 to release lock on table MYDB/TEST"