Configuring the Purge Process

System Purge Options

The PRCSYSPURGE program is scheduled to run based on the settings maintained on the System Purge Options page. Once the settings are saved, the PRCSYSPURGE application engine program will be scheduled to run at the selected interval.

Select PeopleTools > Process Scheduler > System Settings and select the System Purge Options tab.

Image: System Purge Options page

This example illustrates the fields and controls on the System Purge Options page.

The purge schedule is defined by entering a Next Purge Date, Next Purge Time and selecting a Recurrence schedule.

The Run Status Options list contains a list of process request run statuses that are eligible to be purged. Only completed processes or jobs are eligible for purge. The Days Before Purge entry is used to specify how long requests that complete with the corresponding run status should remain in the Process Request tables. All process requests with an Enabled run status and last update date past the Days Before Purge value specified for that run status will be purged.

Report Retention Days

The expiration date for a report is determined by adding the reports retention days to the date the report was run. Retention days for a report can be set at various levels. The system determines the retention days for the report at runtime in the order:

1. Run Control record

2. Process definition

3. Process Type definition

4. System Setting

Image: Expiration date determination

This diagram illustrates the precedence for determining the number of retention days for the report

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.

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.