Results 1 to 4 of 4

Thread: Sequence?

  1. #1
    Join Date
    Jul 2005
    Posts
    102

    Unanswered: Sequence?

    I just stepped into DB2 few months back and I have a question thats been haunting me for long. What are sequences? why do we need them? why it is such a big deal...

    I did search on the net but nothing gave me a good explanation of sequences. So I was wondering if DB2 gurus could help me find out this.

    Thanks in advance

    Anks

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    They are not a big deal, and you don't need them.

    They are used to get a "next available number" sequence for unique keys. But you can create your own table of "next available number" that you increment with an application program.

    You can also use indentity columns (but I would suggest "generated by default" instead of "generated always").
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2005
    Posts
    102
    Thanks Marcus!!

    Your responses are always so well written that it make things easier to understand.

    We often have to load the production data to our testing environment and everytime i have to check for sequences after the db2move to make sure that the sequences on testing are higher than production. Though i have never understood why we do that? Now my question is can I do "generated by default" on testing server and that will take care of sequences forever?

    Env: DB2 UDB v8.2, AIX

    Thanks again..

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you have "generated by default" or "generated always" you will have the same problem as sequences. When a db2move is performed, only the data gets transferred, and the "last number used" in the sequence or the identity column does not move and needs to be reset manually to avoid duplicates.

    One way to avoid this is to store your next available number in a table that gets moved along with the other data, so you don't have to manually reset the sequence or identity column.

    If you do that, you will want to create a table to get the "next available number" table and increment it in the following way.

    CREATE TABLE SEQUENCE_NBR
    (NEXT_AVAIL_TYPE INTEGER,
    NEXT_AVAIL_SEQ INTEGER)

    Then when a unique number is needed for inserting a row into an Invoice table:

    UPDATE SEQUENCE_NBR
    SET NEXT_AVAIL_SEQ = NEXT_AVAIL_SEQ + 1
    WHERE NEXT_AVAIL_TYPE = 1;

    (the update will get an exclusive lock the row so no other application can get it until the current application commits, and this prevents deadlocks)

    SELECT NEXT_AVAIL_SEQ INTO v_NEXT_AVAIL_SEQ FROM SEQUENCE_NBR WHERE NEXT_AVAIL_TYPE = 1;

    (this retrieves the number that you just updated to use in the insert. The exclusive lock is still in effect)

    INSERT INTO INVOICE VALUES (v_NEXT_AVAIL_SEQ, ..........);

    COMMIT;

    The commit will release the exclusive lock on the Invoice row in the SEQUENCE_NBR table.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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