Integration Broker Queues

Integration Broker Queues

• Publication Contracts

select queuename,statusstring,count(*) from psapmsgpubcon where trunc(createdttm) = trunc(sysdate) group by queuename,statusstring

• Subsription Contracts

select queuename,statusstring,count(*) from psapmsgsubcon where trunc(createdttm) = trunc(sysdate) group by queuename,statusstring order by 1

For a date range.

• Publication Contracts

select queuename,statusstring,count(*) from psapmsgpubcon where trunc(createdttm) between trunc(sysdate-

• Subsription Contracts

select queuename,statusstring,count(*) from psapmsgsubcon where trunc(createdttm) between trunc(sysdate-

______________________________________________________________________________________________________________________

Domain Status

SELECT DOMAIN_STATUS INTO DSTAT FROM PSAPMSGDOMSTAT

______________________________________________________________________________________________________________________

Domain is active, but messages not being processed

For publication messages

SELECT IBTRANSACTIONID,QUEUENAME,CREATEDTTM,PUBLISHTIMESTAMP,STATUSSTRING ,

floor(((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60)/3600)

|| ' HOURS ' ||

floor((((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60) -

floor(((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60)/3600)*3600)/60)

|| ' MINUTES ' ||

round((((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60) -

floor(((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60)/3600)*3600 -

(floor((((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60) -

floor(((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60)/3600)*3600)/60)*60) ))

|| ' SECS ' time_difference

FROM PSAPMSGPUBCON

WHERE STATUSSTRING IN ('NEW','ERROR') AND trunc(createdttm) = trunc(sysdate) ORDER BY 4

For subscription messages

Looking at the above query and this query we can find out if the domain is active but still messages are not being proecessed.

SELECT IBTRANSACTIONID,QUEUENAME,CREATEDTTM,PUBLISHTIMESTAMP,STATUSSTRING ,

floor(((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60)/3600)

|| ' HOURS ' ||

floor((((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60) -

floor(((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60)/3600)*3600)/60)

|| ' MINUTES ' ||

round((((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60) -

floor(((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60)/3600)*3600 -

(floor((((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60) -

floor(((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60)/3600)*3600)/60)*60) ))

|| ' SECS ' time_difference

FROM PSAPMSGSUBCON

WHERE STATUSSTRING IN ('NEW','ERROR') AND trunc(createdttm) = trunc(sysdate) ORDER BY 4

Activate Domain Status

**** Be Sure that PUB/SUB processes are up and running before using this script ****

SET SERVEROUT ON;

SPOOL D:\APPS\MONITORDOMAIN\MONITORDOMAIN.LOG;

DECLARE

DSTAT VARCHAR2(1) := 'T';

BEGIN

BEGIN

SELECT DOMAIN_STATUS INTO DSTAT FROM PSAPMSGDOMSTAT WHERE DOMAIN_STATUS = 'I';

EXCEPTION

WHEN NO_DATA_FOUND THEN

DSTAT:='A';

WHEN TOO_MANY_ROWS THEN

DSTAT:='I';

END;

IF DSTAT ='I' THEN

UPDATE PSIBFAILOVER SET IB_FAILOVER_FLAG = 1;

UPDATE PSAPMSGDSPSTAT SET DSPSTATUS = 0 , STATUSSTRING = 'ACT' WHERE STATUSSTRING='INACT';

UPDATE PSAPMSGDOMSTAT SET DOMAIN_STATUS = 'A' WHERE DOMAIN_STATUS = 'I';

END IF;

DBMS_OUTPUT.PUT_LINE(DSTAT);

END;

/

SPOOL OFF;

EXIT;