SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
2-31
Considerations for ALTER TABLE
Recalibrating the Sequence Generator of an IDENTITY
column
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 might be
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 for 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 option. This
incremented value is saved in the CURRENT_VALUE of the internal sequence
generator table (SG 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. For more information, see Example of ALTER TABLE ALTER
COLUMN..RECALIBRATE on page 2-38.