Monthly Archives: September 2018

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;