If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Alter Table <tablename> Alter Column <columnname> Set Default 'abc' not working

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 01-30-04, 00:46
kos2003 kos2003 is offline
Registered User
 
Join Date: Oct 2003
Posts: 9
Question 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
Reply With Quote
  #2 (permalink)  
Old 01-30-04, 01:08
Marcus_A Marcus_A is offline
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,196
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On