MVSFORUMS.com

[sub]

The problem I have is the sequence numbers are getting lost because of the cache 20 in the sequence object.

I have a sequence object defined with cache 20. Only 1 job (1 batch program) uses this sequence object and it runs daily. This batch job inserts into the database the ID that was got by 'next value' of the sequence object. The problem i am seeing is that sequence numbers are lost between the job runs.
For eg.,
if the job runs on 1/2 and it had only 2 rows to insert and its creates the sequence id as 10301 and 10302

Then the next day 1/3 the job had only 1 row to insert, it creates the sequence id as 10321. The numbers between 10302 and 10321 are lost.


Then the next day 1/4 the job had only 10 row to insert, it creates the sequence id as 10341 to 10350. The numbers between 10341 and 10321 are lost.

There is no delete option available from this table so i can't say these missing id's were deleted. As per the IBM the sequence objects are kept in memory and are lost only if there is a system failure. This happens everyday in production environment. Has anyone ever faced this type of issue? Can anyone advise why this happens?

IBM manual:
CACHE integer-constant
Specifies the maximum number of values of the sequence that DB2 can preallocate and keep in memory. Preallocating values in the cache reduces synchronous I/O when values are generated for the sequence. The actual number of values that DB2 caches is always the lesser of the number in effect for the CACHE option and the number of remaining values within the logical range. Thus, the CACHE value is essentially an upper limit for the size of the cache. In the event of a system failure, all cached sequence values that have not been used in committed statements are lost (that is, they will never be used). The value specified for the CACHE option is the maximum number of sequence values that could be lost in case of system failure. The minimum value is 2. The default is CACHE 20. In a data sharing environment, you can use the CACHE and NO ORDER options to allow multiple DB2 members to cache sequence values simultaneously.

Thanks,
Sub

[papadi]

The values posted are a range greater than 20?

Even though there is no "delete" transaction, sql could still delete rows?

Were there any "system failures"?

What does a query show if run just before and just after this batch job?
_________________
All the best,

di

[sub]

The table is a master table where no one has access to delete rows. There is no delete sql in any of the programs for this table.

This is a production system which is very stable. No system failures. We see this unused sequence numbers issue almost every day.

After this batch job the value of MAXASSIGNEDVAL in sysibm.syssequences is incremented by 20 if there are less than 20 inserts into the table. If the inserts are more than 20 and less than 40 then the MAXASSIGNEDVAL is incremented by 40. Today there was no inserts so the MAXASSIGNEDVAL remained unchanged after the batch job. For eg., if previous days batch job had only 5 inserts and it used 21, 22, 23, 24, 25, the MAXASSIGNEDVAL is updated 40 (MAXASSIGNEDVAL was 20 before the batch job). The next day even if there are only 1 insert then the job uses 41 and MAXASSIGNEDVAL is updated to 60. The values from 26 to 40 remains unused/lost.

[NASCAR9]

sub, When you CACHE consider them gone. CACHE is for PERFORMANCE! If the number needs to to be in sequence, then create a sequence number within DB2 and get your number from it. We use this method for our Enrollment system(NO GAPS).
_________________
Thanks,
NASCAR9

[papadi]