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

UPGCOUNT – 1

UPGCOUNT is an AE in current tools releases. Also, there is no delivered page to kick-off this process. The output from this report is critical to determine the success of a major migration (MP or multiple bundles or Upgrade). This helps validate the process to ensure that no data was lost as a result of the migration (running Data Mover scripts, data conversion, etc).

Here are some methods that you can use to run this AE program.

1.Through App Designer

• Open the UPGCOUNT AE in App Designer
• Click on Run Program icon
• Choose appropriate options on the “Run Request” dialog box

The output file will be located in your temp folder

2.Through command line

• Execute the below commands for running on UNIX

export PS_SERVDIR=$PS_HOME/appserv/prcs/FDMO exportPS_SERVER_CFG=$PS_HOME/appserv/prcs/FDMO/psprcs.cfg psae -CT Oracle -CD FDMO -CO VP1 -CP VP1 -R UPGCOUNT1 -AI UPGCOUNT -I 11 -TRACE 135

Read More About UPGCOUNT

PeopleSoft Audit Reports and Migration

I have always recommended the following reports to determine a clean migration especially when there are a ton of objects to be migrated (PS Upgrade, Application of Maintenance Packs or Bundles, etc).

Prior to application of the bundles execute the following.

1. SYSAUDIT
2. DDDAUDIT
3. ALTER AUDIT
4. UPGCOUNT (just prior to the actual migration)

The first three can be executed a few hours or days before the actual migration as long as you ensure that no changes are migrated or performed in to the target environment which might cause an impact on these reports. However the UPGCOUNT process has to run just prior to the actual migration because this provides a row count of every record in the DB.
You need to re-run the above reports after completion of the migration. The objective should be as noted below.

1. Ensure that the reports match
2. For any mismatch there has to be an explanation

Note 1:
UPGCOUNT might not match for all records after application of Maintenance Pack/Bundle/Upgrade. You need to identify what caused the change. You can denote “Bundle 12 – Data Mover script upgnnnn.dms” in your documentation. This exercise will ensure that you have a clean migration.

Record	Before	After	Comments
PSPRCSRQST	10002	10008	Bundle # 11 – Execution of SQR program in step 21, 22, 29.
BENEFIT_PARTIC	103110	103212	Bundle # 12 – Execution of data mover script upgnnnn.dms

Note 2:

It is a good practice to ensure that you have a clean SYSAUDIT, DDDAUDIT and ALTER AUDIT report.

Read More About  PeopleSoft Audit Reports