Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 Alter Table command - default value

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
chan2004

New User


Joined: 28 Mar 2007
Posts: 16
Location: India

PostPosted: Thu Sep 13, 2007 6:05 pm    Post subject: DB2 Alter Table command - default value
Reply with quote

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
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Sep 13, 2007 6:55 pm    Post subject: Re: DB2 Alter Table command - default value
Reply with quote

chan2004 wrote:
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


You have to set the default value when you define the column.
Back to top
View user's profile Send private message
chan2004

New User


Joined: 28 Mar 2007
Posts: 16
Location: India

PostPosted: Fri Sep 14, 2007 11:04 am    Post subject:
Reply with quote

Hi Craig,

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

Thanks,
Chan
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Sep 14, 2007 7:32 pm    Post subject:
Reply with quote

chan2004 wrote:
Hi Craig,

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

Thanks,
Chan

No way that I know of.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat Sep 15, 2007 2:53 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Sat Sep 15, 2007 9:07 am    Post subject:
Reply with quote

dbzTHEdinosauer wrote:
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.


Actually CRAIG is correct, I fat fingered it when I registered and haven't bothered correcting it.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat Sep 15, 2007 8:47 pm    Post subject:
Reply with quote

Craig,

I stand corrected - about the spelling of your name.
Back to top
View user's profile Send private message
chan2004

New User


Joined: 28 Mar 2007
Posts: 16
Location: India

PostPosted: Mon Sep 17, 2007 10:21 am    Post subject:
Reply with quote

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 icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Data replication from multiple Db2 ta... kishpra DB2 1 Mon Mar 27, 2017 9:58 pm
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Customizing the ISPF HILITE Command Pedro TSO/ISPF 3 Fri Mar 03, 2017 11:49 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us