How to Monitor PeopleSoft Messages/Subscription/Publication Contracts?

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

Tagged as: PeopleSoft, Peoplesoft crm, Peoplesoft epm, Peoplesoft Financials, Peoplesoft Implementation, Peoplesoft scm, peoplesoft services, Peoplesoft support, Peoplesoft upgrade

You might want to read these awesome related posts

{ 5 comments }

1 madhan September 18, 2008 at 6:48 am: 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
2 Bharath September 23, 2008 at 10:47 am: 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
3 Raj October 2, 2008 at 11:30 am: I would like to know how to apply service packs and bundles to Peoplesoft system ? Any specific documentation on peoplesoft site
4 Raj October 6, 2008 at 3:02 pm: 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?
5 Chavi July 26, 2010 at 8:44 am: 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 !!!!