Neoview SQL Reference Manual (R2.5)

This allows for more available values in the cycle range for the internal sequence generator.
Inserts will be successful until the new maxvalue is reached.
NO MAXVALUE is not allowed. If you use NO MAXVALUE on the ALTER TABLE ALTER
COLUMN specification, an error will be raised:
>>alter table T115T002 alter column surrogate_key set NO MAXVALUE;
*** ERROR[1595] The MAXVALUE option for the IDENTITY column SURROGATE_KEY
must be a valid numeric value. NO MAXVALUE is not allowed.
--- SQL operation failed with errors.
Rules for ALTER TABLE ALTER COLUMN SET
The only options that can be altered for an IDENTITY column are:
• INCREMENT BY
• MAXVALUE
These rules apply:
• Only one IDENTITY column sequence generator option can be altered at a time.
• The INCREMENT BY option shall not be 0 (zero) or less than 0 (zero).
• The INCREMENT BY option shall not be greater than the maximum value of the data type
of the IDENTITY column.
• The INCREMENT BY or MAXVALUE options can be used only on an IDENTITY column.
• When the INCREMENT BY option is altered, only the property of the internal sequence
generator is altered. The current value in the internal sequence generator table is not altered.
The current value will have already been incremented using the increment value applied
for the previous insert operation. The next insert after the ALTER TABLE ALTER COLUMN
command will obtain the current value. The new INCREMENT BY value will then be applied,
creating a new current value. For more information on the current value of the internal
sequence generator table, see “Considerations for CREATE TABLE LIKE” (page 81).
• Only the INCREMENT BY or MAXVALUE options can be altered for an IDENTITY column.
• The MAXVALUE option shall not be 0 (zero) or less than 0 (zero).
• The MAXVALUE option shall not be greater than the maximum value of the data type of
the IDENTITY column.
• The MAXVALUE option must be greater than the current value of the internal sequence
generator current value for the IDENTITY column.
• The MAXVALUE option value cannot be less than the INCREMENT BY option value.
• A valid numeric value must be specified for the MAXVALUE option. NO MAXVALUE is
not a valid setting.
Recalibrating an IDENTITY Column
Recalibrating the value of the internal sequence generator may be necessary after a CREATE
TABLE AS or CREATE TABLE LIKE AS command. Use the ALTER TABLE ALTER COLUMN
recalibrate-options command to recalibrate an IDENTITY column:
• Recalibrate based on the current maximum value of the IDENTITY column and the
INCREMENT BY internal sequence generator option.
• Recalibrate to a user-specified value.
• Recalibrate to a user-specified value without a SELECT to obtain the maximum value of the
current maximum value of the IDENTITY column.
ALTER TABLE Statement 49