5043Re: PeopleSoft DBA Forum Question on best practices for process scheduler tables.
- Aug 22, 2014David, you got a list of tables you recommend deleting from for that trigger?On Fri, Aug 22, 2014 at 12:52 PM, 'David Kurtz' david.kurtz@... [psftdba] <email@example.com> wrote:
Yes, they can (and IMHO should) be cleaned. Once the corresponding rows have gone from PSPRCSRQST the data in these tables isn’t a lot of use.
The first time you purge these table, you are going to be removing a lot of data, and delete doesn’t reset the high water mark on the table, and you won’t reclaim any free space. So, you might want to rebuild the tables with just the data to be retained (ie does exist on PSPRCSRQST).
Going forward, I can think of two options:
· Put a database trigger on delete on PSPRCSRQST to delete corresponding rows in other tables
· Add a DELETE WHERE NOT EXISTS to the App Engine that does the archiving.
BTW you might also want to check the BAT_TIMING% tables, but these contain useful performance information.
I also proposed a similar approach to run controls (http://www.go-faster.co.uk/scripts.htm#gfc_rc_arch) however, some people want to keep run controls for year end processes from year to year.
Hola, throbbing brain...
I am at a new client site and I have been doing some looking from the process request tables for the client at their request. For the most part, everthing seems fine for process purge process, and I only have a few suggestions there. HOWEVER, there are a couple of tables that concern me.
The process request table contains about 1000000 records with a 45 day purge. These tables, however, are ginormous:
They contain data from the beginning of time, when the system first went live. So, by question that I present to you is thus: Can these tables be cleaned? Kind of like Delete from table process instance does not exist in psprcsrqst? Would there be any issues with that?
clark 'the dragon' willis
- << Previous post in topic