Purging Data

Our company is just starting down the road of deciding how and when to purge data to tape. Any insite from someone has already started this process would be a great help. We currently are on Xe on a AS400 using DB2.

After talking with my Team Leader the real question he would like help on is when to start purging the data. Our users want to know how big can the files get before we start taking a performance hit.

Hi Heather,

I took part in an extensive 'Purging' exercise at my last company. The question you've asked is fairly difficult to answer regarding the how big files can get . However, hope this helps you move in the right direction.

I had the advantage of working for a site that had two OneWorld installations - 1/ Xe live for a number of years 2/ 8.10 live for only a year or so.

The Xe installation had no housekeeping done in the time it was live so we initially did some purging as a 'one off' hit to get the table sizes down. I remember seeing some tables around the 50 million records mark. This project involved CNC, AS400 tech and some Apps resources to establish what could be hit. Some of the purging was 'system' related e.g. Submitted jobs; Work Center Messages; Log files. Other purging was application specific.

Once the initial hit was done and this bought down storage considerably on the AS400, performance naturally improved across the board. The next exercise was to schedule these purges. Most of this was done at an AS400 level (AS400 scheduler; SQL Scripts; RPG routines).

The 8.10 installation was a different story. This supported multiple countries and hence more data and more load on the AS400. Again, while I was still with the company we performed a 'one off' hit. A lot of system specific purges were scheduled (WSJ; Work Center Messages; Log files etc.). I have left the company now but I gather they are evaluating some purging software - 'Arctools'.

I guess the message I'm trying to get across in all this... is that you should start thinking about purging as soon as possible and not wait for performance/load issues.

As a starting point, get your AS400 bods to produce a weekly report showing table sizes (in size order!).

Hope this helps!

Best Regards,

Sanjeev

Long ago we established guidelines for how long we want to keep SO's, WO's and PO's after they have been closed, and purge them on a semi-annual basis. We also started purging our G/L (F0911) this year and will continue to do that on an annual basis -- again with an established guideline for the number of years of history to be kept online.

Note that the purges really only move the data to a purge table, so actually your total database size will increase, at least temporarily. After you do the purge, you'll want to reorganize your database to reclaim the space from the purged records.

We use the standard purge programs (R4801P and R09911) for WO's and G/L. I wrote SQL scripts to purge the SO's and PO's, since the JDE-supplied purge routines were too fragmented and didn't keep all order-related records in the same state (active or purged).

I agree to start setting up your data retention guidelines as soon as possible and then test your purge routines in a test environment. Once you're satisfied with the results, apply the purges to your production on a regular basis.

Dave,

I've been doing a little research on the list to find anything I can about purging the F0911 table.

If you have time, please answer a couple of questions for me. You say that you use the standard JDE program. Do you use just that program or do you also use the programs to purge the A/P and/or A/R records as well? If not, have you had any issues when querying on the records that have been summarized in the G/L?

We were purging only the F0911 when we were on XE. I have inherited the process now that we've moved to 8.10. I want to make sure that we are not creating problems by using the same approach.

Thank you for taking the time to reply.

Hi there, I have worked with the purge program before as well as archive programs. The purge program may have a couple of issues with the approach:
1) the corresponding details in the AP/AR tables are not tied together during the GL purge. This causes integrity issues within the reporting system within JDE.
2) Make sure the summarization program is run prior to your purge, if you plan on purging the details of the F0911. It is important to keep the integrity between F0902 and F0911 for reporting purposes. There are also a couple of SAR's associated with this R09811 program.
3) Also, Audits and compliance issues arise if you are trying to get the information that has been purged to answer audits by state, federal govt's, etc.. The data maybe harder to retrieve due to offsite tapes that need to be restored.

The overall best approach, in my opinion, is to use an archiving solution that starts with the upstream applications like AR and AP prior to purging the F0911. Within an archiving solution there are typically business rules around these archives which maintain integrity within JDEdwards. For example, you would only purge AR records that were for a corresponding time period that were fully posted in the GL. Also, if you had received partial payment on an invoice to a customer, then the entire invoice details should also remain within production until payment was fully received for the entire invoice. These type of business rules are not supplied with the JDE purge programs so an archiving solution is typical (usually off the shelf).

Heather, have you purged the data yet?

Bryan,

Thanks for the response. Your insights will help us build our approach.

If anyone else has some experience with this, please share your thoughts.