Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    14

    Red face Unanswered: How to change max value of identity column

    Hi,

    I've created table by mistake.

    CREATE TABLE TEST (
    DEF_ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 32672 NO CYCLE CACHE 20),
    PROPERTY_NAME VARCHAR(50) NOT NULL
    )


    It has MAXVALUE for IDENTITY But i was too small can I increase max or actually I would like to drop the max value is that posible?

    Thank you in advance.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The manual states which modifications can be done on an IDENTITY column: Modifying the generated or identity property of a column

    If you try an "ALTER TABLE ... ALTER COLUMN ... SET GENERATED BY DEFAULT AS IDENTITY ( MAXVALUE 65536 )", you get an error SQL0190. The explanation for that error states:
    * If SET GENERATED ALWAYS AS IDENTITY or SET GENERATED BY DEFAULT AS
    IDENTITY is specified, the column is already defined with a form of
    generation (default, identity, or expression) and there is no
    corresponding DROP in the same statement.
    Combining this with the above, you have to drop the identity property from the column and re-add.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Sep 2009
    Posts
    14
    Thank you for the replied.

    After re-add the identity. The identity is restart to 1 but i still have data in the table. Do you know how to restart identity?

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You use a different value in the "START WITH 1" clause.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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