Re: What resource are this job waiting for

I'm going to go with - you probably need to re-engineer to use the APIs and look at your minimum supported release level.

Always fun having to reverse engineer the cool stuff that you've been able to do with the new SQL services for older releases.

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

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

message: 5
date: Mon, 13 Dec 2021 15:56:53 +0100
from: Niels Liisberg <nli@xxxxxxxxxxxxxxxxx>
subject: Re: What resource are this job waiting for

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