Neoview SQL Reference Manual (R2.5)

Recalibrate an IDENTITY Column Based on the INCREMENT BY Value
This method always performs a SELECT on the base table containing the IDENTITY column to
obtain the current maximum value of the IDENTITY column. This maximum value will be
incremented using the value of the INCREMENT BY internal sequence generator option. It will
be checked against the current numbering scheme of the INCREMENT BY option. If the newly
incremented maximum value is not consistent with the numbering scheme, the value will be
increased to the next value that would be consistent with the INCREMENT BY numbering
scheme.
Rules for Recalibrating Based on the INCREMENT BY Value
• The column to be recalibrated must exist and be an IDENTITY column in the table.
• If the table containing the IDENTITY column was newly created and has no rows added,
no calibration is necessary. The recalibration statement ends successfully, but no update
will be performed for the internal sequence generator current value.
• If the table containing the IDENTITY column has rows added, calibration is necessary. The
internal sequence generator current value column is recalibrated to a number larger than
the maximum value of the IDENTITY column in the base table.
— The current maximum value in the IDENTITY column is obtained by performing a
SELECT on the IDENTITY column. This maximum value is added to the INCREMENT
BY value of the internal sequence generator table. This incremented value is saved in
the current value of the internal sequence generator table.
— When the new current value is calculated for a RECALIBRATE command, the new
current value must not be greater than the maximum value allowed for the data type
of the IDENTITY column, or the MAXVALUE internal sequence generator option value.
An error is returned if the calculated new current value exceeds these maximums and
the current value of the internal sequence generator table remains unmodified.
— The INCREMENT BY numbering scheme will be honored when determining the new
value for the current value of the internal sequence generator. When the new current
value is calculated, it will be compared to determine if it matches the next logical number
that would be consistent with the numbering scheme for the INCREMENT BY value.
If the number is not consistent, then a number will be added to the current value to
make it consistent. This synchronizes the new current value to the INCREMENT BY
numbering scheme.
— This incremented value is saved in the current value of the internal sequence generator
table. This insures the internal sequence generator value will then generate unique
numbers for the IDENTITY column.
Recalibrate to a User-Specified Value
This method always performs a SELECT on the base table containing the IDENTITY column to
obtain the current maximum value of the IDENTITY column. This maximum value will be
compared to the user-specified value. The user-specified value will not be incremented using
the INCREMENT BY internal sequence generator option or adjusted to match its numbering
scheme.
Rules for Recalibrating to a User-Specified Value
• The column to be recalibrated must exist and be an IDENTITY column in the table.
• The user-specified recalibration value must be included, be a positive number, and must
not be greater than the maximum value allowed for the data type of the IDENTITY column.
In addition, the user-specified recalibration value must not be greater than MAXVALUE
option of the internal sequence generator.
• If the default specification type is GENERATED ALWAYS AS IDENTITY, the user-specified
recalibration value must be larger than the current maximum value of the IDENTITY column.
50 SQL Statements