Creating a DB2 sequence with a specific START WITH value

Question

In Oracle we can do it like this:

declare current_max_value NUMBER;
begin select last_number+1 into current_max_value from USER_SEQUENCES where sequence_name = 'HIBERNATE_SEQUENCE';
execute immediate 'CREATE SEQUENCE SEQ__NEW_SEQUENCE MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH '||current_max_value|| ' CACHE 20 NOORDER NOCYCLE';

Is there a DB2 equivalent?

Answer 1

DB2 has vaugely equivalent functionality.

If you just need to generate uninque keys then:-

CREATE TABLE MYTABLE (
     GENERATED_KEY                    BIGINT
        GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 100),
     MY_DATA                          VARCHAR(1000) ........... 

On the create table statement will accomplish this without too much fuss. Anytime a null value is encountered on an insert an new number will be generated.

If you need an actual sequence number to be used over several tables then:

CREATE SEQUENCE ORG_SEQ
 START WITH 1
 INCREMENT BY 1
 NO MAXVALUE
 NO CYCLE
 CACHE 24

will define a sequence you then use the "NEXTVAL" keyword anywhere you want the next number in you sql:

NEXTVAL FOR ORG_SEQ