One of the hallmarks of a good administrator is that they are already troubleshooting the problem before the users complain!
The key to ensuring that the system does not spring surprises is to automate monitoring (without impacting the system performance). In my previous posts, I have provided steps to monitor the process scheduler and application server. This post will provide insights into monitoring the integration broker messages, publication and subscription contracts.
Use the below SQLs for monitoring.
SUBSCRIPTION
SELECT A.CHNLNAME,
DECODE(A.SUBCONSTATUS,0,’ERROR’,1,’NEW’,4,’DONE’,A.SUBCONSTATUS) STATUS, COUNT(*)
FROM PSAPMSGSUBCON A
WHERE
– not older than 99 days
A.LASTUPDDTTM >= sysdate – 99
– in error or timeout status
AND (A.SUBCONSTATUS IN (0, 6)
– in NEW status but was last updated 30 mins agao.
OR (A.SUBCONSTATUS = 1 AND A.LASTUPDDTTM < SYSDATE – 1/48))
GROUP BY A.CHNLNAME, DECODE(A.SUBCONSTATUS,0,’ERROR’,1,’NEW’,4,’DONE’,A.SUBCONSTATUS)
/
MESSAGE
SELECT A.CHNLNAME, DECODE(A.PUBSTATUS,0,’ERROR’,1,’NEW’,4,’DONE’,A.PUBSTATUS) STATUS, COUNT(*)
FROM PSAPMSGPUBHDR A
WHERE
– not older than 99 days
A.LASTUPDDTTM >= sysdate – 99
– in error or timeout status
AND (A.PUBSTATUS IN (0, 6)
– in NEW status but was last updated 30 mins agao.
OR (A.PUBSTATUS = 1 AND A.LASTUPDDTTM < SYSDATE – 1/48))
GROUP BY A.CHNLNAME, DECODE(A.PUBSTATUS,0,’ERROR’,1,’NEW’,4,’DONE’,A.PUBSTATUS)
/
PUBLICATION
SELECT A.CHNLNAME, DECODE(A.PUBCONSTATUS,0,’ERROR’,1,’NEW’,4,’DONE’,A.PUBCONSTATUS) STATUS, COUNT(*)
FROM PSAPMSGPUBCON A
WHERE
– not older than 99 days
A.LASTUPDDTTM >= sysdate – 99
– in error or timeout status
AND (A.PUBCONSTATUS IN (0, 6)
– in NEW status but was last updated 30 mins agao.
OR (A.PUBCONSTATUS = 1 AND A.LASTUPDDTTM < SYSDATE – 1/48))
GROUP BY A.CHNLNAME, DECODE(A.PUBCONSTATUS,0,’ERROR’,1,’NEW’,4,’DONE’,A.PUBCONSTATUS)
/
Value
|
Status
|
0
|
ERROR
|
1
|
NEW
|
2
|
STARTED
|
3
|
WORKING
|
4
|
DONE
|
5
|
RETRY
|
6
|
TIMEOUT
|
7
|
EDITED
|
8
|
CANCELED
|
9
|
HOLD
|
