A former coworker has created an interesting, informative web site
on PeopleSoft topics: PeopleSoft Planet.
Check it out!
Today's topic is a simple tip. You may find tables that grow without
limit but contain information that is no longer needed. Although a
gigabyte of disk storage now costs only $1, these tables can cause
slower performance, longer backup and refresh times, etc.
You could manually delete rows from these tables, but they will just
start growing again. You could write a process to purge each table,
but that means you need to schedule and maintain multiple processes.
This is one of those situations in which a simple
App Engine program is a good solution. Create one AE purge process. Each
step can delete selected rows out of one table. It's best if you can
avoid using any run control parameters for this. Simply write your
SQL to use today's date (SYSDATE, GETDATE() or CURRENT DATE, depending
on your database, or meta-SQL constructs if you prefer). For example:
DELETE FROM PS_MY_TABLE
WHERE DATE_FIELD < (SYSDATE - 180)
This purges all rows that are more than 6 months old.
With one simple AE program, you can keep audit tables, worklists,
history logs and similar tables down to a manageable size. Just schedule the
program to run once a month and forget it.
