Hi,

Is there any way to rollback a sequence value in a procedure? I'm trying to run this code and it keeps incrementing the value of the sequence seq_id..

CREATE PROCEDURE NRG1.TEST (OUT msg VARCHAR(128))
RESULT SETS 0
MODIFIES SQL DATA
NOT DETERMINISTIC
LANGUAGE SQL
BEGIN

DECLARE id INTEGER DEFAULT -1;

VALUES nextval FOR seq_id INTO id;
IF id > 0 THEN
ROLLBACK;
SET msg = 'Rolling back value of sequence ';
RETURN;
END IF;

END
@



If I give -- db2 "VALUES prevval FOR seq_id" it keeps showing me the next value in the sequence so basically it is incrementing it even though I put 'rollback' in the procedure. Is there any way to achieve this? Thanks!!

No, that's not possible. Sequences are non-transactional. That's actually their big advantage otherwise they would not scale well for a large number of concurrent sessions.

thank you.. that helps!

The only option would be to reset/restart the sequence using the ALTER SEQUENCE statement. But then you would loose all guarantees that the sequence values are unique.

p.s: If you want to rely on sequences to have no gaps in the data being produced, sequences are the wrong thing anyway. Due to the internal caching, it may happen that sequence values are available for a transaction but never used by it. Those values will not be used by other transactions either.