I am wondering when using the NEXT VALUE FOR does it lock the data area in the background? I want to make sure it does not create duplicate sequence numbers.
Advertisement
Answer
For Db2 for i-Series, the reference documentation for sequence is here.
The reference documentation (for Db2-LUW) has what you need.
When a value is generated for a sequence, that value is consumed, and the next time that a value is requested, a new value will be generated. This is true even when the statement containing the NEXT VALUE expression fails or is rolled back.
However, if there are multiple instances of a NEXT VALUE expression specifying the same sequence name within a query, the counter for the sequence is incremented only once for each row of the result, and all instances of NEXT VALUE return the same value for a row of the result.
Notice that Db2 does not guarantee sequential numbers will be returned. So if your transaction rolls back, any previously consumed value from the sequence will not be returned in future – as long as the sequence is not reset at least.