Faking an identity column

From Try-AS/400
Jump to navigation Jump to search

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

If there is a certain chance that another process is concurrently active, it will derive the same ID and those INSERTs will 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. Note that using commitment control adds another layer of possible pitfalls here.

Similar ways can be done with classic positional API calls, and a LF defined only for the ID column.

Other ways of obtaining IDs are pointed out briefly in Sequence Objects and Identity Columns.

See also

Weblinks

Footnotes

  1. You should ensure that only a duplicate key error forces another iteration. Other errors can happen.