Hi,

I have a table and it has a column with GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20,
NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER) .The table data in that column (Generated always) are not in sequencly ordered,For example values are 20,25,26,28,31 etc instead of 20,21,22,23,24 etc .

I appreciate your help on this.

Each sequence number can only be allocated once so if you rollback a transaction after allocating a sequence there will be gaps.

Yes,Thanks for your response.But no transaction has been rollbacked. Please let me know is there anything we need to do for the same.whenever the data got inserted the value on the column(Generated always) getting random number ,not in sequence order

This discussion might be useful to you:
http://bytes.com/groups/ibm-db2/185464-generated-always-identity-clause

Thanks once again...Let me check that.

Hi,

By default DB2 caches 20 values for the identity for performance reasons. but When the database is deactivated this cache is lost, and thus the break in sequence????

if we use NO CACHE option also we may face same problem if you insert the data and then roll back performed .And If we use NO CACHE option the performance can degraded. is it???

Are you saying this issue only happens when the DB is deactivated? If so, then it’s something that I’d hope we don’t encounter in production (Unless it’s done outside of normal business operational hours), but what is the best way to prove this is what’s happening (Deactivate and Reactivate during a normal testing day?)..

An answer would be nice..thanks in advance