Examining the PRCSYSPURGE Application Engine Program

The PRCSYSPURGE application engine program is run on the master Process Schedule server. The following steps are executed by the program:

  1. Step TrigPrg: This step updates statistics and purges process request tables.

  2. Step ArchRptM: This step cleans up Report Manager Repository and purges Report Repository tables.

  3. If more than one Process Scheduler server exists, the application engine program PRCSRVCLN is scheduled to run. This program cleans up the Report Repository for a specific server.

  4. Step SchdlCl2: This step schedules the application engine program PSXPARCHATTR to run. This program archives BI Publisher report-related metadata and the URLs of the report files.

This diagram shows the flow for the PRCSYSPURGE application engine program

Program flow for PRCSYSPURGE application engine program

Step TrigPrg: Purge Process Request Tables

In this step, statistics are updated for the process request tables and then expired processes and jobs are removed from process request tables. Expired items are archived in the PSPRCSRQSTARCH table.

The expiration of a process or job is determined by the field LASTUPDDTTM on the PSPRCSRQST table and the purge settings configured for each process request status. For each of the run statuses listed in this table, you will determine if the purge should be enabled and indicate the number of days before a process request is purged.

Run Status Code Value

Cancel

1

Delete

2

Error

3

Hold

4

Cancelled

8

Successful

9

Not Successful

10

Successful with Warning

17

See Defining System Purge Options.

This step runs the following steps to remove and archive expired processes or jobs from process request tables.

  1. Run Statistics

    This step updates database statistics of process request tables to insure that the optimizer has updated information.

    Note:

    This step is enabled or disabled, based on the setting in psprcs.cfg.

    See Enabling and Disabling Statistics.

  2. Gather information on the run statuses to be purged

    This step gathers information about the run statuses which are enabled for purging. This information is later used to remove items from process request tables. It is obtained by querying the table PS_PRCSPURGELIST. The value DAYSBEFOREPURGE and current date are used to determine if a process or job entry has expired.

  3. Remove existing entries from PSPRCSRQSTARCH

    The table PSPRCSRQSTARCH is used to store expired items. It has a column DELETE_FLAG to identify the items for deletion. This step deletes all items marked for deletion (DELETE_FLAG=1) between runs of the PRCSYSPURGE program from PSPTCSRQSTARCH table before attempting to add newly expired entries.

  4. Remove duplicates from PSPRCSRQSTARCH

    This step removes any remaining expired processes or job items from the PSPRCSRQSTARCH table which also exist in PSPRCSRQST table. This is done to avoid inserting duplicate entries into this table when newly expired jobs or processes are added.

  5. Archive single expired processes to PSPRCSRQSTARCH

    This step moves all expired single processes, with run status enabled for purge, from the PSPRCSRQST table to the PSPRCSRQSTARCH table. The process expiration date is determined by calculating:

    PSPRCSRQST. LASTUPDDTTM< = Current Date – number of days before purge (from the System Purge Options page)

    For example, if the current date is May 12, 2009 and the number of days before purge for the run status Success is 7, all successful processes run on or before May 5, 2009 are eligible for deletion.

    Each process moved into the PSPRCSRQSTARCH table has a DELETE_FLAG = 0.

  6. Archive expired job header to PSPRCSRQSTARCH

    This step performs the same function as the previous step for job headers.

  7. Archive expired job items to PSPRCSRQSTARCH

    For each main job that was moved into PSPRCSRQST table in the previous step, this step will locate and move the corresponding job items.

  8. Mark items in PSPRCSRQSTARCH for removal

    On successful completion of steps 1 through 7, all of the transactions are committed and all rows in PSPRCSRQSTARCH are marked for deletion. This is done by updating the PSPRCSRQSTARCH table with DELETE_FLAG=1. If any of the data modification steps 1 through 7 were not successful, then all transactions are rolled back and the tables are restored to their original state.

  9. Remove expired processes and jobs from process request tables

    This step removes all expired entries from the process request tables. These tables include:

    • PSPRCSRQST

    • PSPRCSQUE

    • PSPRCSCHLDINFO

    • PSPRCSPARMS

    • PSPRCSRQSTXFER

    • PS_PRCSRQSTDIST

    • PSPRCSRQSTTEXT

    • PSPRCSRQSTFILE

    • PSPRCSRQSTMETA

    • PSPRCSRQSTSTRNG

    • PSPRCSRQSTTIME

    • PS_PRCSRQSTNOTIFY

    • PS_PRCSRQSTURL

    • PS_PRCSFILE

    • PSPRCSJOBSTATUS

    • PSNVSBATCHRSTRT

  10. Remove expired entries from message log tables

    This step removes the messages that are logged by the expired processes from message log tables PS_MESSAGE_LOG and PS_MESSAGE_LOGPARM.

    All the entries which are at least one day old are deleted from the PSBATAUTH table to avoid an accumulation of messages.

Step ArchRptM: Archive Report Manager Tables

