PROCESSES IN POSTING STATUS

PeopleSoft DBA often faces lot of issues in batch server and the processes in posting status may be the prominent one. Here I have given a scenario and a simple but effective solution to solve this issue.  We have 2 process scheduler server domains in our finance production server and recently we faced an issue in the Master server which abruptly went down and the processes that were scheduled in it were in queued status. It happened because of corruption of the process instance which was resolved later. After then, the process started going to success but were still in 'Posting' status. 

The distribution agent log shows that the PSDSTSRV service died during posting with error :PSDSTSRV.7156 (2) [02/02/12 14:50:20 PostReport](0) PostReport: Unrecoverable error: out of available memory. Requested 390070272 bytes. I wonder whether it is trying to post a very huge report. Gather some information with the below queries.

1. select count(*) from PS_CDM_TRANSFER  -----> XXX rows (no of prcs instances in posting status)

2. select MIN(TRANSFERINSTANCE) from PS_CDM_TRANSFER; -----> shows which transfer instance is blocking.

3. select PRCSINSTANCE, CONTENTID, PRCSNAME, PRCSTYPE from PS_CDM_LIST where TRANSFERINSTANCE IN (select MIN(TRANSFERINSTANCE) from PS_CDM_TRANSFER);   -----> will show the process details that blocks

PS_CDM_TRANSFER is the table used to find any process that got stuck in posting (it stores info while transferring reports)

The posting issue will not be cleared until you clear the bottleneck that is occuring. XXX rows in PS_CDM_TRANSFER indicate that there is a bottleneck because there are XXX reports lining up to be posted. The cause of such bottlenecks normally is the oldest transferinstance (ie.. the lowest transferinstance) and usually this report tends to be huge in file size. The huge size causes blockage for the http transfer.  

By linking this transferinstance to the reporting table PS_CDM_LIST, you can then identify which process instance and contentid it is. This is the result 3 that was asked. Therefore, the idea is to delete this transferinstance from PS_CDM_TRANSFER table which was identified in result of 2^nd query (Eg:2314157)

After deleting the row and thus clear the blockage, the DISTSTATUS field for the process instance should be set to Not Posted (4) manually using SQL update statement on tables PSPRCSRQST/PSPRCSQUE/PS_CDM_LIST. Sometimes it’s necessary to repeat such steps if another blockage are found. The expected end result is that all reports go to posted and the PS_CDM_TRANSFER table is empty.

update PS_CDM_LIST set DISTSTATUS='4' where PRCSINSTANCE in (select PRCSINSTANCE from PS_CDM_LIST where DISTSTATUS='7');

update PSPRCSRQST set DISTSTATUS='4' where PRCSINSTANCE in (select PRCSINSTANCE from PSPRCSRQST where DISTSTATUS='7');

update PSPRCSQUE set DISTSTATUS='4' where PRCSINSTANCE in (select PRCSINSTANCE from PSPRCSQUE where DISTSTATUS='7');

Remove the offending process instance from PS_CDM_TRANSFER table and update other tables to reflect the not posted status. This will clear up the bottleneck and automatically the processes which were updated to Not Posted will get posted after sometime. 

Bounce the Process scheduler server...(mandatory)

These are the translate values for the DISTSTATUS field:

    * DISTSTATUS 0 = None

    * DISTSTATUS 1 = Scheduled (N/A)

    * DISTSTATUS 2 = Processing

    * DISTSTATUS 3 = Generated

    * DISTSTATUS 4 = Not posted

    * DISTSTATUS 5 = Posted

    * DISTSTATUS 6 = Delete

    * DISTSTATUS 7 = Posting (stuck).

If this workaround did not work, then run the following queries to update the posting status to 'Not Posted' in below 3 tables. This will change the posting status in process monitor. Once the status changes, click Re-send Content. This transfers the file from batch server to web server. Search in webserver with content ID (you can find it in PS_CDM_LIST table) under psreports folder. 

select PRCSNAME, PRCSTYPE , PRCSOUTPUTDIR from PS_CDM_LIST where PRCSINSTANCE = 'Process Instance'; 

update PS_CDM_LIST set DISTSTATUS='4' where PRCSINSTANCE ='Process Instance';

update PSPRCSRQST set DISTSTATUS='4' where PRCSINSTANCE ='Process Instance';

update PSPRCSQUE set DISTSTATUS='4' where PRCSINSTANCE ='Process Instance';