Re: Data Corruption Troubleshooting FMA

["Tom Jedrzejewicz @ San Pedro" <tjedrzejewicz@xxxxxxxxxxxx>]

Al Mac --

You could write a query that breaks at the order/seq, and does a COUNT
subtotal to an outfile.  A second query over the outfile identifying
items with count > 1 finds the erroneous order/sequences.

If you don't already know, you can ID what process is doing the
duplicates by adding a UNIQUE keyed logical file over FMA by order/seq
-- the program that is writing duplicates will fail, and voila, you have
found your problem program.  Note that the LF won't add the member
successfully until the data is fixed.

The hardest part is figuring out what is happening when the duplicates
are getting added.  You may need to add some locking logic to the
programs, or test for the dup key error on write and not allow the
update.

Good luck; this sounds like a fun one!

>>> MacWheel99@aol.com 04/13/02 07:59AM >>>
I have a wee problem in version V405CD mixed mode BPCS/400 & before I
program
this thing I was wondering if there is a better way to do it.  Perhaps
some
other BPCS site has already seen this phenomena & knows exactly what
causes
it.

We have non-indexed BPCS file FMA that lists what raw material
inventory is
needed for each factory order, and all programs access this data by
keyed
logicals against fields in different sequences.

In theory for each order # there should be sequence # 1 2 3 4 5 etc.
for each
of the different sub-component items involved.
but intermittently some orders are getting doubled up 1 1 2 2 3 3 4 4 5
5
etc. or triple 1 1 1 2 2 2 3 3 3 4 4 4 etc. same item, only some of the
item
info duplicated (other fields zero in the duplicate records)

First, I want to identify which orders are corrupted so that we can
kill them
& reissue them, or see if DFU delete excess is enough to uncorrupt
them.
Right now the end users stumble over corrupted orders & we never know
how
many other ones are out there that way.

Second, I want to identify which processes by which users lead to this
happening, since we do not know if it is a bug, improper responses to
abnormal termination, or human error.  The hope being that it is
practical to
put a stop to it.  We have been using BPCS for almost 15 years & until
recently this has been an occasional glitch easily solved, but our hit
rate
is on the rise, so management wants me to take the time to put a stop
to it.

I figured we could use my proposed list corrupted records
intermittently
between various standard user factory order management efforts to see
at what
point this problem pops into existance, or the volume out there
increases.

I recently learning some stuff about check constraints & wonder if
there can
be a business rule that says this is a no-no, then list all no-nos.

I could write RPG program to read entire file through the old RPG
cycle
looking for this scenario.
Make Order # control break L2 & Seq # L1
If I have NL2 NL1 that's a failure.

I do not see how to do this with query/400, except I wondering if I
created a
logical that says no duplicate keys allowed (in the logical) then
attempt
no-match physical file to its new logical to list entries in physical
not in
logical.  I like to do things in query/400 that non-programmer
co-workers can
then use as a guide to creating new stuff based on my showing the way.

Our disk space is a bit too tight to be adding much more stuff, so I
reluctant to explore trigger programs & journaling that would analyse
every
update to locate which ones are adding the garbage, but eventually I
may need
to learn how to do that.

MacWheel99@aol.com (Alister Wm Macintyre) (Al Mac)
_______________________________________________
This is the SSA's BPCS ERP System (BPCS-L) mailing list
To post a message email: BPCS-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/bpcs-l
or email: BPCS-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/bpcs-l.

THIS MESSAGE IS INTENDED ONLY FOR THE USE OF THE INDIVIDUAL OR ENTITY TO WHICH 
IT IS ADDRESSED AND MAY CONTAIN INFORMATION THAT IS PRIVILEGED, CONFIDENTIAL 
AND EXEMPT FROM DISCLOSURE UNDER APPLICABLE LAW.  If the reader of this message 
is not the intended recipient, or the employee or agent responsible for 
delivering the message to the intended recipient, you are hereby notified that 
any dissemination, distribution, copying, downloading, storing or forwarding of 
this communication is prohibited.  If you have received this communication in 
error, please notify us immediately via email and delete the message from your 
computer files and/or data base.  Thank you.