Right from the birth of DB2 generation of unique and increasing key values has been a huge challenge. The first solution was to create a table holding either the last used generated value or the next to be used. This table soon became a hotspot giving lots of deadlock and timeout problems. Then DB2 introduced so-called identity columns where the generation of the key became a part of the table definition. The identity columns gave birth to other problems like coming out of sync and creating holes in the key values. It was also difficult to copy data from one copy of the table to another because of clashing key values.

The latest attempt to get control of increasing key values are the so-called sequences, which I have written about, but in another context. The purpose of a sequence is to separate the key generation from the table definition. And the solution is like having a table holding the next or current key value, but without the deadlock/timeout problems. There is though still the problem around the next key value if data where the key value is used is copied from one table to another.

Recently I came up with an idea which might solve all the challenges with generated key values. I have not tried it in connection with OLTP solutions where the solution may have its challenges. Here is an example on how the idea works:

INSERT INTO MYTABLE (KEYCOLUMN, COLUMNA, COLUMNB)
VALUES((SELECT VALUE(MAX(KEYCOLUMN),0) + 1 FROM MYTABLE), 'COLUMNA-VALUE', 'COLUMNB-VALUE')

If you did not already know it it has been legal to use sub-selects as expressions almost everywhere since DB2 version 8. The requirement is that the sub-select must return exactly one value. In order to handle the initial situation with an empty table I have put a VALUE function around MAX(KEYCOLUMN) returning a max value of zero. The trick can also be used in a INSERT INTO SELECT ... FROM in the following way:

INSERT INTO MYTABLE (KEYCOLUMN, COLUMNA, COLUMNB)
SELECT ROWNUMBER() OVER() + (SELECT VALUE(MAX(KEYCOLUMN),0) FROM MYTABLE)
     , OTHER_COLUMNA, OTHER_COLUMNB
FROM OTHER_TABLE
WHERE ...

The advantage of the above idea compared to a SEQUENCE is that you do not need to think about how to set the initial value of the SEQUENCE if the table is born with data from another source where the key values are not generated using the SEQUENCE. Whether the solution performs worse than a SEQUENCE or not I do not know. DB2 may use a special access path when the largest value in the first column in an index is to be retrieved. If the above solution is to have good performance in all contexts you are required to create an index on KEYCOLUMN.