Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Rollback of a sequence value

    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!!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by db2user24 View Post
    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.

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    thank you.. that helps!

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •