Dear collective wisdom,
we use DB2 and EGL-programs on iSeries as the backbone of our web services. Our tables have ID column with unique index constraint. Typically option "generated always with identity" is not used. (Changing table design is not an option here.) Instead, we query current max key and then do the insert by adding 1 to the max key. Something like the following:
The problem is that if two users are concurrently doing the inserts, we may receive duplicate key error (SQL code -803). What would be a solid I am looking for a solid pattern for handling keys when inserting rows. As far as I know, EGL does not allow locking table between getCurrentMaxID() and insert. While we could write insert SQL manually and use select max(ID)+1 in the insert, then we would not know which ID was inserted.
The best idea so far is to try adding record, catch slqCode 803, call getCurrentMaxID() again and try insert again. Any better ideas?