Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    9

    Question Unanswered: Alter Table <tablename> Alter Column <columnname> Set Default 'abc' not working

    Alter Table <tablename> Alter Column <columnname> Set Default 'abc' not working

    I tried to execute the below given SQL statement and was confronted with the following error in DB2 7.2

    1 .The table is test_emp in SQLP72 database.
    2. The column for which default is being set is emp_name, having data type varchar(50) Not Null

    SQL-statement:
    ALTER table SQLP72.test_emp ALTER column emp_name SET default 'xyz';
    -----------------------------------------------------------------------------
    ERROR:
    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 "default 'xyz'" was found following "column emp_name SET". Expected tokens may include: "<space>". SQLSTATE=42601


    I checked the syntax in DB2 reference manuals, andit seems correct. One interesting thing is that I'm able to alter the columns data type, but not the default value.
    Pls help me out at the earliest....
    Thanks in advance,
    Kos

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The ALTER TABLE statement modifies existing tables by:

    - Adding one or more columns to a table
    - Adding or dropping a primary key
    - Adding or dropping one or more unique or referential constraints
    - Adding or dropping one or more check constraint definitions
    - Altering the length of a VARCHAR column
    - Altering a reference type column to add a scope
    - Altering the generation expression of a generated column
    - Adding or dropping a partitioning key
    - Changing table attributes such as the data capture option, pctfree, lock size,
    or append mode.
    - Setting the table to not logged initially state.

    I don't see anything about altering the default of an existing column. The syntax you saw may be for adding a new column, not changing an existing one.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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