Results 1 to 2 of 2

Thread: Alter table

  1. #1
    Join Date
    Apr 2003
    Location
    Melbourne, Australia
    Posts
    53

    Unanswered: Alter table

    Hi All,

    Here's the scenario

    I have a table EMP with columns as, key with data type as integer and name being a varchar data type.

    Problem: I need to make the key column as a auto-generated field.

    Solution: I issued the alter table command as given below

    alter table db2admin.EMP alter column KEY SET GENERATED BY DEFAULT AS IDENTITY (START WITH 10, INCREMENT BY 1, NO CACHE )

    But, its giving a error as given below.

    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:

    SQL0104N An unexpected token "GENERATED BY DEFAULT AS IDENTITY (START W" was found following "alter column key SET". Expected tokens may include: "<space>". SQLSTATE=42601

    Please let me know

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That is because it is not allowed. The only things we can alter about columns is changing the maximum length of varchar and changing the generation expression if it already exists.

    Your only alternative is to:
    1) rename the table
    2) create a new table of the same name as you want it to be
    3) copy the data from the old table to the new
    4) drop the old table

    Andy

Posting Permissions

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