Tags: alter, behavior, bigint, column, create, database, db2, default, generated, identity, keycol, mysql, null, oracle, sample, sql, statement, statements, table

Alter statement for Identity Column

On Database » DB2

6,176 words with 4 Comments; publish: Thu, 22 May 2008 15:26:00 GMT; (250109.38, « »)

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

All Comments

Leave a comment...

  • 4 Comments
    • 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

      #1; Thu, 22 May 2008 15:27:00 GMT
    • 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

      On Dec 5, 8:35 pm, Serge Rielau <srie....db2.todaysummary.com.ca.ibm.com> wrote:

      > UDBDBA wrote:

      >

      > 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

      #2; Thu, 22 May 2008 15:28:00 GMT
    • 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

      DB20000I The SQL command completed successfully.

      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_1_ALL"

      DB20000I The SQL command completed successfully.

      delete from sample.id

      SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result

      of a

      query is an empty table. SQLSTATE=02000

      insert into sample.id (dwcontractid) values (1001)

      DB20000I The SQL command completed successfully.

      alter table sample.id alter keycol restart with 100

      DB20000I The SQL command completed successfully.

      insert into sample.id (dwcontractid) values (1002)

      DB20000I The SQL command completed successfully.

      select * from sample.id order by 2

      KEYCOL DWCONTRACTID

      -- --

      1 1001

      100 1002

      2 record(s) selected.

      alter table sample.id alter keycol set cache 100000

      DB20000I The SQL command completed successfully.

      insert into sample.id (dwcontractid) values (1003)

      DB20000I The SQL command completed successfully.

      select * from sample.id order by 2

      KEYCOL DWCONTRACTID

      -- --

      1 1001

      100 1002

      100100 1003

      3 record(s) selected.

      alter table sample.id alter keycol restart with 999

      DB20000I The SQL command completed successfully.

      alter table sample.id alter keycol set cache 100000

      DB20000I The SQL command completed successfully.

      insert into sample.id (dwcontractid) values (1004)

      DB20000I The SQL command completed successfully.

      select * from sample.id order by 2

      KEYCOL DWCONTRACTID

      -- --

      1 1001

      100 1002

      100100 1003

      1 1004

      4 record(s) selected.

      Thank you.

      Vijay

      #3; Thu, 22 May 2008 15:29:00 GMT
    • Vijay,

      OK, I agree that is not working properly. Can you open a PMR?

      If support sends you around refer them to me.

      Cheers

      Serge

      Serge Rielau

      DB2 Solutions Development

      IBM Toronto Lab

      #4; Thu, 22 May 2008 15:30:00 GMT