Alter statement for Identity Column

UDBDBA

unread,

Dec 5, 2007, 5:22:31 PM12/5/07

to

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

Serge Rielau

unread,

Dec 5, 2007, 9:35:38 PM12/5/07

to

UDBDBA wrote:
> 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?

I don't think so, but your sample is Frankenstein's monster.
E.g. your first insert statement won't work and you use different
schemata throughout the repro. So it's hardly compelling evidence of a
problem.

Could you provide a true repro?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

UDBDBA

unread,

Dec 6, 2007, 10:37:28 AM12/6/07

to

Hi Serge,

It's table sample.ID all the way. I wanted to substitute my name
"vijay" with "sample" for schema name in the posting...

What you see in the initial posting is the true repro.

Thanks!

Vijay

UDBDBA

unread,

Dec 6, 2007, 10:55:03 AM12/6/07

to

Hi Serge,

I have the script tested and here is the output, still the same
problem! I see the problem in V8 FP12 & V9 FP2.

DROP TABLE SAMPLE.ID