Hi,
I have a table with identity column (generated always by default) and a primary key on it. I insert some values in this with default clause and some values after that with manul values.
But if i try inserting again with default, it starts at the last values generated by default.
Is there any way to avoid this behaviour apart from alter table option ?
The scenario with example and comments is mentioned below

Thanks,
Raj

Scenario:
CREATE TABLE TABB
(
USER_VIEW_ID INTEGER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1, INCREMENT BY 1, NO CYCLE, MINVALUE 1, MAXVALUE 2147483647, NO CACHE , ORDER ),
LABEL VARCHAR(256) NOT NULL,
CONSTRAINT XPKUSER_VIEW
PRIMARY KEY (USER_VIEW_ID)
)


INSERT INTO TABB(USER_VIEW_ID,LABEL)VALUES(DEFAULT,'TEST1');
VALUES IDENTITY_VAL_LOCAL();-->1

INSERT INTO TABB(USER_VIEW_ID,LABEL)VALUES(3,'TEST1');
INSERT INTO TABB(USER_VIEW_ID,LABEL)VALUES(4,'TEST1');
INSERT INTO TABB(USER_VIEW_ID,LABEL)VALUES(5,'TEST1');
INSERT INTO TABB(USER_VIEW_ID,LABEL)VALUES(6,'TEST1');
INSERT INTO TABB(USER_VIEW_ID,LABEL)VALUES(7,'TEST1');
VALUES IDENTITY_VAL_LOCAL();--7

INSERT INTO TABB(USER_VIEW_ID,LABEL)VALUES(DEFAULT,'TEST1');
VALUES IDENTITY_VAL_LOCAL();--2 ==> I would like to have this start at 8
INSERT INTO TABB(USER_VIEW_ID,LABEL)VALUES(DEFAULT,'TEST1');== >FAILS
VALUES IDENTITY_VAL_LOCAL();--3

No. If you decide to put your own values in the column, then it is your responsibility to ensure that they do not interfere with any generated values if you let DB2 assign the value.

I have written a stored procedure that reads the maximum value of the identity column and then resets the value to one value higher. You could do something like that, but DB2 will not automatically do it for you.

Thanks for the quick reply