Table with "Identity column" as PK returns Duplicate key after adding a column.

[pkoopmanpk]

I'm experimenting with the "Identity column" field. The generated column looks like this:

Code: Select all

CREATE TABLE DEVLIB/VBSTRG ( 
	TRGID BIGINT GENERATED ALWAYS AS IDENTITY ( 
	START WITH 1 INCREMENT BY 1 
	NO MINVALUE NO MAXVALUE 
	NO CYCLE NO ORDER 
	CACHE 20 ) 
. . . 

I had a table with some data which needed an extra column.

After adding the field and checking it into the IBM i, every insert kept returning a IO$STS = VE. Joblogs said there was a duplicate key.

Only after clearing data with a CLRPFM, the table started accepting new records.

Am I missing something? Or is clearing the table the only way in these cases?

[pkoopmanpk]

A quick search on CPYF and identity columns returns this tip.

1. Lock the table
2. Query, selecting the max-value of the identity column
3. Alter the table, using the identity column reset clause to set the next value the max-value plus one
4. Unlock the table

Could it be that Lansa forgets to do this when checking in an existing table?

[appbuilder]

You may need to reset the identity column value after adding a column.

Find the last identity column value and use
ALTER TABLE TEST ALTER TEST_ID RESTART WITH X;
where X is the next value.

[pkoopmanpk]

Hi Don,

After some further Googling, I agree. Thank you for the pointer in the right direction.

I think Lansa should do this automatically. But until they fix it, this is a good work around.

Best regards, Peter

[pkoopmanpk]

I use this simple script now to restart the identity column:

Code: Select all

begin
 for V as CUR cursor for 
    select 
       'ALTER TABLE VBSTRG ALTER TRGID RESTART WITH ' || trim(char(max(TRGID) + 1)) as STMT
    from 
        VBSTRG
  do 
    execute immediate V.STMT;
  end for;
end;