Integration Broker Error Monitoring with SQL

The SQL could probably use some dressing up, however these queries help me to understand the overall error rate of IB processes.

-- SYNCHRONOUS API Calls

--Summary

SELECT

statusstring,

count(pubnode)

FROM sysadm.PSIBLOGHDR

where trunc(publishtimestamp) between '01-JUN-21' and '01-SEP-21'

group by statusstring;

--Detail

SELECT

ib_operationname,

statusstring,

count(pubnode)

FROM sysadm.PSIBLOGHDR

where trunc(publishtimestamp) between '01-JUN-21' and '01-SEP-21'

group by statusstring, ib_operationname;

-- AYNCHRONOUS API Calls

--Summary

SELECT

count(*)

FROM sysadm.PSAPMSGSUBCON

where trunc(createdttm) between '01-JUN-21' and '01-SEP-21';

--Detail

SELECT

count(actionname) total_retries,

queuename,

retrycount

FROM sysadm.PSAPMSGSUBCON

where trunc(createdttm) between '01-JUN-21' and '01-SEP-21'

and retrycount <> '0'

group by retrycount,queuename;

--Alternate SQL for between today and a number of days prior

SELECT

count(actionname) total_retries,

queuename,

retrycount

FROM sysadm.PSAPMSGSUBCON

where trunc(createdttm) between trunc(systimestamp)-90 and trunc(systimestamp)

and retrycount <> '0'

group by retrycount,queuename;

--References

http://dineshbandelkar.com/peoplesoft-integration-broker-performance-issue-after-dataguard-remote-far-sync-implementation/

https://docs.oracle.com/cd/F44947_01/pt858pbr3/eng/pt/index.html