Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
For this gist, I was asked to provide a query that would find jobs that have been stuck on Message Wait (MSGW) status for > 90 minutes. There's a 3 part progression to reach the solution.
--
-- Find active jobs (in QBATCH) that are in message wait status
--
with msgw_jobs (jn) as (
select job_name
from table (
qsys2.active_job_info(subsystem_list_filter => 'QBATCH')
) x
where job_status = 'MSGW'
)
select *
from msgw_jobs;
stop;
--
-- Find active jobs (in QBATCH) that are in message wait status, and find the message that they're waiting on
--
with msgw_jobs (jn) as (
select job_name
from table (
qsys2.active_job_info(subsystem_list_filter => 'QBATCH')
) x
where job_status = 'MSGW'
)
select from_job as job, from_user as user_name,
message_timestamp, message_id, message_type, message_text,
severity, message_key
from msgw_jobs, lateral (select * from qsys2.message_queue_info a
where from_job = jn
order by message_timestamp asc
limit 1);
stop;
--
-- Find active jobs (in QBATCH) that are in message wait status, and find the message that they're waiting on
-- note: Only return those jobs that have been waiting on a response for > 90 minutes
--
with msgw_jobs (jn) as (
select job_name
from table (
qsys2.active_job_info(subsystem_list_filter => 'QBATCH')
) x
where job_status = 'MSGW'
)
select from_job as job, from_user as user_name,
message_timestamp, message_id, message_type, message_text,
severity, message_key
from msgw_jobs, lateral (select * from qsys2.message_queue_info a
where from_job = jn
order by message_timestamp asc
limit 1)
where message_timestamp < current timestamp - 90 minutes;
@carlosir

This comment has been minimized.

Copy link

@carlosir carlosir commented Dec 4, 2020

Thanks Scott
It is in my Scott’s tool now
Have a great weekend

@AjaykulkarniIBMi

This comment has been minimized.

Copy link

@AjaykulkarniIBMi AjaykulkarniIBMi commented Dec 20, 2020

Cool tool, Scott, you continue to make life easy

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment