RE: Problem with GENERATED ALWAYS Identity column

Paul,

What do you mean "This is a flaw in your design.  You have two copies of
the file using the same identity."

As I understand it, an identity is unique to a particular table object.  You're 
not thinking about a SEQUENCE object are you?  That can indeed be shared 
between objects.


Don't understand what you are saying about the INSERT and the CPYF.

With a GENERATED ALWAYS AS IDENTITY, every time a row is inserted no matter 
what the method, a new IDENTITY value is assigned.  The "generator" is always 
involved and thus should always know what the next value is.

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Paul Morgan
> Sent: Wednesday, August 03, 2005 2:38 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: Re: Problem with GENERATED ALWAYS Identity column
> 
> 
> 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
>