#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2005
    Posts
    16
    Rep Power
    0

    Identity_val_local()


    Hi,
    I have a strange problem with IDENTITY_VAL_LOCAL().I am using DB2 UDB Express 8.2 on Windows 2003. Sometimes when I want to insert a values in a table give me the error 'You are attempting to add an entry that already exists. ' - seem that IDENTITY is not incremented by 1.

    Which can be the cause ? the column is declared GENERATED BY DEFAULT AS IDENTITY( START WITH 1, INCREMENT BY 1, NO CYCLE,MINVALUE 1,MAXVALUE 2147483647, CACHE 20 )

    Thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2003
    Location
    Waco, Texas
    Posts
    636
    Rep Power
    27
    Not sure, but here is example DDL from one of my tables:

    (ITEM_NBR BIGINT NOT NULL GENERATED BY DEFAULT
    AS IDENTITY (START WITH 0, INCREMENT BY 1, NO CACHE, MINVALUE 0, MAXVALUE 9223372036854775807, NO CYCLE, NO ORDER),

    You aren't trying to actually supply a value when you insert, are you? DB2 should automatically provide that for you.

    identity_val_local() is used to get the ID of the row you JUST inserted.

    fv
    ...because that is the way we have always done it. We've been doing it like that for 80 Years! (How do we change that mindset?)
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2005
    Posts
    16
    Rep Power
    0

    Identity_val_local()


    I have the same syntax like you. Can be the problem that I am importing (moving) the database from another. I heard that in this situation it is possible that IDENTITY_VAL_LOCAL() to lose the order and is possible to give you a value which exist. Is this true?

IMN logo majestic logo threadwatch logo seochat tools logo