Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009
    Posts
    14

    Unanswered: GENERATED BY DEFAULT AS IDENTITY (How to set/change sequence value)

    Hi,

    have a DB2 new database.
    Import things into my tables.
    My tables looks like this:

    Code:
    CREATE TABLE i4 (
    	ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1  INCREMENT BY 1 NO CACHE),
    	NAME VARCHAR(25)
    )
    I can insert into ID column to => fine (Ask not why..)
    So I will get some new IDs over time from other sources into my database.

    And now my problem:
    Code:
    CREATE TABLE i3 (
    	ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1  INCREMENT BY 1),
    	NAME VARCHAR(25)
    ) => OK
    ALTER TABLE ALTER TABLE i3 ADD PRIMARY KEY (ID) => OK
    INSERT INTO i3 (ID, NAME) VALUES (3, 'foo') => OK
    INSERT INTO i3 (NAME) VALUES ('bar') => OK
    INSERT INTO i3 (NAME) VALUES ('bar') => OK
    INSERT INTO i3 (NAME) VALUES ('bar') => ERROR: SQL0803N
    INSERT INTO i3 (NAME) VALUES ('bar') => OK
    He count 1,2,3 (error),4...

    Is there an option where I can set to bring this IDENTITY function to try increment until there is a valid value? (Sry, Im fastidious from MySQL used b4..)
    May it is ok, if I can set the current value of the ID sequence.
    But i dont now how.
    Can any help me?
    Tx!

    Greetings
    Desian

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    you can't force IDENTITY function to retry until there is a valid value.

    But you can raise ( or lower ) the value of the ID sequence by

    ALTER TABLE tablename ALTER COLUMN columnname RESTART WITH constant

  3. #3
    Join Date
    Dec 2009
    Posts
    14
    Thx.
    It work fine.

    Greetings
    Desian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •