Results 1 to 10 of 10

Thread: DB2 Sequence

  1. #1
    Join Date
    Nov 2011
    Posts
    87

    Unanswered: DB2 Sequence

    Hi,

    DB2 9.7 FP 4 /WIN 2008

    I am trying to workout a sequence question (found in a db2 book). can you give some explanation that how it works please?

    A sequence was created with the DDL statement shown below:
    CREATE SEQUENCE my_seq START WITH 10 INCREMENT BY 10 CACHE 10
    User USER1 successfully executes the following statements in the order shown:
    VALUES NEXT VALUE FOR my_seq INTO :hvar;
    VALUES NEXT VALUE FOR my_seq INTO :hvar;
    User USER2 successfully executes the following statements in the order shown:
    ALTER SEQUENCE my_seq RESTART WITH 5 INCREMENT BY 5 CACHE 5;
    VALUES NEXT VALUE FOR my_seq INTO :hvar;
    After users USER1 and USER2 are finished, user USER3 executes the following
    query: SELECT NEXT VALUE FOR my_seq FROM sysibm.sysdummy1
    What value will be returned by the query?


    I am getting the answer 10. (i didnt have a test enviornment to test this).
    Thanks.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by db2mtrk View Post
    Hi,

    DB2 9.7 FP 4 /WIN 2008

    I am trying to workout a sequence question (found in a db2 book). can you give some explanation that how it works please?

    A sequence was created with the DDL statement shown below:
    CREATE SEQUENCE my_seq START WITH 10 INCREMENT BY 10 CACHE 10
    User USER1 successfully executes the following statements in the order shown:
    VALUES NEXT VALUE FOR my_seq INTO :hvar;
    VALUES NEXT VALUE FOR my_seq INTO :hvar;
    User USER2 successfully executes the following statements in the order shown:
    ALTER SEQUENCE my_seq RESTART WITH 5 INCREMENT BY 5 CACHE 5;
    VALUES NEXT VALUE FOR my_seq INTO :hvar;
    After users USER1 and USER2 are finished, user USER3 executes the following
    query: SELECT NEXT VALUE FOR my_seq FROM sysibm.sysdummy1
    What value will be returned by the query?


    I am getting the answer 10. (i didnt have a test enviornment to test this).
    Thanks.
    CREATE SEQUENCE my_seq START WITH 10 INCREMENT BY 10 CACHE 10 <-- Start value is 10
    User USER1 successfully executes the following statements in the order shown:
    VALUES NEXT VALUE FOR my_seq INTO :hvar; <-- value should be 10 (next value would be 20)
    VALUES NEXT VALUE FOR my_seq INTO :hvar; <-- Value should be 20 (next value would be 30)
    User USER2 successfully executes the following statements in the order shown:
    ALTER SEQUENCE my_seq RESTART WITH 5 INCREMENT BY 5 CACHE 5; <-- resets next value to 5
    VALUES NEXT VALUE FOR my_seq INTO :hvar; <-- Gets new value 5 (next value should be 10)
    After users USER1 and USER2 are finished, user USER3 executes the following
    query: SELECT NEXT VALUE FOR my_seq FROM sysibm.sysdummy1 <-- gets value 10 (next value should be 15)

    Andy

  3. #3
    Join Date
    Nov 2011
    Posts
    87

    Thank you

    Andy
    Thanks a lot. My guess is right and i am very happy.

    Regards,

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    db2mtrk,
    Sorry for this, but the other poster refuses to start another thread. He/she keeps using this one, after I reminded them twice to start a new one.

    Andy

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    john sergo's thread has been chopped out of here in to a separate thread: http://www.dbforums.com/db2/1692181-...l-network.html
    George
    Home | Blog

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ARWinner View Post
    ALTER SEQUENCE my_seq RESTART WITH 5 INCREMENT BY 5 CACHE 5; <-- resets next value to 5
    VALUES NEXT VALUE FOR my_seq INTO :hvar; <-- Gets new value 5 (next value should be 10)
    After users USER1 and USER2 are finished, user USER3 executes the following
    query: SELECT NEXT VALUE FOR my_seq FROM sysibm.sysdummy1 <-- gets value 10 (next value should be 15)
    I think you're forgetting the cache. The first access by user2 reserves (caches) 5 values of the sequence: 5, 10, 15, 20, 25. Although only one of them is used by user2, the next reference to the sequence in another session by user3 should start with 30.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It is a system wide cache, not a session cache. The cache is only lost if the database is deactivated. Try it in your test system.

    1) create the sequence
    2) session 1: get the next value
    3) session get the next value

    You will see that the sequences progress naturally, regardless of the session.

    Andy

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    On pureScale systems, each member has their own sequence cache, and for that reason getting the previous sequence value is not supported (will not return correct results).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Mar 2013
    Posts
    9
    oh shit...!!!pls dbforum administrator...pls make my discussion as new thread...mine is

    1.sql query for no of user in db2.

    2.sql query for table size in db2..

    its required urgent...this is not my thread...simply i am receiving these mails..

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by john sergo View Post
    oh shit...!!!pls dbforum administrator...pls make my discussion as new thread...mine is

    1.sql query for no of user in db2.

    2.sql query for table size in db2..

    its required urgent...this is not my thread...simply i am receiving these mails..
    Well quit posting to this thread. Simply start a new one.

    Andy

Posting Permissions

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