One can do this by creating a before insert trigger on each table that replaces whatever value the user provides with the current one from a common sequence. Imagine that one wanted to maintain a unique sequence of values over multiple tables. Selecting the NEXTVAL CREATE SEQUENCE fred ANSWER COMMIT = SEQ# WITH temp1 (n1) AS - (VALUES 1 1 UNION ALL 2 SELECT n1 + 1 3 FROM temp1 4 WHERE n1 PRV (VALUES (PREVVAL FOR fred)) - SELECT * FROM temp1 WITH temp1 (nxt) AS => NXT (VALUES (NEXTVAL FOR fred)) - SELECT * 1 FROM temp1 WITH temp1 (prv) AS => PRV (VALUES (PREVVAL FOR fred)) - SELECT * 1 FROM temp1 WITH temp1 (n1) AS => NXT PRV (VALUES 1 - UNION ALL 2 1 SELECT n1 + 1 3 1 FROM temp1 4 1 WHERE n1 ID NXT (SELECT id - ,NEXTVAL FOR fred AS nxt 50 5 FROM staff WHERE id NXT PRV (VALUES (NEXTVAL FOR fred - ,PREVVAL FOR fred)) 10 9 SELECT * FROM temp1 And any reference to the next value will invariably cause the sequence to be incremented. Instead one can either retrieve the next or the previous value (if there is one).

There is no concept of a current sequence value. Sequence that doesn't change CREATE SEQUENCE biggest_sale_to_date SEQ# VALUES ASSIGNED AS INTEGER = START WITH 345678 345678, 345678, etc.

This can be useful if wants to have a constant that can be globally referenced: If the increment is zero, the sequence will stay whatever value one started it with until it is altered. The only sequence attribute that one cannot change with the ALTER command is the field type that is used to hold the current value. MINVALUE -1000 MAXVALUE +1000 NO CACHE NO ORDER CYCLE Likewise, one can alter a sequence in much the same way as one would alter the status of an identity column:Īlter sequence attributes ALTER SEQUENCE fred SEQ# VALUES ASSIGNED RESTART WITH -55 = INCREMENT BY -5 -55 -60 -65 -70 etc.

The options and defaults for a sequence are exactly the same as those for an identity column. INCREMENT BY 2 NO MINVALUE NO MAXVALUE NO CYCLE CACHE 20 ORDER A sequence is almost the same as an identity column, except that it is an object that exists outside of any particular table.Ĭreate sequence CREATE SEQUENCE fred SEQ# VALUES ASSIGNED AS DECIMAL(31) = START WITH 100 100 102 104 106 etc.

Db2 sequence number