1) We have 2 types of IDENTITY in DB2 i.e. GENERATED ALWAYS AS IDENTITY(does not allow INSERT/UPDATE) and GENERATED ALWAYS BY DEFAULT(Allows). I am using the former as:
CREATE TABLE t1(c1 SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1), c2 CHAR(1) NOT NULL);
Now, when I insert values for c2 & let us say I get 10 records. They come in sequence 1 - 10 which is fine. Now I delete 6-10 and INSERT 5 more and I get 1-5 and 11-15 which is also fine.
But if I try a failed insert like 5.5 for c2 5 times and then insert a correct value, I land up inserting 21. Is this not ridiculous. How to avoid this from happening?
Also, when I generate DDL using db2look for some tables IDENTITY is shown to RESTART with 40. From where could this have come?
Both these problems have dumbfounded me into a nitwit. Please help.