Page 1 of 1
Table with "Identity column" as PK returns Duplicate key after adding a column.
Posted: Tue May 14, 2024 12:49 am
by 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?
Re: Table with "Identity column" as PK returns Duplicate key after adding a column.
Posted: Tue May 14, 2024 1:02 am
by 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?
Re: Table with "Identity column" as PK returns Duplicate key after adding a column.
Posted: Tue May 14, 2024 1:05 am
by 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.
Re: Table with "Identity column" as PK returns Duplicate key after adding a column.
Posted: Tue May 14, 2024 1:22 am
by 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
Re: Table with "Identity column" as PK returns Duplicate key after adding a column.
Posted: Tue May 28, 2024 6:27 pm
by 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;