The request... show how SQL can tackle that pesky MESSAGE_KEY binary value and respond to an inquiry message.

Responding to an inquiry message.sql

--
-- Subject: The request... show how SQL can tackle that pesky MESSAGE_KEY binary value and respond to an inquiry message.
-- Author: Scott Forstie  
-- Date  : May, 2022
-- Features Used : This Gist uses CTEs, SQL PL, message_queue_info, inner join, exception join, qcmdexc
--
-- 
--
stop;

--
-- Send an inquiry message
--
call qsys2.qcmdexc('SNDMSG MSG(''what is 1+2?'') TOUSR(*SYSOPR) MSGTYPE(*INQ) RPYMSGQ(QSYS/QSYSOPR) CCSID(37)');
stop;

--
-- Examine all system operator inquiry messages that have no reply
--
with REPLIED_MSGS (KEY) as (
    select a.message_key
      from qsys2.message_queue_info a
           inner join qsys2.message_queue_info b
             on a.message_key = b.associated_message_key
      where A.MESSAGE_QUEUE_NAME = 'QSYSOPR' and
            A.MESSAGE_QUEUE_LIBRARY = 'QSYS' and
            B.MESSAGE_QUEUE_NAME = 'QSYSOPR' and
            B.MESSAGE_QUEUE_LIBRARY = 'QSYS'
      order by b.message_timestamp desc)
  select a.message_text as "INQUIRY", A.*
    from qsys2.message_queue_info a
         left exception join REPLIED_MSGS b
           on a.message_key = b.key
    where MESSAGE_QUEUE_NAME = 'QSYSOPR' and
          MESSAGE_QUEUE_LIBRARY = 'QSYS' and
          message_type = 'INQUIRY'
    order by message_timestamp desc; 
stop;

--
-- Inquiry message responder
--
create or replace function coolstuff.inquiry_responder (
      text_search_string varchar(132) for sbcs data, response varchar(132) for sbcs data
  )
  returns integer
  not deterministic
  no external action
  modifies sql data
  not fenced
  set option COMMIT = *NONE
  begin
    declare LOCAL_SQLCODE integer;
    declare LOCAL_SQLSTATE char(5) for sbcs data;
    declare not_found condition for '02000';
    declare at_end integer default 0;
    declare v_message_text varchar(500) for sbcs data;
    declare msgq_lib varchar(10) for sbcs data;
    declare msgq_name varchar(10) for sbcs data;
    declare msg_key_value char(8);
    declare v_catalog_aspnum integer;
    declare v_count integer;
    declare c_inqs_stmt_text varchar(1000) for sbcs data;
    declare v_text_search_string varchar(132) for sbcs data;
    declare c_inqs cursor for c_inqs_stmt;
    declare exit handler for SQLEXCEPTION
    begin
      get diagnostics condition 1
          LOCAL_SQLCODE = DB2_RETURNED_SQLCODE, LOCAL_SQLSTATE = RETURNED_SQLSTATE;
      set V_Message_text = 'inquiry_responder() failed with: ' concat LOCAL_SQLCODE concat '  AND '
            concat LOCAL_SQLSTATE;
    end;
    declare continue handler for not_found set at_end = 1;
    --
    -- active reorgs in *SYSBAS
    --
    set c_inqs_stmt_text = 'WITH REPLIED_MSGS(KEY) AS (
    SELECT a.message_key
     FROM qsys2.message_queue_info a INNER JOIN   
          qsys2.message_queue_info b
    ON a.message_key = b.associated_message_key
    WHERE A.MESSAGE_QUEUE_NAME = ''QSYSOPR'' AND
          A.MESSAGE_QUEUE_LIBRARY = ''QSYS'' AND
          B.MESSAGE_QUEUE_NAME = ''QSYSOPR'' AND
          B.MESSAGE_QUEUE_LIBRARY = ''QSYS''
    ORDER BY b.message_timestamp DESC
    )
    SELECT a.message_queue_library, 
           a.message_queue_name, 
           hex(a.message_key)
     FROM qsys2.message_queue_info a 
          LEFT EXCEPTION JOIN REPLIED_MSGS b
    ON a.message_key = b.key
    WHERE MESSAGE_QUEUE_NAME = ''QSYSOPR'' AND
          MESSAGE_QUEUE_LIBRARY = ''QSYS'' AND
          message_type = ''INQUIRY''  AND
          upper(message_text) like ?
    ORDER BY message_timestamp DESC';
    prepare c_inqs_stmt from c_inqs_stmt_text;
    set v_text_search_string = '%' concat upper(text_search_string) concat '%';
    set v_count = 0;
    open c_inqs using v_text_search_string;
    set at_end = 0;
    fetch from c_inqs
      into msgq_lib, msgq_name, msg_key_value;
    while (at_end = 0) do
      call qsys2.qcmdexc('SNDRPY MSGKEY(x''' concat msg_key_value concat ''') MSGQ(' concat msgq_lib
          concat '/' concat msgq_name concat ') RPY(''' concat response concat ''')');
      set v_count = v_count + 1;
      fetch from c_inqs
        into msgq_lib, msgq_name, msg_key_value;
    end while;
    close c_inqs;
    return v_count;
  end;
stop;

values coolstuff.inquiry_responder(
    TEXT_SEARCH_STRING => 'what is 1+2?', RESPONSE => '3 of course!!!');
stop;