Transcript Purging Strategies with JD Edwards Software

Purging Strategies with JD Edwards Software
Northern California OAUG
Training Day 2007
January 17, 2007
Agenda
• Purging the old fashioned way
Overview
Prep
Process
• Purging today Using ArcTools by DCSoftware
Overview
Prep
Process
• Case Study
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
Purge Preparation
• Verify DASD Availability
• Required Setup Steps
Review Program Helps
DreamWriter Versions
Processing Options
• Data Summarization
• File Backups
Perform Integrity Tests
•
•
•
•
General Ledger Integrity Tests
Batch Integrity Tests
Accounts Payable Integrity Tests
Accounts Receivable Integrity Tests
Process Purges
• Process with No Users on the System
• Time Considerations
# of Records Being Purged
Start Time of Process
File Backup Timing
Archiving Data
• Preparing Data for Archiving
File Names
Library Locations
• Archiving Options
Tape
Separate Environment
Imaging
• Data Retrieval
• Future Release Considerations
Reorganize and Re-Test
• JDE Reorganization Program
Decide Which Files
Timing
• Re-Do Integrity Tests
From Old to New…
JDE - Existing Purge Functionality
•
Some purge programs (ex: AP, AR, GL)
Existing purge programs have issues…
•
P00Purge
•
Purge schemas
Handy, but limited functionality…
High 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.
From Old to New…
Problems with Standard JDE Purge Programs:
1.
2.
3.
4.
5.
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:
1.
2.
Too simple… not capable of building enough intelligence into the logic
Performance, stop / restart
“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 data
to exist in other modules.
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
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.
o Analyze complex JDE data relationships using simple queries (AS400 query
and/or SQL), run purges using menu driven user interface
o Top-down, bottom up, process of inclusion, process of elimination, iterative
approaches
o Keep related data together in both live and archive (a.k.a. JDE data integrity)
o Numerous functional area purges pre-defined
o Over 170 JDE customers on six continents
o Recommended by Denver as part of A9 upgrade strategy
Demo screens
• Insert screen shots or live demo here…
Purge Results
File
# Records
(Millions)
F0411
17.7
F0413
Analysis Job
Purge Job
Run Hours
Run Hours
17.3
# Purged
% Purged
6.8
6.87
39%
2.66
0.25
1.84
69%
F0414
17.5
0.9
6.92
40%
F0311
8.52
(Invoices)
1.1
2.47
29%
(Receipts)
1.25
2.7
32%
2.4
5.17
61%
0.01
0.013
24%
8.3
(Total)
F1201
0.0553
0.15
F1202
27.1
1.4
4.78
18%
F1204
13.3
0.35
1.33
10%
Purge Results
File
# Records
(Millons)
F67100
5.33
F67101
Analysis Job
Purge Job
Run Hours
Run Hours
N/A
# Purged
% Purged
1.1
3.54
66%
12.4
2.25
8.4
68%
F67102
24.2
4.1
16.7
69%
F67103
15.1
2.9
10.3
68%
F06136
1.07
0.12
0.94
88%
F06166
32.2
3.6
29.7
92%
F06156
3.47
0.17
2.3
66%
F0619
16.7
0.5
14.8
89%
F06278
15.1
15.15
100%
F0618
25.4
17.6
69%
N/A
n/a
2.75
Purge Results
# Records
(Millions)
File
F0911
Analysis Job
Purge Job
Run Hours
Run Hours
# Purged
% Purged
Notes
240
G/D/N/P/T/#/7
71
13.6
68
28%
900k of 1771k batches
V/K/M/W
5.5
9.3
9
4%
283k of 525k batches
I/R
6.3
4.7
17.5
7%
191k of 333k batches
Z/X/E
0.3
0.5
2.85
1%
11k of 19k batches
29
28
97.35
TOTAL
41% 1385k of 2648k batches
Reorg after Purging
ReorgWizard™
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
Overview of the solution
AEM modules for JDE E1
DST
MFG
FIN
AEM Engine
Driving
File
Outlines data to
be archived or
purged
Purge
Archive
Production
Database
Archive
Database
Demo screens
• Insert screen shots or live demo here…
Purge Results
Purge
Module
Table
Description
Receivables
F03B11
Invoice
F03B13
Receipts
F03B14
Receipt Detail
F0911
G/L
F0411
Total
Records
Archived
Records
Purged
Records
Archive
Time
Purge
Time
Gigabytes
Gained
1,481,130
597,614
597,614
5m 40s
45m
0.50
293,701
191,256
191,256
40s
5m
0.15
2,146,335
1,190,682
1,190,682
7m
1h 45m
0.90
10,087,257
3,680,649
3,680,649
1h 7m
3h 15m
2.60
Vouchers
371,442
172,634
172,634
4m
10m
0.15
F0413
Payments
195,372
128,035
128,035
1m
3m
0.20
F0414
Payment Detail
326,721
164,073
164,073
2m
2m 20s
0.05
F0911
G/L
6,406,608
349,558
349,558
20m
45m
0.25
GL
F0911
G/L
6,057,050
750,788
750,788
19m
1h
0.60
Purchasing
F4301
PO Header
532,480
193,466
193,466
1m
9m
0.15
F4311
PO Detail
922,263
382,657
382,657
4m
21m
0.60
F43121
PO Receptions
1,189,718
752,654
752,654
3m 30s
35m
0.75
F43199
PO History
858,243
235,774
235,774
3m
15m
0.30
Inventory
F4111
Cardex
5,694,074
503,611
503,611
5m 30s
1h
0.40
Sales Orders
F42019
SO Header History
247,682
245,849
245,849
2m
14m
0,30
F42119
SO History
4,056,853
1,871,622
1,871,622
16m
1h 25m
1.50
F49219
SO Tag History
2,692,554
1,179,491
1,179,491
8m
1h 5m
0.50
Payables
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
Questions ??????
Steve Colgrove, Platinum 21:
714.375.6642
[email protected]
www.platinum21st.com
David Shea, DCSoftware, Inc.:
508.435.8243 x200
[email protected]
www.arctools.com