Skip to content

Instantly share code, notes, and snippets.

@forstie
Last active June 9, 2020 20:34
Show Gist options
  • Save forstie/c80b10451f8cffaf2dab6e13df557c9c to your computer and use it in GitHub Desktop.
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
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);
@bdietz400
Copy link

in line 2 should "user" should be "from_user" ?
also other 'user' references be "user" ?

@forstie
Copy link
Author

forstie commented Jun 9, 2020

Good points!
Please see the revised version.

@mmayer625
Copy link

mmayer625 commented Jun 9, 2020

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
--- ),

 qsysopr_answers_today (assoc_key) as (
   select associated_message_key
     from qsys2.msgq_info
     where message_queue_library = 'QSYS'
           and message_queue_name = 'QSYSOPR'
           and message_type = 'REPLY'
           and date(message_timestamp) >= '2020-06-05-00.00.00.000000'
 )          

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