| View previous topic :: View next topic |
| Author |
Message |
hellohatim
Beginner
Joined: 12 Apr 2007
Posts: 28
Topics: 7
|
 Posted: Tue Jul 15, 2008 1:21 am Post subject: Query on Self generating Identity |
 |
|
Hi,
We have a table (defination given below) when we try to insert records into it, the sequence numbers being generated are not always consecutive. They start in gaps which are not neccessaily in same amount.
| Code: | CREATE TABLE
DA72.T262901
(
REQ_ID INTEGER NOT NULL
GENERATED ALWAYS
AS IDENTITY
(
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2147483647
NO CYCLE
CACHE 20
NO ORDER
) |
Initially we tried a couple of inserts, these started with 1001 series. Next day, it started with 2001. Each consecutive day it had a gap of thousand. We had not given DEFAULT keyword for REQ_ID field.
Next we tried the insert query with DEFAULT, now it comes in sequence (each new insert with +1 on REQ_ID).
Just wanted to know why this is happening? We are trying to find out if there is a chance we would run out of numbers.
Regards,
Hatim
_________________
-Hatim M P |
|
| Back to top |
|
 |
vkphani
Intermediate
Joined: 05 Sep 2003
Posts: 483
Topics: 48
|
| Posted: Tue Jul 15, 2008 7:47 am Post subject: |
|
|
hellohatim,
If you want to include the data from the identity column or ROWID column when you load the unloaded data into a table, the identity column or ROWID column in the target table must be defined as GENERATED BY DEFAULT. To use the generated LOAD statement, remove the IGNOREFIELDS keyword and change the dummy field names to the corresponding column names in the target table.
The below link might be useful.
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNUGH13/2.12.2.3.4?SHELF=&DT=20020826194002 |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Tue Jul 15, 2008 9:39 am Post subject: |
|
|
Below is how I created a table that has rows added all day long. It does not skip. I think your CACHE 20 could be your problem.
CLAIM_ID INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 0 ,
INCREMENT BY 1 ,
CYCLE ,
MINVALUE -2147483648 ,
MAXVALUE 2147483647) ,
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
