Category Archives: Integration Broker

Helpful SQL for Integration Broker Monitoring

#1 Count of messages older than 90 days.
#2 Messages older than 90 days.
#3 Counts/per message.


SELECT COUNT(*) FROM PSAPMSGPUBHDR WHERE CREATEDTTM < SYSDATE-90;
SELECT * FROM PSAPMSGPUBHDR WHERE CREATEDTTM < SYSDATE-90;
SELECT DISTINCT(IB_OPERATIONNAME),COUNT(*) FROM PSAPMSGPUBHDR GROUP BY IB_OPERATIONNAME;

SELECT COUNT(*) FROM PSAPMSGPUBCON WHERE CREATEDTTM < SYSDATE-90;
SELECT * FROM PSAPMSGPUBCON WHERE CREATEDTTM < SYSDATE-90;
SELECT DISTINCT(IB_OPERATIONNAME),COUNT(*) FROM PSAPMSGPUBCON GROUP BY IB_OPERATIONNAME;

SELECT COUNT(*) FROM PSAPMSGSUBCON WHERE CREATEDTTM < SYSDATE-90;
SELECT * FROM PSAPMSGSUBCON WHERE CREATEDTTM < SYSDATE-90;
SELECT DISTINCT(IB_OPERATIONNAME),COUNT(*) FROM PSAPMSGSUBCON GROUP BY IB_OPERATIONNAME;

SELECT COUNT(*) FROM PSIBLOGHDR WHERE PUBLISHTIMESTAMP < SYSDATE-90;
SELECT * FROM PSIBLOGHDR WHERE PUBLISHTIMESTAMP < SYSDATE-90;
SELECT DISTINCT(IB_OPERATIONNAME),COUNT(*) FROM PSIBLOGHDR GROUP BY IB_OPERATIONNAME;

No contracts were created because the publication did not pass any filters.

“No contracts were created because the publication did not pass any filters.”

If you get this message in your Application Server logs when trying to invoke the “Service Operation Tester” on a Service Operation you need to create a Local to Local routing for your Service Operation because the Application Server doesn’t know where to route your message. Usually you would create a Any to Local routing by default, but the tester needs the Local to Local routing built so it knows what to do with it inside PIA.