Process Scheduler

Hence it is easy to build this simple PL/SQL loop to request to all unsent content is reposted.

BEGIN
FOR x in  (
       SELECT prcsinstance
       FROM   psprcsrqst
       WHERE  diststatus = 4 --that are not posted
       AND    runstatus = 9  --successful processes
       )
LOOP
 UPDATE PSPRCSRQST
 SET    DISTSTATUS = '7'
 WHERE  PRCSINSTANCE = x.prcsinstance;

 UPDATE PSPRCSQUE
 SET    DISTSTATUS = '7'
 WHERE  PRCSINSTANCE = x.prcsinstance;

 UPDATE PS_CDM_LIST
 SET    DISTSTATUS   = '8'
 ,      TRANSFERINSTANCE = 0
 WHERE  PRCSINSTANCE = x.prcsinstance
 AND    DISTSTATUS   <> '5'; --posted
END LOOP;
END;
/
COMMIT
/

Distribution status stays "Posting" 








This problem is mainly the process interface value not being updated correctly on the process scheduler tables. As PSJobs don't post any process output files, it only update the status field base on the child processes. And since all the child process posted successfully, one can try to run the following query in the database to update this output.

 IMPORTANT... For security reasons, please make sure to back up the original tables before executing the following scripts. One may also want to stop the process scheduler server before query execution.

 1.) Double the current value of your Process Scheduler's Maximum Transfer Retries

 Home > PeopleTools > Process Scheduler > Servers > {Process Scheduler Server} > Distribution tab

 2.) Stop and restart your Process Scheduler.

 3.) Are you finding there are lots of Process Instance numbers perhaps from previous unsuccessful posting attempts that are still flagged as "Posting" in the PeopleTools tables?

 SELECT COUNT * FROM PSPRCSRQST WHERE DISTSTATUS=7
 SELECT COUNT * FROM PSPRCSQUE WHERE DISTSTATUS=7
 SELECT COUNT * FROM PS_CDM_LIST WHERE DISTSTATUS=7
 SELECT COUNT * FROM PS_CDM_TRANSFER (this table should be empty, if not.. STOP)

 4.) If so, as part of a cleanup either flag those to Not Posted (4) or POSTED (5)

 UPDATE PSPRCSRQST SET DISTSTATUS=4 WHERE DISTSTATUS=7
 UPDATE PSPRCSQUE SET DISTSTATUS=4 WHERE DISTSTATUS=7
 UPDATE PS_CDM_LIST SET DISTSTATUS=4 WHERE DISTSTATUS=7

 5.) This should prevent backlogs and performance issues with the Distribution Agent. 


The below script is for server name PSNT2, which you’ll need to replace with the appropriate name of the server name in your case.

DELETE FROM PS_SERVERDEFN WHERE SERVERNAME= ‘PSNT2’ ;
DELETE FROM PSSERVERSTAT where SERVERNAME = ‘PSNT2’ ;
DELETE FROM PS_SERVERCLASS WHERE SERVERNAME= ‘PSNT2’ ;
DELETE FROM PS_SERVEROPRTN WHERE SERVERNAME= ‘PSNT2’ ;
DELETE FROM PS_SERVERACTVTY WHERE SERVERNAME= ‘PSNT2’ ;
DELETE FROM PS_SERVERMESSAGE WHERE SERVERNAME= ‘PSNT2’ ;
DELETE FROM PS_SERVERNOTIFY WHERE SERVERNAME= ‘PSNT2’ ;
DELETE FROM PS_SERVERPURGLIST WHERE SERVERNAME= ‘PSNT2’ ;
DELETE FROM PSPRCSQUE WHERE SERVERNAMERQST= ‘PSNT2’ ;
DELETE FROM PS_CDM_TRANSFER WHERE SERVERNAMERUN= ‘PSNT2’ ;
DELETE FROM PSPRCSRQST WHERE SERVERNAMERQST= ‘PSNT2’ ;
DELETE FROM PS_SERVERCATEGORY WHERE SERVERNAME = ‘PSNT2’