Re: Problem with GENERATED ALWAYS Identity column -- MIDRANGE-L

Charles,

I've had similar exprience with our CMS (Aldon CMS) where the table isrecreated, then distributed to the test library. The identity column inthe new table starts the column at 1, then CPYF command (run by CMS tocopy the data from the old table into the new table) inserts the rows,but retains the values of the identity column. Now the data in the newtable is out of sync with the identity column. Apparently CPYF commandis using the equivalent of the OVERRIDING SYSTEM VALUE clause of the SQLINSERT statement.

We had to write a little utility program that resets the identity columnin a table.


Brian Johnson

Wilt, Charles wrote:

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 havetwo copies ofthe file using the same identity. When you add records toboth files theycan end up with the same identity value because the filesaren't aware ofeach other. As you mentioned you are doing CPYFs betweentables which can
merge these records into the same file.
The INSERT is having a problem because you used a CPYF toinsert a recordwith the next available index value. The generator isn'taware that valueis already taken and it just attempts to increment for thenext value on theINSERT. But that value is already in the file so you get aduplicate 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 nextincrement value.
Paul