SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
2-122
Considerations for CREATE TABLE
Duplicates can be generated for an IDENTITY column if the IDENTITY column is
defined as GENERATED BY DEFAULT AS IDENTITY and you specify a duplicate
value for the column.
Sequence Generator Cache
The next values of the sequence generator can be cached in the operator that assigns
the generated value to the IDENTITY column. Caching ability is provided to reduce the
bottleneck at the SG table. The bottleneck can occur from many concurrent users
getting the next value from the sequence generator table. For large insert operations,
caching also reduces the overhead incurred from selecting and updating the sequence
generator table while obtaining the next values.
The sequence generator cache is not a global cache; it resides in the instance that
assigns the value for the IDENTITY column. The instance can either be a process
(ESP or MASTER) within the same statement or from another application inserting
values into the same table.
There are two types of sequence generator cache:
1. User-Defined cache: User-Defined cache value is specified by setting the Control
Query Default (CQD) SEQUENCE_GENERATOR_CACHE at compile time. If this value
is greater than its default of 0, then this is a pure override. Each cache size
obtained will be for the SEQUENCE_GENERATOR_CACHE size and does not change.
2. Adaptive Cache: The cache value dynamically changes at runtime based on the
load of the query. The following three CQD settings are used to manipulate the
cache value dynamically at runtime. The computation starts with the
SEQUENCE_GENERATOR_CACHE_INITIAL value, then multiplies it with the
SEQUENCE_GENERATOR_CACHE_INCREMENT and compares it to the
SEQUENCE_GENERATOR_CACHE_MAXIMUM. The adaptive cache is the default.
Example: assume the following settings:
SEQUENCE_GENERATOR_CACHE_INITIAL of “2”
SEQUENCE_GENERATOR_CACHE_INCREMENT of “10”
SEQUENCE_GENERATOR_CACHE_MAXIMUM of “20000”
SEQUENCE_GENERATOR_CACHE_INITIAL provides the initial value of “2”. A
cache of “2” is obtained. When the cache is exhausted, “2” is multiplied by the
SEQUENCE_GENERATOR_CACHE_INCREMENT of “10”, supplying a new cache of
“20” numbers. When the “20” values are exhausted, the current cache value of
“20” is multiplied by SEQUENCE_GENERATOR_CACHE_INCREMENT “10”, supplying
“20 X 10” = “200” new cache values. This dynamic calculation continues
incrementing the numbers from “2” to “20” to “200” to “2000” to “20000”. “20000” is
the SEQUENCE_GENERATOR_CACHE_MAXIMUM, so no further multiplication occurs.
The maximum is used from that point on to determine the cache size.
The default values for these CQDs are: