How Sequence Generator behave when it is aborted?
If SL.NO 1 to 50 is loaded to Target table from sequence generator and suddenly workflow got aborted, during next run what will be current value? Either 1 or 51?
If depends on whether the sequence generator transformation is reusable or not and if you have configured number of cached values.
If it is non-reusable and you are not using number of cached values then it will start from 1 because due to the fatal error, at the end of the session, IS was not able to update repository with the final current value plus one.
If it is non-reusable but you are using number of cached values as 100, then it will start at 101 next.
If it's reusable and number of cached values is 100, then it will still behave just like scenario#2 above.
When using number of cached values option IS draws the number configured (100 in our example) and updates repository at the start of the session.
**Non-Reusable Vs Reusable **
Say you have a table that gets loaded by two different mappings and you are using sequence generator to load keys for this table.
If you use non-reusable sequence generator and run these mappings in parallel you will run into contention of the keys. Why? Because for a non-reusable sequence generator IS (integration service) starts with the value saved in the repo and at the end of the session updates the repo with the next available value.
When you use a combination of non-reusable and number of cached values (say set to 100), IS will start with the value saved in the repo and reserve or cache 100 values for this session and update the repo's next available value. If only 30 get used in the session, remaining 70 are discarded. This means 70 key values will never get used. If all 100 are used up then IS access repo again.
In case of reusable transformation IS mandates that you configure number of cached values.
The other major difference between reusable and non-reusable is you cannot reset the sequence for a reusable transformation.