E9.0 UBE commitment control

[JohnDanter2]

Hi folks

I've had an issue for days which I've finally gotten to the bottom of but wanted to double check I am on the right track

I have a UBE under commitment control (Transaction Processing Enabled)
Am I right in thinking that if a set of DB transactions contains just ONE unique key violation, ALL other DB updates, deletes etc fail even when committed.

My UBE reads F41061 uploads from an excel file. I do various deletes and reinserts to F41061 (expire existing records by updating EXDJ to yesterday) using table IO (not B4100250)

At the end of my UBE I check an error flag and either commit or rollback the table IOs

I am experiencing an issue with updating existing F41061 records if there already is is a record there with yesterdays date.
So if we run my UBE today twice, then there will be a record already on the F41061 for MCU ITM AN8 UOM UORG EXDJ (EXDJ now = yesterday). If we then run it a second time for the same MCU ITM AN8 UOM UORG it tries to set EXDJ to yesterday again (already there) and so I get a unique key violation

So seen as EXDJ is part of the unique key, you have to copy, delete and reinsert F41061 wth an updated EXDJ

I think this ONE uniqiue key violation then totally ruins any other update, insert etc which may have been legit.

Am I right on this?

Thanks

John

 

[BOster]

Yes I believe so. When you try and commit, if any of the SQL transactions in the scope of the commit cannot be committed for any reason (like a PK violation) the entire transaction should be rolled back.

 

[Larry_Jones]

or you do a Fetch first to see if Entry with Key is there and depending on found or not do an update or a insert.

 

[JohnDanter2]

or you do a Fetch first to see if Entry with Key is there and depending on found or not do an update or a insert.

Thanks, yeah it's on the list of changes to do. Save it all going into error.

Thanks both