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

Posted by Nitin Pai
Comments (5)
October 19th, 2007

Comments (5)

Chavi - July 26th, 2010

I am trying to do a async integration from ps to other party and when i am posting message from my system all the messages are getting posted but the messages are in new state only ….can any one help me in this?? PLEASE HELP !!!!

Raj - October 6th, 2008

Wanted find out on the customer sync interface Also, we'll need to load data into the INTFC_BI table(s) for both invoice and AR open items. What would be the quickest way to build these (the message, CI or inbound file publish interfaces) for this data?

Raj - October 2nd, 2008

I would like to know how to apply service packs and bundles to Peoplesoft system ? Any specific documentation on peoplesoft site

Bharath - September 23rd, 2008

When I try to take PIA trace for "Customer" Content reference, I find the login takes me to "Employee" Content reference. If I do not set trace, "Customer" content reference works fine. Kindly advise. PT 8.46.07 CRM 8.9

madhan - September 18th, 2008

I am trying to do an async integration from ps to ps and when i am posting message from HCM system all the messages are getting posted but the messages are in new state only in pub contract....can any one help me in this?? Thanks, Madhan

Comments are closed.