|Bits and Bytes of|
Indeed is it possible to experience gaps for the generated numbers. An example would be that numbers from 1 to 187 have been generated and the next time a new value from a sequence or for an identity column is obtained, the number 200 is returned. The reason - and the possible gap and its properties - can be guessed by taking a look at the syntax, either for a sequence (CREATE SEQUENCE) or for an identity column (as part of CREATE TABLE): Both have a caching option with the default of "CACHE 20".
For each new value the database system needs to increment the last assigned value and return it. Calls can come from possibly hundreds of concurrent transactions. Because the database system needs to be fault-tolerant, the last assigned value needs to be persisted in the sequence's metadata (system catalog SYSCAT.SEQUENCES). After a possible system crash, the database system could obtain the last assigned value and start from there. Writing out the current number to disk for each request is really expensive, hence a chunk of new numbers is cached by DB2. The default is 20 values (CACHE 20). When the database shuts down ("is deactivated") the last number assigned off that cache is not stored. When the sequence or identity column is used the next time after the database is started or activated, the number for the next value is read from the system catalog which possibly causes a gap between the previously generated to the new value.
Caching is used for performance reasons, not for beauty. And hence users have the choice of changing the sequence or identity column behavior to either "no cache" (causes disk I/O for each new value) or far bigger cache sizes with possibly larger gaps. With this and other options users can give sequences their own identity...