Last active
June 9, 2020 20:34
-
-
Save forstie/c80b10451f8cffaf2dab6e13df557c9c to your computer and use it in GitHub Desktop.
Inquiry messages to the QSYSOPR message queue might be worth answering. Use this query to see which questions have not been answered today. #SQLcandoit
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
with qsysopr_inquiries_today (msg, asker, text, key) as ( | |
select message_id, from_user, message_text, message_key | |
from qsys2.message_queue_info | |
where message_queue_library = 'QSYS' | |
and message_queue_name = 'QSYSOPR' | |
and message_type = 'INQUIRY' | |
and date(message_timestamp) = current_date | |
), | |
qsysopr_answers_today (assoc_key) as ( | |
select associated_message_key | |
from qsys2.message_queue_info | |
where message_queue_library = 'QSYS' | |
and message_queue_name = 'QSYSOPR' | |
and message_type = 'REPLY' | |
and date(message_timestamp) = current_date | |
) | |
select msg, asker, text | |
from qsysopr_inquiries_today | |
where key not in (select assoc_key | |
from qsysopr_answers_today); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
In case anyone wants it, did a bit of research on the file @ V7R2 (I know, behind on the OS - not my decision). Here's how it could look.
Please let me know if this isn't correct or needs an adjustment.
MM
--- Which system operator messgaes have NOT been answered today
--- Or set message_timestamp as needed
with qsysopr_inquiries_today (msg, asker, text, key) as (
select message_id, from_user, message_text, message_key
from qsys2.msgq_info
where message_queue_library = 'QSYS'
and message_queue_name = 'QSYSOPR'
and message_type = 'INQUIRY'
and date(message_timestamp) >= '2020-06-05-00.00.00.000000'
),
--- and date(message_timestamp) = current_date
--- ),
--- and date(message_timestamp) = current_date
--- )
select msg, asker, text
from qsysopr_inquiries_today
where key not in (select assoc_key
from qsysopr_answers_today);