Re: What resource are this job waiting for -- RPG400-L

Hello Richard - You ask:

What's the OS release level?

All the clients can have any supported OS. But the tricky part is TR - Some
are super old TR and therefore my SQL solution will not work.

On Sat, Dec 11, 2021 at 9:58 PM Richard Schoen <richard@xxxxxxxxxxxxxxxxx>
wrote:

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"*

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com