|
View previous topic :: View next topic
|
| Author |
Message |
a027412
New User
Joined: 05 Jul 2007
Posts: 40
Location: Minneapolis, MN
|
|
|
|
I face a problem with DB2 Sequences object.
I have created SEQUENCE as follows
| Code: |
(SEQ_I DECIMAL(15 , 0)
GENERATED ALWAYS AS IDENTITY
(START WITH 1 ,
RESTART WITH 1 ,
INCREMENT BY 1 ,
CACHE 20 ,
NO CYCLE ,
MINVALUE 1 ,
MAXVALUE 999999999999999 ,
ORDER ) |
For the first time i loaded the table with 1000 rows. Then i emptied the table and again loaded 1000 rows. Now the SEQ_I which has to start from 1, it started from 1001.
My requirement is:
1: When i continue loading record without emptying the table, the SEQ_I must be a continous one.
2: When i empty the table, the SEQ_I i should start with 1.
Please help me on this. |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
How did you empty the table? |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| Have you read the manual? Emptying the table does not reset an auto generated sequence number. You need to either drop and recreate the table or reset the sequence number (I don't know if this can be done by a non-dba) |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
Also, if you are using an auto generated value as your primary key in the table, that is generally seen as bad practice.
We had to re-design a 400 million row table because some one had decided auto number primary keys were the way to go and even with indexing on the "natural" primary key, the table was completely unusable even for simple queries. |
|
| Back to top |
|
|
|
|
