Referencing a sequence object

The sequence objects create values that get incremented under the application control. We can use NEXT VALUE and PREVIOUS VALUE commands to use sequence values. Alternatively, we can also use NEXTVAL and CURRVAL instead of NEXT VALUE and PREVIOUS VALUE respectively. These alternatives are only for compatibility with the previous versions of DB2 and are not recommended to be used.

Getting ready

We need the USAGE privilege on a sequence object to use the sequence. The creator of the sequence object automatically gets USAGE and ALTER privileges.

How to do it...

• We can use the NEXT VALUE command to get the next value generated by the sequence object:

VALUES NEXT VALUE FOR item_num;
VALUES item_num NEXTVAL;

• We can also use sequence values in SQL statements like INSERT, UPDATE, and so on:

INSERT INTO item_tbl(ITEM_NUM) VALUES (NEXT VALUE FOR item_num);
INSERT INTO item_tbl(ITEM_NUM) VALUES (item_num NEXTVAL);

• We can use the PREVIOUS VALUE command to get the previous...