I am aware that using GENERATED BY DEFAULT option for Identity Columns, DB2 provides me the ability to insert ID(mentioned in the sample code below) of my choice and If I don't provide ID then ID would be created by Server. But when I specify a value myself, Server doesn't caches that value and throws violation since I have those values inserted myself earlier and a Primary Key constraint defined. If I dont have Primary Key defined then it inserts duplicate values.
I need a solution such that I need to insert my own values as well as from Server if I dont specify value.
Is there any other way to overcome this situation ?
Kindly execute the sample code below in order to reproduce my problem.
Thanks in advance for your time and support.
create table test (
id integer primary key generated by default AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE),
insert into test (id, col) values ( 1, 'ab');
insert into test (id, col) values ( 2, 'cd');
insert into test (id, col) values ( 3, 'ef');
insert into test (col) values ( 'bc');
insert into test (col) values ( 'de');
insert into test (col) values ( 'fg');