In this step, all expired reports are archived to the PS_CDM_LIST_PURGE and PS_CDM_LIST_ARCH tables, their associated report output is removed from the Report Repository and finally, all of the expired report entries are removed from the Report Distribution tables.

The following steps are followed to archive Report Manager tables.

  1. Select expired reports

    All the expired reports are obtained from PS_CDM_LIST table. A report’s expiration is determined using the EXPIRATION_DATE field in the PS_CDM_LIST table and the current date. Reports with distribution status 6 (DELETE) are also considered expired.

    See the Report Retention Days section earlier in this topic for additional information.

  2. Remove output from Report Repository

    For each of the expired reports, the corresponding output files and folders are removed from the Report Repository. Removal of output files and folders is performed by a script which is auto-generated based on the transfer protocol (Ftp/Xcopy or Http) and Operating System selections found in the Report Node definition.

    This is sample script generated for an Ftp/XCopy node on Windows:

    @ECHO off
set DELETELOG=LOGOUTPUTDIR\%\delete_0520.log
if exist %DELETELOG% del %DELETELOG%
echo Deleting "\\ple70009qtas\psreports\t850u71nt/20090513/656" ... 
>>%DELETELOG%
if exist "\\ ple70009qtas \psreports\ t850u71nt /20090513/656\" rd  
"\\ple70009\psreports\ t850u71nt /20090513/656\" /q /s
echo End of Transfer script >> %DELETELOG%
@ECHO on

    See Defining Report Nodes.

  3. Archive expired reports to PS_CDM_LIST_ARCH

    Move all the expired reports with known distribution nodes except for the Deleted reports (Distribution Status = 6) from the PS_CDM_LIST table to the PS_CDM_LIST_ARCH table.

  4. Archive expired reports in PS_CDM_LIST_PURGE

    This step is almost the same as the previous step. However, in this case all the expired and deleted reports where the report distribution node does not match the distribution node of the server are archived. The purge process can only cleanup data for the report node designated for the server it is running on. Data for all the other active Process Scheduler Servers will be deleted as part of the PRCSRVCLN process (Step SchdlCln).

  5. Remove expired reports from the report distribution tables

    After all of the expired report entries have been successfully moved to the PS_CDM_LIST_ARCH and PS_CDM_LIST_PURGE archive tables, they are removed from the Report Distribution tables. This step deletes all expired report items from the following report distribution tables:

    • PS_CDM_AUTH

    • PS_CDM_LIST

    • PS_CDM_FILE_LIST

    • PS_CDM_TRANSFER

    • PSRF_RATTR_TBL

    • PSRF_RSCRTY_TBL

    • PSRF_RDESC_TBL

    • PSRF_RDESC_LANG

    • PSRF_RINFO_TBL

Step SchdlCln: Schedule PRCSSRVCLN Application Engine Program

If PRCSYSPURGE detects that more than one Process Scheduler is active, it schedules the application engine program PRCSSRVCLN to run on each of the active Process Scheduler servers to clean up the expired process, job, and report data for that server. For example, if there are two process scheduler servers PSNT and PSNT1. The purge program runs on PSNT which archives the expired process, job, and report data that ran on server PSNT. The program PRCSSRVCLN archives the expired process, job, and report data that ran on server PSNT1. If there is application engine (AE) program data to be purged, then all the related temporary tables and state records used by the expired AE program will also be removed.

This application engine program is launched if there are more active process scheduler servers. The following steps are executed.

  1. Select all expired processes from PSPRCSRQSTARCH run by a specific server

    This step creates a list of all expired single processes for a specific server.

  2. Select all expired job items from PSPRCSRQSTARCH run by a specific server

    This step appends the list created in step 1 with all job items that are expired and run by the server referenced in step 1.

  3. Purge application engine related tables

    This step removes all temporary tables or state records for any expired AE programs in the list created in step 2 . Related information tables include temporary tables or records used by the program. First, the PS_AETEMPTBLMGR is queried to find the records used by the expired AE program. Then, the table SQLTABLENAME is queried to find table names of corresponding records used by the AE program. Lastly, DELETE statements are issued to clean up the tables used by AE.

    • TBLNAME

    • RNAME

    • PS_AETEMPTBLMGR

    • PS_AERUNCONTROL

    • PS_AERUNCONTROLPC

  4. Remove expired entries run by the specified Process Scheduler server from the archive table PSPRCSRQSTARCH

    This step deletes all the processes or jobs run by server from the archive table PSPRCSRQSTARCH.

  5. Clean the archive table PS_CDM_LIST_PURGE of all the expired reports run by a specific server

    This step deletes all the expired reports run by specified server from the PS_CDM_LIST_PURGE table. All the corresponding output files will also be deleted from the report repository.

Step SchdlCl2: Schedule PSXPARCHATTR AE Program

This is the last step in the purge process. This step schedules the application engine program PSXPARCHAATTR to archive metadata related to expired BI Publisher reports. The URLs of the report files are archived along with the process information to the PSXP_FLURL_ARCH table. Search data related to BI Publisher reports is stored along with the process information in the PSXP_RATTR_ARCH table.