CODEWITHSUNDEEP
db2sequence
Blake
Blake

Reputation: 1123

Creating a DB2 sequence with a specific START WITH value

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?

Upvotes: 1

Views: 18848

Answers (2)

Shiva Komuravelly
Shiva Komuravelly

Reputation: 3280

After a great difficulty I was able to figure this out with DB2 syntax and here we go

CREATE PROCEDURE SEQ_NAME
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN ATOMIC
DECLARE MAX_VAL_NO INTEGER;
SELECT MAX(COL_NAME)+1 INTO MAX_VAL_NO FROM TABLE_NAME;
execute immediate 'CREATE SEQUENCE SEQ_NAME NO MAXVALUE NO CYCLE START WITH '|| MAX_VAL_NO;
END
GO

Could any one tell me why should we use LANGUAGE SQL and DYNAMIC RESULT SETS 1

And what is the syntax used here, frankly speaking I really dont have an Idea but I hit it through trial and error method. Eagerly waiting to know what is the syntax is it either ANSI C or some other.

Appreciate if you could answer this. Also provide some links for good study.(Not regular IBM links)

Upvotes: 0

James Anderson
James Anderson

Reputation: 27478

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

Upvotes: 4

Related Questions