RE: What resource are this job waiting for

What's the OS release level ?

Regards,
Richard Schoen
Web: http://www.richardschoen.net
Email: richard@xxxxxxxxxxxxxxxxx

----------------------------------------------------------------------

message: 1
date: Fri, 10 Dec 2021 13:05:37 +0100
from: Niels Liisberg <nli@xxxxxxxxxxxxxxxxx>
subject: 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"*