Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
The request... show how SQL can tackle that pesky MESSAGE_KEY binary value and respond to an inquiry message.
--
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment