Get batch values for a sequence

To get a batch of values for a sequence, use the NEXT <integer expression> VALUES FOR statement.
NEXT <integer expression> VALUES FOR <sequence name>
The system returns the single value that is the earliest of a set of <integer expression> contiguous values available for the sequence. The two adjacent values from the batch differ by the increment value.
  • If the sequence increment is positive, the system returns the lowest value of the batch of values.
  • If the sequence increment is negative, the system returns the highest of the batch values.
  • If the sequence does not cycle and the requested batch size exceeds the number of available sequence values, the system displays an error.
  • If the sequence cycles and there are not enough values between the current base value and the endpoint of the cycle, the system wraps the base value and assigns the batch from that point, which results in wasting the values skipped to wrap.
  • If the batch size exceeds the cycle size of the sequence or the total number of possible sequence values, the system displays an error.

You might want to request batch values for a sequence if you are using ETL tools that require many sequence values and want to avoid the performance impact of requesting sequence values individually. Another advantage of requesting batch sequence values is that when you get values in advance of adding a table row, rows of related tables can cross-reference each other.