Integration Broker Error Monitoring with SQL
- Jeff
- Sep 1, 2021
- 1 min read
Updated: Sep 3, 2021
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
Comments