DB2 Alter Table command - default value

chan2004 · New User Joined: 28 Mar 2007 Posts: 16 Location: India

Hi,

I am trying to run the following DB2 command to alter a column to set its default value as given below.

ALTER TABLE OA2A.TCK020A1
ALTER COLUMN CK020032 SET DEFAULT '0';

But this gives me an error:-

DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD DEFAULT, TOKEN DATA WAS EXPECTED

DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 103 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
X'00000067' X'00000000' SQL DIAGNOSTIC INFORMATION

-------------------------------------------------
The column is a character field
I want to have a default value of zero('0').

When I checked the manuals for the alter command, it didn't have any command to set a default value for an existing table.
My DB2 version is 7
Pls give me some advice to proceed further

Thanks in advance
Chan

Craq Giegerich · Posted: Thu Sep 13, 2007 6:55 pm

chan2004 · New User Joined: 28 Mar 2007 Posts: 16 Location: India

Hi Craig,

Isn't there any way i can alter the definition cause it will be a lot of rework

Thanks,
Chan

Craq Giegerich · Posted: Fri Sep 14, 2007 7:32 pm

dbzTHEdinosauer · Posted: Sat Sep 15, 2007 2:53 am

chan2004,

you need to work on your attention to detail - that's why your original create was erroneous.

Mr Giegerich's first name is Craq not Craig.

Craq Giegerich · Posted: Sat Sep 15, 2007 9:07 am

dbzTHEdinosauer · Posted: Sat Sep 15, 2007 8:47 pm

Craig,

I stand corrected - about the spelling of your name.

chan2004 · New User Joined: 28 Mar 2007 Posts: 16 Location: India

guys,

first of all i would like to mention is that the new change has been asked by the client after the declaration.
there's nothing i could have done

anyway, thanks for the quick reply

Chan