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

Read More About  PeopleSoft

UPGCOUNT – 2

In some cases the two preferred methods for executing UPGCOUNT do not work for you because

1. You do not have access to PSAE executable
2. You have very limited access to the environment to successfully run the AE

Here is a PL/SQL that you can use to provide similar result.

set serverout on size 1000000

DECLARE

CURSOR C1 IS

SELECT RECNAME, SQLTABLENAME FROM PSRECDEFN WHERE RECTYPE = 0 ORDER BY RECNAME;

VAR_COUNT NUMBER;

VAR_REC_COUNT NUMBER :=0;

VAR_SQLTEXT VARCHAR2(32000);

VAR_RECNAME VARCHAR2(64);

VAR_PAD VARCHAR2(100);

VAR_DATE DATE;

BEGIN

SELECT SYSDATE

INTO VAR_DATE

FROM DUAL;

DBMS_OUTPUT.PUT_LINE(‘Report for Record Rowcount’);

DBMS_OUTPUT.PUT_LINE(‘START DATE AND TIME:’||TO_CHAR(VAR_DATE,’MM/DD/YYYY HH24:MI:SS’));

DBMS_OUTPUT.PUT_LINE(‘RECNAME Row Count’);

DBMS_OUTPUT.PUT_LINE(‘============================== =========’);

FOR CUR_REC IN C1

LOOP

VAR_PAD := RPAD(‘ ‘,31 – LENGTH(CUR_REC.RECNAME));

IF NVL(LENGTH(RTRIM(CUR_REC.SQLTABLENAME)),0) = 0 THEN

VAR_RECNAME := ‘PS_’||CUR_REC.RECNAME;

END IF;

VAR_REC_COUNT := VAR_REC_COUNT + 1;

BEGIN

VAR_SQLTEXT := ‘SELECT COUNT(*) FROM ‘||VAR_RECNAME;

EXECUTE IMMEDIATE VAR_SQLTEXT INTO VAR_COUNT;

DBMS_OUTPUT.PUT_LINE(CUR_REC.RECNAME||VAR_PAD||VAR_COUNT);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(CUR_REC.RECNAME||VAR_PAD||’ERROR – ‘||SQLERRM);

END;

END LOOP;

DBMS_OUTPUT.PUT_LINE(‘============================== =========’);

DBMS_OUTPUT.PUT_LINE(‘ ‘);

DBMS_OUTPUT.PUT_LINE(‘Total Number of Tables = ‘||VAR_REC_COUNT);

SELECT SYSDATE

INTO VAR_DATE

FROM DUAL;

DBMS_OUTPUT.PUT_LINE(‘END DATE AND TIME:’||TO_CHAR(VAR_DATE,’MM/DD/YYYY HH24:MI:SS’));

END;

/

Read More About UPGCOUNT