Thursday, June 24, 2010

Messages jammed in BGQueue!!

There is a typical scenario that when messages are not getting moved from the Background queue and you can see that message count is getting increased.

Queue ID : 1

$SWSIR/util/swadm count_messages 1
Queue ID : 2
$SWSIR/util/swadm count_messages 2

Please follow the steps given below :

1. Stop the iProcess Engine
2. Run the following query on IPE Database - Once this is completed.
3. Start iProcess Engine.

SQL Query:

DECLARE

dequeue_options DBMS_AQ.dequeue_options_t;
message_properties DBMS_AQ.message_properties_t;
message_handle RAW (16);
MESSAGE swlocalmsg;
STATUS VARCHAR2 (10);
enqueue_options DBMS_AQ.enqueue_options_t;

-- Build cursor of exception messages, from both queues
CURSOR c1 IS
SELECT msgid, user_data, enq_time, q_name FROM BGMBOXTABLE1 WHERE state = 0
UNION ALL
SELECT msgid, user_data, enq_time, q_name FROM BGMBOXTABLE2 WHERE state = 0;

xt swlocalmsg;
v_head VARCHAR2(20);
v_user VARCHAR2(20);
v_inst VARCHAR2(20);
v_in INTEGER;
v_io INTEGER;

BEGIN
-- enable dequeue for exception queues
--DBMS_AQADM.START_QUEUE ('aq$_bgmboxtable1_e', FALSE, TRUE);
--DBMS_AQADM.START_QUEUE ('aq$_bgmboxtable2_e', FALSE, TRUE);

-- Open exception cursor
FOR x_c1 IN c1 LOOP

xt := x_c1.user_data;

-- ################
-- Breakdown the message
-- ################
v_in := INSTR(xt.msginfo,'^');
v_io := v_in;

v_head := SUBSTR(xt.msginfo,1,(v_in - 1));
v_in := INSTR(xt.msginfo,'^',(v_in + 1));
v_user := SUBSTR(xt.msginfo, (v_io + 1), ((v_in - v_io) - 1));

v_io := v_in;
v_in := INSTR(xt.msginfo,'^',(v_in + 1));
v_inst := SUBSTR(xt.msginfo, (v_io + 1), ((v_in - v_io) - 1));


IF v_inst = 'PROCCASE' THEN
dequeue_options.msgid := x_c1.msgid;
dbms_output.put_line( v_inst||' '||dequeue_options.msgid);
dBMS_AQ.dequeue (queue_name => x_c1.q_name,
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => MESSAGE,
msgid => message_handle
);
END IF;
END LOOP;
COMMIT;
END;
/

No comments:

Post a Comment

Followers