EMEA PeopleSoft Roadshow 2014

Friday, September 10, 2010

PeopleSoft Run Control Purge Utility

Run Control records are used to pass parameters into processes scheduled processes. These tables tend to grow, and are rarely purged. Generally, once created a run control is not deleted.  When operator accounts are deleted, the Run Controls remain, but are no longer accessible to anyone else.

I have worked on systems where new Run Controls, whose IDs contain either a date or sequence number, are generated for each process. The result is that the Run Control tables, especially child tables, grow quickly and if not regularly managed will become very large. On one system, I found 18 million rows on one table!

RECNAME         FIELDNAME            NUM_ROWS     BLOCKS
--------------- ------------------ ---------- ----------
TL_RUN_CTRL_GRP RUN_CNTL_ID          18424536     126377
AEREQUESTPARM   RUN_CNTL_ID           1742676      19280
AEREQUESTTBL    RUN_CNTL_ID            333579       3271
XPQRYRUNPARM    RUN_CNTL_ID            121337       1630
TL_TA_RUNCTL    RUN_CNTL_ID            112920        622
…

I have written a simple Application Engine process, GFC_RC_ARCH, that purges old Run Controls from these tables.  It can be downloaded from my website.

Run Control records are easily identified. They are characterised by:
  • the first column of these tables is always OPRID, and the second is either RUNCNTLID or RUN_CNTL_ID,
  • these two columns are also the first two columns of the unique key,
  • the Run Control tables appear on pages of components that are declared as the process security component for that process.
I have decided that if the combination of OPRID and RUN_CNTL_ID does not appear in the process scheduler request table, PSPRCSRQST, then the Run Control record should be deleted. Thus, as the delivered Process Scheduler Purge process, PRCSPURGE, deletes rows from the Process Scheduler tables, so my purge process will delete rows from the Run Control tables.

I have chosen to make these two Application Engine processes mutually exclusive, so the Process Scheduler will not run both at the same time, but that configuration cannot be delivered in an Application Designer project.

1 comment:

ropatel said...

Hey Dave thanks for the great article and AppEngine.

I do believe there might be one additional method for purging rows from the run control table when the associated user profile is deleted.

When a user profile is deleted, there is PeopleCode which will use the tables PSRECDEFN and PSRECFIELD to find any additional records which have OPRID or ROLEUSER as a key field on the record. These records are then aggregated and any rows which match the OPRID, being deleted, are also purged.

Therefore to take part of this existing process, you would have to specify OPRID or ROLEUSER as a key field on your run control table. When the user profile was deleted, so too would the associated rows in your run control record.

As an additional note: You can specify that the table not be included in this purge process by adding it to a list of tables to skip. The navigation to do so follows: PeopleTools - Security - Security Objects - Tables to Skip