Neoview Control Query Default (CQD) Reference Guide (R2.5)

8 Sequence Generator
This section describes these CQDs that are used by the sequence generator:
• “SEQUENCE_GENERATOR_CACHE” (page 37)
• “SEQUENCE_GENERATOR_CACHE_INCREMENT” (page 38)
• “SEQUENCE_GENERATOR_CACHE_INITIAL” (page 39)
• “SEQUENCE_GENERATOR_CACHE_MAXIMUM” (page 40)
SEQUENCE_GENERATOR_CACHE
Runtime controlsCategory
Specifies the number of values to fetch into the sequence generator cache from the sequence
generator object, when values are needed to insert rows into a table with an identity column.
If this value is set to greater than zero, the sequence generator only retrieves as many values
as specified by this CQD, and does not dynamically adjust the sequence generator cache
size.
Description
Any unsigned integer
The default value is ‘0’.
Values
At the default value of 0, an algorithm is used to dynamically increase the size of the
sequence generator cache as the number of rows being inserted increases. This algorithm
is based on the settings of the following CQDs:
• SEQUENCE_GENERATOR_CACHE_INITIAL
• SEQUENCE_GENERATOR_CACHE_INCREMENT
• SEQUENCE_GENERATOR_CACHE_MAXIMUM
Initial is the first set of values it retrieves, increment is the multiplier that it uses to retrieve
the next set of values, up to the maximum value. Once it reaches the maximum it retrieves
only the number of values specified by maximum.
For maximum efficiency when you are doing a large number of inserts via rowsets (a load)
and would rather not suffer the impact of the initial retrievals until the algorithm ramps-up
to the maximum, specify the maximum value through this CQD, or even a higher value
for a large load.
In fact, if you have the row count of the rows you are planning to insert, and do not expect
any failures, you could use the row count as the sequence generator cache value. Then the
sequence generator object is accessed only once and no sequences are wasted. But in that
case, ensure that a subsequent retrieval of the same size is not made. Otherwise, it results
in a very large gap. If the failure rate is very low, the gap may still be better than with the
default maximum. You would use this for specific load jobs.
Also, if for some reason you believe that the algorithm is leaving large gaps in the identity
column values, you could set this CQD to a low value.
Usage
Not applicableProduction usage
If the value used is larger than the SEQUENCE_GENERATOR_CACHE_MAXIMUM, then
while there would be a performance gain, there is the potential of wasting more sequence
values that are unused than the algorithm would have, when there are no more rows to
insert. This would leave large gaps in the identity column values. If the value is small, you
could be doing extra I/O to the sequence generator object to retrieve the set of values each
time, thereby impacting performance.
Impact
Set this CQD at the system level, but if used may make more sense at a service level to
influence certain loads/insert transactions, or even at a session level for loads.
Level
Instead of using this CQD, you could also manipulate the following CQDs to achieve your
objectives:
• SEQUENCE_GENERATOR_CACHE_INITIAL
• SEQUENCE_GENERATOR_CACHE_INCREMENT
• SEQUENCE_GENERATOR_CACHE_MAXIMUM
Conflicts/Synergies
SEQUENCE_GENERATOR_CACHE 37