If you are using the IBM Load Utility it always uses a cache of
regardless of the value specified at CREATE time.
Also, the remaining values in the cache at utility execution time
and at DB2
shut-down are lost. For example, if you CREATE a new table with an
column specifying a cache of 20 (the default) and insert one row
you get a
value of 1. If you then shut down DB2 and re-start it, then do
insert, you get a value of 21 (you lost the 20 values cached by the
start of DB2). If you then do a load, the first value generated by
utility will be 41 (you lost the 20 values cached and un-used by
you loaded less than 1000 rows, then do a second load utility, the
value generated by the second utility will be 1041 (you lost the
and un-used by the first load utility). And so on and so forth.
Keep in mind that IDENTITY values are only guaranteed to be
uniquely, not consecutively. You can specify a cache of 1 at CREATE
attempt to keep the values consecutive, but utilities will still
non-consecutive values. And if you have high insert activity the
updating of SYSIBM.SYSSEQUENCES at every insert is probably not
Architect, DB2 Utilities
BMC Software, Inc.
From: Jackson, Phil [mailto:[login to unmask email]
Sent: Thursday, December 12, 2002 1:59 PM
To: [login to unmask email]
Subject: Re: identity columns
Sounds like perhaps you are having DB2 reserve X number of values -
the cache value
in the control panel - I think if checked, it will cache or reserve
20) ID values.
I created a table with an Identity Columns defined as Generate
(Start at 1, Increment by 1). When I load a row into the table the
was set to 1.
When I loaded another row the column was set to 1001. Then 2001,
so on. The inserts work find, i.e 1,2,3,4,etc. but the load doesn't
to have any rhyme or reason. I did 4 inserts and got 1,2,3,4. I
loaded a row and the next number was 5. I then loaded another row
number jumped to 1021.
Anybody have any clue as to what's happening.
Wakefern Food Corp.
[login to unmask email]
DB2-L webpage at http://listserv.ylassoc.com
The owners of the list can