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

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").

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

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.