RIB 13.1.x - JMS AQ Monitoring

PT : RIB - JMS AQ 

EN : RIB - JMS AQ

Items

-- find all items queues
select * from user_queues where queue_type = 'NORMAL_QUEUE' and name like '%ITEM%'

--- find all items to process
select * from ETITEMSTLFROMRIBTABLE

-- queue subscribers
select * from AQ$_ETITEMSTLFROMRIBTABLE_S

-- queue historic
select * from AQ$_ETITEMSTLFROMRIBTABLE_H where msgid = 'FE4C339218D830B1E0440024813309CD'

--- find all tables/queue tables
select * from dba_tables where table_name like '%ETITEMSTLFROMRIB%'

-- Test Integration - Items 
-- Item : 101715654

-- RMS DB
select  item, item_desc  from item_master where item ='101715654'

ITEM ITEM_DESC
101715654 ITEM XPTO

update  rms.item_master
set item_desc = 'ITEM XPTO - DESC CHANGED'
where item='101715654';

select  item, item_desc  from item_master where item ='101715654';
commit;

-- RIB DB
-- if you are fast enough

SELECT msgid,enq_time, deq_time, r.user_data.text_lob
FROM RIBAQ.ETITEMSFROMRMSTABLE r
WHERE
enq_time > sysdate - 1
and r.user_data.text_lob  like '%101715654%'
ORDER BY enq_time;

--WMS DB 
select  item_id, description  from wms.item_master where item_id ='101715654';
101715654 ITEM XPTO - DESC CHANGED

Vendor

-- find user queues
select * from user_queues where queue_type = 'NORMAL_QUEUE' and name like '%VENDOR%'

select * from user_queues
where
name like '%VEND%' and QUEUE_TYPE = 'NORMAL_QUEUE'
and name not like '%WH%'

--count number vendors
select count(0) from ETVENDORFROMRMSTABLE

-- select all
select * from ETVENDORFROMRMSTABLE

-- Validate who subscribed the the message (Query 1)

select subscriber#,count(0)  from AQ$_ETVENDORFROMRMSTABLE_H
where dequeue_time is null
group by subscriber#

-- Validate who subscribed the the message (Query 2)
select * from  AQ$_ETVENDORFROMRMSTABLE_S  where subscriber_id in (
select subscriber#  from AQ$_ETVENDORFROMRMSTABLE_H group by subscriber#)

Orders

--Order queues

select * from ETSTOCKORDERSISOTABLE

--Count orders to WH3

select count(*) from ETSTKORDERSFROMRIBTOWH3T;

-- WMS Queues

select * from user_tables where table_name like '%ORDERS%' and table_name not like '%WH%'

ASNOut

alter session set nls_date_format = 'dd-mm-yy hh24:mi'

select * from user_queues where name like '%ASN%' and QUEUE_TYPE = 'NORMAL_QUEUE'
and name not like '%WH%'

--tafr
select min(enq_time),sysdate as current_time from ETASNOUTTABLE,dual
select count(0), sysdate from ETASNOUTTABLE,dual

-- rms
select min(enq_time),sysdate as current_time from ETASNOUTATTABLE,dual

select count(0),sysdate as current_time from ETASNOUTATTABLE,dual

-- look into payload
select count(0) from
(SELECT enq_time, deq_time, r.user_data.text_lob
FROM ribaq.ETASNOUTATTABLE r
WHERE
 r.user_data.text_lob like '118000001000%')