CODEWITHSUNDEEP
sqldb2sequence
Sas
Sas

Reputation: 278

Difference in Assigned and Start value in a Sequence

I have created a sequence 

CREATE SEQUENCE CA_SEQUENCE_NUMBER_SEQ
start with 9000000000
increment by 1
nocycle
nocache;

The assigned and the start values are the same number. Kindly suggest me what could be the reason

please refer to the image for the clear picture.Evidence

this image details are from SYSSEQUENCES Data dictionary view

Upvotes: 0

Views: 621

Answers (2)

Paul Vernon
Paul Vernon

Reputation: 3901

I would assume that the sequence has been altered. E.g.

CREATE SEQUENCE CA_SEQUENCE_NUMBER_SEQ AS DECIMAL(31,0) start with 7000000000;
values nextval for CA_SEQUENCE_NUMBER_SEQ;
ALTER SEQUENCE CA_SEQUENCE_NUMBER_SEQ restart with 9000000000;

then SYSCAT.SEQUENCES would show

select NEXTCACHEFIRSTVALUE from syscat.sequences where seqname = 'CA_SEQUENCE_NUMBER_SEQ'"

NEXTCACHEFIRSTVALUE              
---------------------------------
                       7000000000.

  1 record(s) selected.

but after another call to values nextval for CA_SEQUENCE_NUMBER_SEQ, the NEXTCACHEFIRSTVALUE will update

values nextval for CA_SEQUENCE_NUMBER_SEQ;

1                                
---------------------------------
                      9000000000.

  1 record(s) selected.

select NEXTCACHEFIRSTVALUE from syscat.sequences where seqname = 'CA_SEQUENCE_NUMBER_SEQ'"

NEXTCACHEFIRSTVALUE              
---------------------------------
                       9000000020.

  1 record(s) selected.

Upvotes: 1

jmarkmurphy
jmarkmurphy

Reputation: 11473

It appears that the create statement happened some time ago as >700,000 numbers have been generated from the sequence. It is possible to restart a sequence from anywhere by issuing an ALTER SEQUENCE statement such as:

ALTER SEQUENCE CA_SEQUENCE_NUMBER_SEQ RESTART 7000000000

You can point it back to the starting value specified in the CREATE SEQUENCE statement by using just RESTART like this:

ALTER SEQUENCE CA_SEQUENCE_NUMBER_SEQ RESTART

Here is the relavant section of the documentation:

>>-ALTER SEQUENCE--sequence-name-------------------------------->

   .-----------------------------------------------.   
   V  (1)                                          |   
>----------+-RESTART--+------------------------+-+-+-----------><
           |          '-WITH--numeric-constant-' |
RESTART
Restarts the sequence. If numeric-constant is not specified, the sequence is restarted at the value specified implicitly or explicitly as the starting value on the CREATE SEQUENCE statement that originally created the sequence.
WITH numeric-constant
Restarts the sequence with the specified value. This value can be any positive or negative value that could be assigned to a column of the data type associated with the sequence (SQLSTATE 42815), without nonzero digits existing to the right of the decimal point (SQLSTATE 428FA).

Upvotes: 1

Related Questions