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-47
Examples of ALTER TABLE
3. This example illustrates the behavior of “Recalibrate to a User-Specified Value NO
SELECT”:
CREATE TABLE T004 (colA LARGEINT
GENERATED BY DEFAULT AS IDENTITY
(START WITH 700
INCREMENT BY 2
MAXVALUE 800
MINVALUE 100
NO CYCLE)
NOT NULL NOT DROPPABLE,
colB INT UNSIGNED NOT NULL,
colC INT UNSIGNED,
primary key(colA) );
insert into T004 (colB,colC) values(1,1);
--- 1 row(s) inserted.
-- colA will have value '700';
select * from T004;
COLA COLB COLC
--------- --------- ----------
700 1 1
--- 1 row(s) selected.
-- Recalibrate the internal sequence generator
-- using the user-specified value with SELECT. This
-- will succeed as it is less than the maximum value
-- and greater than the MINVALUE and START WITH values.
-- The recalibrate will reset the CURRENT_VALUE in the SG Table
-- to 710.
alter table t004 alter column colA recalibrate to 710;
--- SQL operation complete.
-- The recalibrate does not perform a SELECT on table T004 to
obtain MAX(colA).
-- The recalibrate to value of 702 is a pure override. No error is
raised.
alter table T004 alter column colA recalibrate to 702;
--- SQL operation complete.
Notice the plus 1 that is done to calculate the new CURRRENT_VALUE; that is
because adding the INCREMENT BY 2 to 9223372036854775805 will result in an
odd number, whereas the sequence generator is defined to generate even values.
Hence the plus 1 is done to be consistent to the numbering scheme.