Faking an identity column
Older versions of OS/400 lack an automatically maintained identity column, to uniquely refer to a given record in a table. Depending on use case, it might be programmatically easier to pick a certain record by a a single value, instead of e. g. a multi-column key.
The most easy way to fake this is to define a column as numeric and derive a new value at insert time.
INSERT INTO mytable (id, field1, field2, field3)
SELECT MAX(id)+1, 'foo', 'bar', 'baz' FROM mytable
There is a certain chance that concurrently active processes derive the same ID and those INSERTs might fail with a duplicate key error. As long as this ID is not referenced by other tables as a primary key value, the easiest way to counter this is to programmatically try to insert the record in a loop[1] until the INSERT eventually succeeds. Similar ways can be done with classic positional API calls, and a LF defined only for the ID column.[2]
Other ways of obtaining IDs are pointed out briefly in Sequence Objects and Identity Columns.
Weblinks
- Sequence Objects and Identity Columns, datavail.com
- Subfile Templates, GitHub
Footnotes
- ↑ You should ensure that only a duplicate key error forces another iteration. Other errors can happen.
- ↑ An example can be found in the v_lodallpg.rpgle I wrote. Look for subroutine
INCLASTID.