Forgot your password?
Document Actions
How to create and use sequence on DB2
Using Sequences
CREATE SEQUENCE SEQ1 AS INTEGER
By default the sequence number starts at one and increments by one at a
time and is of an INTEGER data type. The application needs to get the next
value in the sequence by using the NEXT VALUE function. This function generates
the next value for the sequence which can then be used for subsequent SQL
statements:
VALUES NEXT VALUE FOR SEQ1
Instead of generating the next number with the VALUES function, the programmer
could have used this function within an INSERT statement. For instance, if
the first column of the customer table contained the customer number, an INSERT
statement could be written as follows:
INSERT INTO employee VALUES
(NEXT VALUE FOR SEQ1, 'comment', ...)
If the sequence number needs to be used for inserts into other tables,
the PREVIOUS VALUE function can be used to retrieve the previously generated
value. For instance, if the customer number just created needs to be used
for a subsequent invoice record, the SQL would include the PREVIOUS VALUE
function:
The PREVIOUS VALUE function can be used multiple times within the application
and it will only return the last value generated by that application. It may
be possible that subsequent transactions have already incremented the sequence
to another value, but you will always see the last number that is generated
INSERT INTO invoices
(34,PREVIOUS VALUE FOR SEQ1, 234.44, ...)
Dropping sequence
DROP SEQUENCE <sequence_name>
Hacker Rangers - Gamification for CyberSecurity