Hi, Patrik,
One technique used by RPG-II on S/36, etc., and carried over to OS/400, is to just do a SETGT to 99999999 (or equivalent) and then READP to find the "last" record in the given table with the unique keys; then you just add one and attempt to WRITE the new record, but with Error indicators set up to catch the possible "duplicate key" because someone got in there while you were in this logic. Then you basically start over with the SETGT, etc. "lather, rinse, repeat" ...
Another classic technique was to use a data area to hold the next number value for a given table or file, and RPG/400 or ILE RPG IV makes it easy, with the IN opcode, it can automatically lock the data area, read the current value, then you can add one and do an OUT to write it out again, and that unlocks the data area. Then you use that value you just obtained as the "unique key" for your database insert (WRITE in RPG).
Another similar technique is to have a single database file or table named NEXTNUMBER or similar, that has as a "key" the name of the table or file you want to store a next number for, and the "value" for that particular file.
Then, you can read by key from this file, opened for update, and the DB2 record locking prevents anyone else from updating that particular next number until you have updated it. So, read with lock, increment, write the record back out, which unlocks it. Eliminates having all those *DTAARAs, one for each file or table.
At some point, IBM added a 'sequence' object to SQL to do much the same; it creates a data area to hold the next number, but lets you manage it via SQL statements. See:
https://www.rpgpgm.com/2019/08/more-about-sql-sequences.html
and
https://www.ibm.com/docs/api/v1/content/ssw_ibm_i_73/sqlp/rbafysequence.htm
Hope that helps,
Mark
On Monday, February 5, 2024 at 05:07:55 AM EST, Patrik Schindler <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
midrange.com
