Purging Strategies with JD Edwards Software

1. Purging Strategies with JD Edwards Software Northern California OAUG Training Day 2007 January 17, 2007

2. Agenda • Purging the old fashioned way • Overview • Prep • Process • Purging today Using ArcTools by DCSoftware • Overview • Prep • Process • Case Study

3. The Old Fashioned Way • The Old Fashioned Way • Overview of Purge Process • Purge Preparation • Perform Integrity Tests • Process Purges • Archive Data • Reorganize Files • Re-do Integrity Tests

4. Purge Preparation • Verify DASD Availability • Required Setup Steps • Review Program Helps • DreamWriter Versions • Processing Options • Data Summarization • File Backups

5. Perform Integrity Tests • General Ledger Integrity Tests • Batch Integrity Tests • Accounts Payable Integrity Tests • Accounts Receivable Integrity Tests

6. Process Purges • Process with No Users on the System • Time Considerations • # of Records Being Purged • Start Time of Process • File Backup Timing

7. Archiving Data • Preparing Data for Archiving • File Names • Library Locations • Archiving Options • Tape • Separate Environment • Imaging • Data Retrieval • Future Release Considerations

8. Reorganize and Re-Test • JDE Reorganization Program • Decide Which Files • Timing • Re-Do Integrity Tests

9. From Old to New… JDE - Existing Purge Functionality • Some purge programs (ex: AP, AR, GL)Existing purge programs have issues… • P00PurgeHandy, but limited functionality… • Purge schemasHigh level design a ‘step in the right direction’. Schema presents purge approach at the ‘document level’… Schemas provide the ‘road map’ – the high level design and the approach to the purge of various functional areas. In many cases, higher level considerations, such as batch integrity, can further complicate the requirements.

10. From Old to New… Problems with Standard JDE Purge Programs: • Narrowly focused, resulting in too much data purged and integrity problems in the live environment (ex: GL purge) • Programs do not purge related files, also leading to integrity problems in the production database (ex: AP purge) • Archived data does not come close to becoming an environment, so archived data is difficult to use. • Extended file locks, inability to stop/restart • Inability to purge other files Problems with P00Purge or SQL: • Too simple… not capable of building enough intelligence into the logic • Performance, stop / restart

11. “Data Integrity” Textbook: - headers and details – the easy part JDE Specific – the hard part: - Batch – GL/AP/AR batches – purge partial batches… purge unbalanced - Cross functional area – AP/GL integrity, orders/cardex,PO/AP… - Integrity reports JDE programs are often expecting certain datato exist in other modules.

12. Beyond Purge Schemas • Purge Schemas – a start, but… • Standard purge approach, very basic • Mirrors those (few) purge programs that exist • Files, joins, selection criteria • But… beyond the schemas: • Related files considerations • “JDE Integrity” • “Gotchas” • Batch considerations • Which date field(s)? • Bad data? • Example – Accounts Payable

13. Intro to ARCTOOLS/400™and JDE Module™ • Purge and archive files without programming – any AS/400 database • Create purge routines as easily as writing a query • RPG code generator processes data safely and in the native database – no UBE’s • ‘Stop-by’ functionality allows purges to be run in increments • Throttle feature controls resource utilization. • Analyze complex JDE data relationships using simple queries (AS400 query and/or SQL), run purges using menu driven user interface • Top-down, bottom up, process of inclusion, process of elimination, iterative approaches • Keep related data together in both live and archive (a.k.a. JDE data integrity) • Numerous functional area purges pre-defined • Over 170 JDE customers on six continents • Recommended by Denver as part of A9 upgrade strategy

14. Demo screens • Insert screen shots or live demo here…

15. Purge Results

16. Purge Results

17. Purge Results

18. Reorg after Purging ReorgWizard™

19. Intro to ARCTOOLS Enterprise Manager™ • Based on ARCTOOLS/400 and JDE Module – same methodology, same approaches to analyses • Java front end • PL/SQL code generator (Oracle) or TL/SQL code generator (SQL Server) • Not a UBE. Runs in the native database  fast • Completely parameter driven – front end feeds inclusion, exclusion, iterative approaches • Useful for any other application on Oracle or SQL Server, simply through configuration

20. Overview of the solution AEM modules for JDE E1 DST MFG FIN AEM Engine Purge Driving File Archive Production Database Archive Database Outlines data to be archived or purged

21. Demo screens • Insert screen shots or live demo here…

22. Purge Results

23. Purging Strategies • Co-ordination With Files Within Each System (i.e. G/L) With Other Systems • Timing of Process • Backups • Users on the System • Deletion of Records (copy to alternate media) • Reorganization of Files • Future Release Considerations

24. Questions ?????? Steve Colgrove, Platinum 21: 714.375.6642 stevec@platinum21st.com www.platinum21st.com David Shea, DCSoftware, Inc.: 508.435.8243 x200 dshea@arctools.com www.arctools.com