Hi All,

I am seeing this behavior with IDENTITY COLUMN alter statements.

CREATE TABLE "SAMPLE   "."ID"  (
                 "KEYCOL" BIGINT NOT NULL GENERATED BY DEFAULT AS
IDENTITY (
                   START WITH +1
                   INCREMENT BY +1
                   MINVALUE +999
                   MAXVALUE +9223372036854775807
                   NO CYCLE
                   CACHE 100000
                   NO ORDER ) ,
                 "DWCONTRACTID" BIGINT NOT NULL )
                IN "STAGE_32K" ;

INSERT INTO SAMPLE.ID (KEYCOL) VALUES (1001);
DB20000I  The SQL command completed successfully.

SELECT * FROM SAMPLE.ID;

KEYCOL               DWCONTRACTID
-------------------- --------------------
                  1                 1001

 1 record(s) selected.

db2 "alter table sample.id alter keycol restart with 100"
DB20000I  The SQL command completed successfully.

db2 "insert into sample.id (dwcontractid) values (1002)"
DB20000I  The SQL command completed successfully.

db2 "select * from sample.id order by 2"

KEYCOL               DWCONTRACTID
-------------------- --------------------
                  1                 1001
                100                1002

 2 record(s) selected.

db2 "alter table vijay.id alter keycol set cache 100000"
DB20000I  The SQL command completed successfully.

db2 "insert into vijay.id (dwcontractid) values (1003)"
DB20000I  The SQL command completed successfully.

db2 "select * from vijay.id order by 2"

KEYCOL               DWCONTRACTID
-------------------- --------------------
                  1                 1001
              100                 1002
        100100                 1003

 3 record(s) selected.

--- Everything works good so far ----

Below is the problem!

db2 "alter table sample.id alter keycol restart with 999"
DB20000I  The SQL command completed successfully.

db2 "alter table sample.id alter keycol set cache 100000"
DB20000I  The SQL command completed successfully.

db2 "insert into sample.id (dwcontractid) values (1004)"
DB20000I  The SQL command completed successfully.

db2 "select * from sample.id order by 2"

KEYCOL               DWCONTRACTID
-------------------- --------------------
                  1                 1001
              100                 1002
        100100                 1003
                  1                 1004

 4 record(s) selected.

If you see above, the KEYCOL IDENTITY value is reset to 1, I set the
RESTART value to 999 and followed that alter statement with CACHE set
to 1000000. Whenever the two alter statements are executed back to
back, the IDENTITY column value is reset to 1 irrespective of the
RESTART VALUE.

Is this by design?

Thank you for time & effort in advance.

Vijay