PeopleSosft IB Messages Monitoring Scripts

 

 

 

-- PUB Contract monitoring:

 

SELECT A.QUEUENAME,DECODE(A.PUBCONSTATUS,0,'ERROR',6,'TIME',A.PUBCONSTATUS) STATUS, COUNT(*) FROM PSAPMSGPUBCON A

WHERE A.LASTUPDDTTM >= sysdate - 99 AND (A.PUBCONSTATUS IN (0,6)

OR (A.PUBCONSTATUS = 0 AND A.LASTUPDDTTM < SYSDATE - 1/48))

GROUP BY A.QUEUENAME, DECODE(A.PUBCONSTATUS,0,'ERROR',6,'TIME',A.PUBCONSTATUS);

 

 

--Oper Instance monitoring:

 

SELECT  A.QUEUENAME,DECODE(A.PUBSTATUS,0,'ERROR',6,'TIME',A.PUBSTATUS) STATUS, COUNT(*) FROM PSAPMSGPUBHDR A

WHERE A.LASTUPDDTTM >= sysdate - 99

AND (A.PUBSTATUS IN (0, 6) OR (A.PUBSTATUS = 0 AND A.LASTUPDDTTM < SYSDATE - 1/48))

GROUP BY A.QUEUENAME, DECODE(A.PUBSTATUS,0,'ERROR',6,'TIME',A.PUBSTATUS);

 

--Sub Contract monitoring

SELECT A.QUEUENAME,DECODE(A.SUBCONSTATUS,0,'ERROR',6,'TIME',A.SUBCONSTATUS) STATUS, COUNT(*)

FROM PSAPMSGSUBCON A

WHERE A.LASTUPDDTTM >= sysdate - 99

AND (A.SUBCONSTATUS IN (0, 6)

OR (A.SUBCONSTATUS = 0 AND A.LASTUPDDTTM < SYSDATE - 1/48))

GROUP BY A.QUEUENAME,  DECODE(A.SUBCONSTATUS,0,'ERROR',6,'TIME',A.SUBCONSTATUS);

 

 

Note :

 Script updated to check only last 99 days "error and timeout" messages.

 

 

Useful Oracle Timeout format :

 

Time Interval	Format
Now	sysdate
Tomorrow/Next Day	sysdate + 1
Seven Days from Now	sysdate + 7
One Hour from Now	sysdate + 1/24
Three Hours from Now	sysdate + 3/24
Half an Hour from Now	sysdate + 1/48
Ten Minutes from Now	sysdate + 10/1440
Thirty Seconds from Now	sysdate + 30/86400
Tomorrow at 12 Midnight	trunc(sysdate + 1)
Tomorrow at 8am	trunc(sysdate + 1) + 8/24
Next Monday at 12 Noon	next_day(trunc(sysdate), 'MONDAY') + 12/24
First Day of the Month at 12 Midnight	trunc(last_day(sysdate) + 1)
The Nex Monday, Wednesday or Friday at 9am	trunc(least(next_day(sysdate,''MONDAY''),next_day(sysdate,''WEDNESDAY''),next_day(sysdate,''FRIDAY''))) + (9/24)