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-28
Considerations for ALTER TABLE
INSERTs will fail with ERROR[8934] as well, but the CURRENT_VALUE of the SG Table
is updated for every failed INSERT. If an artificially low cycle range was created by
having the MAXVALUE set to a value lower than the natural maximum allowed for the
data type, the ALTER TABLE ALTER COLUMN SET MAXVALUE option can be used
to raise the maximum, including up to the natural maximum allowed for the data type.
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.
IDENTITY column and redefinition timestamp
When the MAXVALUE or the INCREMENT BY attribute is altered, the redefinition
timestamp is updated for the base table that contains the IDENTITY column as well as
for the SG Table associated with that IDENTITY column. The following example
illustrates this behavior:
control query default SEQUENCE_GENERATOR_CACHE '1';
--- SQL operation complete.
control query default AUTOMATIC_RECOMPILATION 'ON';
--- SQL operation complete.
control query default RECOMPILATION_WARNINGS 'ON';
--- SQL operation complete.
create table T115T009 (id_key int unsigned GENERATED BY DEFAULT AS
IDENTITY (MINVALUE 1 MAXVALUE 2)
, b int
, c int);
--- SQL operation complete.
prepare s1 from insert into T115T009 values (DEFAULT, 1, 1);
--- SQL command prepared.
execute s1;
--- 1 row(s) inserted.
execute s1;
--- 1 row(s) inserted.
-- Two successful executes and the third execute Must get
--"*** ERROR[8934] The MAXVALUE for the sequence generator has been