Re: Problem with GENERATED ALWAYS Identity column

["Paul Morgan" <pmorgan@xxxxxxxxxxxxxx>]

Charles,

This is not a bug.  This is a flaw in your design.  You have two copies of
the file using the same identity.  When you add records to both files they
can end up with the same identity value because the files aren't aware of
each other.  As you mentioned you are doing CPYFs between tables which can
merge these records into the same file.

The INSERT is having a problem because you used a CPYF to insert a record
with the next available index value.  The generator isn't aware that value
is already taken and it just attempts to increment for the next value on the
INSERT.  But that value is already in the file so you get a duplicate record
error.

You can reset the next value for an identity column (above any value
currently in the file) with:

ALTER TABLE MELDBFQ.HPDOCU ALTER DHDOID RESTART WITH nnnn

Query SYSCOLUMNS in the QSYS2 library to determine the next increment value.

Paul