Effect of data sharing on sequence number caching

Db2 always assigns sequence numbers in order of request.

Members in a data sharing environment can use the CREATE SEQUENCE statement with the CACHE option to request a "chunk" of sequence numbers. These numbers become the members local cache. Since each member assigns cache from its local chunk, numbers may not be assigned in order across the data sharing group. Each value is guaranteed to be unique.

For example, the following CREATE SEQUENCE statement results in sequence SEQ1 being defined as a block of 20 cache values numbered 1 through 20:

Because each data sharing group member gets its own sequenced block of cache values, all values assigned to that member are from that block. If member DB2A requests the first cache value for SEQ1, it is assigned value 1. If member DB2B requests the next cache value, it is assigned value 21. When member DB2A requests another cache value, it is assigned value 2, and when member DB2B requests another cache value, it is assigned value 22. When a member's block of cache values is exhausted, the next available block of cache values is allocated.

In data sharing systems, if cache value sequence must be assigned in strict numeric order, then the NOCACHE option of the CREATE SEQUENCE statement must be specified.