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-38
Examples of ALTER TABLE
Example of ALTER TABLE ALTER COLUMN..RECALIBRATE
1. This example shows how the recalibrate value is adjusted to the INCREMENT BY
numbering scheme:
CREATE TABLE T127T004 (a LARGEINT
GENERATED BY DEFAULT AS IDENTITY
(START WITH 9223372036854775804
INCREMENT BY 1
MAXVALUE 9223372036854775807
MINVALUE 100
NO CYCLE)
NOT NULL NOT DROPPABLE,
b INT UNSIGNED NOT NULL,
c INT UNSIGNED,
primary key(a) );
--- SQL operation complete.
alter table t127t004 alter column a recalibrate;
--- SQL operation complete.
-- The CURRENT_VALUE will be 9223372036854775806 after this
-- INSERT
insert into t127t004 values(default,1,1), (default,2,2);
--- 2 row(s) inserted.
select * from t127t004;
A B C
-------------------- ---------- ----------
9223372036854775804 1 1
9223372036854775805 2 2
--- 2 row(s) selected.
alter table t127t004 alter column a set increment by 2;
--- SQL operation complete.
alter table t127t004 alter column a recalibrate;
*** ERROR[1598] The new CURRENT_VALUE, 9223372036854775805
increment by 2 plus 1 to adjust to numbering scheme, for the
IDENTITY column, A, for the table, IC.ICALT.T127T004, will be
greater than the maximum allowed, 9223372036854775807.
2. This example illustrates the behavior of Recalibrate to a User-Specified Value with
SELECT:
CREATE TABLE T004 (colA LARGEINT
GENERATED ALWAYS AS IDENTITY
(START WITH 700
INCREMENT BY 2
MAXVALUE 800
MINVALUE 100
NO CYCLE)
NOT NULL NOT DROPPABLE,
colB INT UNSIGNED NOT NULL,