Loading ...
Sorry, an error occurred while loading the content.

5043Re: PeopleSoft DBA Forum Question on best practices for process scheduler tables.

Expand Messages
  • Ethan Post
    Aug 22, 2014
    • 0 Attachment
      David, 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] <psftdba@yahoogroups.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.

       

      regards
      _________________________
      David Kurtz
      tel: +44 (0)7771 760660
      mailto:david.kurtz@...

       

       

      From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]
      Sent: 22 August 2014 18:03
      To: psftdba@yahoogroups.com
      Subject: PeopleSoft DBA Forum Question on best practices for process scheduler tables.

       




      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:
       
      PS_MESSAGE_LOG      10,607,291
      PS_MESSAGE_LOGPARM  14,001,290
      PS_CDM_LIST_ARCH    11,383,375
      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?
       
      peace,
      clark 'the dragon' willis







    • Show all 8 messages in this topic