Re: How to Undo an SQL Insert

[Frank Kolmann <Frank.Kolmann@xxxxxxxxx>]

Hi

Been a while.
Coding for the common case is simple.  Coding to catch things that may 
go wrong is much harder.
However the effort is well worth it.  You end up with self healing systems.
Often in case of problems all I needed to do was rerun the process.
Before journaling was common practice, in I cases like this I had two 
solutions.

1.  Treat the transaction file as the 'True' data. Then write rebalance 
programs to update the related data, report on any unbalanced records.

2.  Along with Header and Detail files have  a Trailer file.
Write the data to temporary files and the update logic never updates if 
the Trailer record is missing.
A checker program reports on Header/Details with missing Trailers and 
gives options to correct/remove.
I used this method for customers orders transmitted via hand held devices.

Journaling is magic, turn it on even if you dont use commit. It really 
helps to detect data errors and what program caused them.
When checking the journalled data it is fairly simple to create a DDS 
from the file plus the journalling fields then  copy with 
fmtopt(*nochk), then SQL to view the journalled data.
Have the system auto create the receivers. Put in place a procedure to 
delete receivers x days old.

Be careful with batch jobs that update a lot of records that you have 
the disk space to hold the journals.

Frank Kolmann


On 20/05/2022 4:20 am, Greg Wilburn wrote:
> Duane,
>
> The second insert should not ever fail, IF I have validated the data correctly prior to insert.  I experienced a failure during development/testing.  So I asked what I thought to be a simple question.  Turns out it's not so simple.
>
> While the two records created are "complimentary", there is no real connection between the two... they are really "offsetting" so that the change in Qty on Hand for that Company/Warehouse/Part nets to zero.  There are two "header" or parent files that ensure these stay in sync.  (They can get out of sync during abnormal ends to other ERP applications).  There is a resolution program that runs.
>
> IC Balance - Qty on Hand for a Company/Warehouse/Part (header)
> IC Detail - Qty on Hand for a Company/Warehouse/Part/Location (detail)
> IC Location Transaction Details (the file I'm working with) - transactions by Company/Warehouse/Part/Location
>
> So when something creates IC Location Transaction Details, the background programs in the ERP update the corresponding (parent) IC Balance & Detail files.
> Records are never deleted from this file - only created.
>
> So I think I'm OK w/out a solution.  Although I may try the journaling option mentioned by Charles.
>
> Greg