Could be because some records were written to Bad file or they have been filtered after the SEQ gen is assigned. Please verify the logic
No Logic its one to one mapping.
Reusable Sequence Generators
When you have a reusable Sequence Generator transformation in several sessions and the sessions run at the same time, use Number of Cached Values to ensure each session receives unique values in the sequence. By default, Number of Cached Values is set to 1000 for reusable Sequence Generators.
When multiple sessions use the same Sequence Generator transformation at the same time, you risk generating the same values for each session. To avoid this, have the Integration Service cache a set number of values for each session by configuring Number of Cached Values.
For example, you configure a reusable Sequence Generator transformation as follows: Number of Cached Values = 50, Current Value = 1, Increment By = 1. Two sessions use the Sequence Generator, and they are scheduled to run at approximately the same time. When the Integration Service starts the first session, it caches 50 values for the session and updates the current value to 50 in the repository. The Integration Service begins using values 1 to 50 in the session. When the Integration Service starts the second session, it checks the repository for the current value, which is 50. It then caches the next 50 values and updates the current value to 100. It then uses values 51 to 100 in the second session. When either session uses all its cached values, the Integration Service caches a new set of values and updates the current value to ensure these values remain unique to the Sequence Generator.
For reusable Sequence Generator transformations, you can reduce Number of Cached Values to minimize discarded values, however it must be greater than one. When you reduce the Number of Cached Values, you might increase the number of times the Integration Service accesses the repository to cache values during the session.
Please go through above help guide, decreasing the number of cached values may help in your case to reduce/eliminate discarded values.
You are right. This is next scenario that could happen.
To overcome this issue you can use the DB Seq Generator I believe.
thanks for the explanation. I m not running the sessions at a time.
Initial load is one time load. So it is not going to run any more .
CDC is continues running process.
In initial load table A completed . and i have final SEQ no 2345.
CDC started with same SEQ generator for TABLE A ,but its not continue the no 2346 on words. its picking different seq and starting from that point.
Sorry if i failed to explain . let me know is that my configuration for Sequence generator is wrong.
You can reduce no. of cached values to 2, however as explained in the help guide there will be a degrade in performance.
In your case, 1000 values will be cached at a time, if only one session is running and it processes 2350 records then the rest 650 ids are discarded and the next run will start with a cached value of 3001.
if i reduce no. of cached values to 2, i will get next value for the next session right?
did you ran the session with above implementation?