Re: Data Corruption Troubleshooting FMA

[thomas@xxxxxxxxxxx]

Al Mac:

On Sat, 13 April 2002, MacWheel99@aol.com wrote

> 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.

<snip>

> 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.

I'm not sure how much could be done through just Query/400, but
SQL provides a couple features that would help. Even if you don't
want to use SQL to do any of the work, you could still create a
logical file or two (in the form of SQL views) that would give
you or your users some information to work with.

Quite a while ago, I posted a basic example of how to use SQL to
locate and remove duplicate records very much like you describe.
Look at the Files section at http://zap.to/tl400 (or
http://tl400.tk to avoid any ads) for the "SQL Remove Duplicate
Records" directory.

There are two sample SQL source members -- FINDDUPES and
KILLDUPES -- that are suitable for RUNSQLSTM. The SQL is generic,
so you'd need to modify it to match the level breaks you need
since there's no way I could know what your key field names nor
table names would be. The members can be run separately.
KILLDUPES would only be needed if FINDDUPES indicated a problem.

If you see how those members do their work, it should be easy for
you to figure out how to create a view that could be used by
Query/400 or even RPG to do the work you wouldn't want to do with
SQL.

Tom Liotta

--
Tom Liotta
The PowerTech Group, Inc.
19426 68th Avenue South
Kent, WA 98032
Phone  253-872-7788
Fax  253-872-7904
http://www.400Security.com
___________________________________________________
The ALL NEW CS2000 from CompuServe
 Better!  Faster! More Powerful!
 250 FREE hours! Sign-on Now!
 http://www.compuserve.com/trycsrv/cs2000/webmail/