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