SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-35
Considerations for ALTER TABLE
Generator is updated for every failed INSERT. If an artificially low cycle range is
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 then be used to raise the maximum, 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 are 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