What resource are this job waiting for

Gents, Ladies;

I have a situation where I need to find what causes a certain job to go
into lock wait - It can be record locks, object locks etc.

I have written the following SQL stored procedure ( that only works for
record locks btw) - however my SQL solution is not an option, because of
versions os the clients OS version - so perhaps anyone of you beautiful
people have a (API) solution to detect what causing a certain job to be in
lock wait.

Otherwise - I have to re engineer my solution using the API's

QWCRJBLK for JOB_LOCK_INFO;
QWCRLCKI for OBJECT_LOCK_INFO;
QDBRRCDL for RECORD_LOCK_INFO;
QWCRLRQI for OBJECT_LOCK_INFO;

Any help will be highly appreciated.

create or replace function yxdb.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 (
yxdb.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 YXDB/TEST"*