Re: Atomic updates in the olden days

Hi all


Michael Sansoterra's article is good - it describes using the MI function _GENUUID - it used to use a type 1 version that, according to the IBM i documentation through 7.2, "does not exactly match the specification."


Starting with 7.3, you can now specify either type 1 or type 4 - the latter, again according to IBM i documentation, "is randomly generated and is consistent with the Internet Engineering Task Force (IETF) specification as described in the RFC 4122 documentation." Sansoterra's comment that UUIDs seemed to look quite the same as others in some ways - I think that might have had to do with everything being type 1 in 2014.


UUIDs (GUIDs ) are also described by Sansoterra and IBM's documentation as "...unique ... across all time and space..." This is true only on the system where they are generated. If you create a UUID on 2 systems and try to use them in the same application on one system, it is very possible to have collisions. It happened to us between an SQL Server and our IBM i.


Now the SQL GENERATE_UNIQUE does not generate a UUID - the former is 13 bytes long, the latter is 16. I don't remember if I've submitted and Idea to make a true UUID an option in GENERATE_UNIQUE. I just looked again, I see that this function creates in ascending order - UUIDs are not generated in any kind of order - SQL Server folks work with that all the time and don't seem to have issues. They're not used for sorting, rather, for referential integrity.


As mentioned, this unique ID combines an internal representation of UTC plus the serial number.


Cheers
Vern


On Mon, 5 Feb, 2024 at 11:50 AM, Rob Berendt <robertowenberendt@xxxxxxxxx> wrote:


To: midrange systems technical discussion

Never used this method but I've heard of it:
https://www.itjungle.com/2014/08/27/fhg082714-story01/
Basically you generate a UUID or a GUID and use that.

On Mon, Feb 5, 2024 at 5:08 AM Patrik Schindler <poc@xxxxxxxxxx<mailto:poc@xxxxxxxxxx>> wrote:

Hello,

for quite some time, the SQL interface of IBM i offers automatic
generation of a unique identity field value on INSERTs. I assume this is an
atomic operation, so concurrent INSERTs do not cause a duplicate key. Is
this correct? I also assume this facility works with the peculiarities of
commitment control and still not generating duplicate keys. Is this
assumption also correct?

Now, assume a really old release of the OS (V4R5) on a very low level
machine (150). No identity columns, and SQL is usually outperformed by
native I/O calls in the order of magnitude. Thus using SQL is generally
undesired at best.

How were "atomic" updates to PFs handled back then? Was this even possible?

For use cases without commitment control, I currently tie a LF with just
the identity field to the PF. Speaking in RPG lingo, I then do a SETGT and
a READP on this LF to obtain the highest ID value. If there is no BOF
error, I add 1 to the obtained value for the next WRITE. Of course, the
same applies to the C record I/O API calls as well.
I was also considering putting a loop-until-no-error around the WRITE,
incrementing the ID value in each iteration to catch concurrent writes in a
graceful way.

This clearly isn't atomic but works fairly well for a single user machine
like mine. ;-)

The interesting part starts when I want to use commitment control. Now
it's possible to have two WRITEs pending in the journal. Both uncommitted
records have obtained the same id value from said LF. Committing the second
transaction to the PF would throw a duplicate key error. How to recover
from this? As far as I understand, just incrementing the ID value and WRITE
again would not help because the erroneous WRITE with the duplicate key is
still in the journal, waiting to be committed. Issuing a ROLLBACK is
undesirable, because this would throw away successful, prior changes to
PFs. I know that it's programmatically possible to remove entries from the
journal but this feels to get messy pretty quick: How to just delete my own
erroneous WRITE from the journal and no other entries?

What was best practice back in the days for handling the described cases?

Thanks!

:wq! PoC

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related
questions.