Plese help:Sequence question?

[rose70]

Hi All,

I have created a sequence and my sequence number should not restart with 1 after reaching the maximum value.After reaching the maximum value 100 it should give error.Please reply to this query

please find below the code :
CREATE SEQUENCE pos AS INTEGER START WITH 1 INCREMENT
BY 1 MAXVALUE 100 CYCLE CACHE 5

ALTER SEQUENCE pos RESTART WITH 1 INCREMENT BY 1
MAXVALUE 100 CYCLE CACHE 5


Thanks to all.

 

[blom0344]

I am not sure what the exact question is. If you specify cycle instead of no cycle the sequence will loop (and you don't want that do you?)

Ties Blom

 

[MarcLodge]

Rose,

Are you associating the sequence to an insert to a table?

If so you might wish to look into using an identity column on the table coupled with a trigger to give an error once the specfic limit is reached eg.

Graeme Birchall's excellent DB2 cookbook has a number of good examples of this, starting on page 257 onwards. For the cookbook, click here:

http://mysite.verizon.net/Graeme_Birchall/cookbook/DB2V91CK.PDF

Hope this helps.

Marc

 

[macehill]

No need to create a trigger:-

C:\Program Files\SQLLIB\BIN>db2 create sequence test_seq start with 1 maxvalue 2
nocycle
DB20000I The SQL command completed successfully.

C:\Program Files\SQLLIB\BIN>db2 values (nextval for test_seq)

1
-----------
1

1 record(s) selected.


C:\Program Files\SQLLIB\BIN>db2 values (nextval for test_seq)

1
-----------
2

1 record(s) selected.


C:\Program Files\SQLLIB\BIN>db2 values (nextval for test_seq)

1
-----------
SQL0359N The range of values for the identity column or sequence is
exhausted. SQLSTATE=23522

C:\Program Files\SQLLIB\BIN>

 

[Truusvlugindewind]

Should be simple:
- use a sequence for the sequencing
- use a contraint for the "error when > 99" (page 22 in the cookbook mentioned eearlier)

Why wanna use a sequence for a constrain task?