IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Change data types of a column in DB2 8.1


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
H Muraleedhar

New User


Joined: 03 Mar 2009
Posts: 12
Location: Chennai

PostPosted: Wed Mar 04, 2009 10:46 am
Reply with quote

Is there any way to change data types of a column in DB2 8.1?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Wed Mar 04, 2009 11:13 am
Reply with quote

Hello,

Many of my sites drop the column and re add it.
Back to top
View user's profile Send private message
H Muraleedhar

New User


Joined: 03 Mar 2009
Posts: 12
Location: Chennai

PostPosted: Wed Mar 04, 2009 11:22 am
Reply with quote

Is it possible to drop column in DB2 8.1 ( OS/390 ) . I think its not possible to drop a column. If it is possible please provide the syntax.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Wed Mar 04, 2009 11:46 am
Reply with quote

Hello,

I should have used more explanation. By drop and add i mean they backup the table, drop the table, modify the sql create table code, and then run the sql to re-create the table with the new column name.
Back to top
View user's profile Send private message
aishwarya_20

New User


Joined: 19 Nov 2008
Posts: 57
Location: pune

PostPosted: Wed Mar 04, 2009 12:04 pm
Reply with quote

Hi,

We can change the datatype of the table column as mentioned below :

ALTER TABLE <table-name> ALTER COLUMN <column-name> SET DATA TYPE VARCHAR(51)

Provided that there is not any view defined for that table and same column. If View is there then we need to drop the view and then alter the column.
Back to top
View user's profile Send private message
H Muraleedhar

New User


Joined: 03 Mar 2009
Posts: 12
Location: Chennai

PostPosted: Wed Mar 04, 2009 12:39 pm
Reply with quote

Hi Thank you Dick Scherer and Aishwarya.

Hi Aishwarya , I dont think you can change the Constraints ( eg: null , not null ) using the Alter column . You can change only the lenth of column by this .
Back to top
View user's profile Send private message
aishwarya_20

New User


Joined: 19 Nov 2008
Posts: 57
Location: pune

PostPosted: Wed Mar 04, 2009 12:42 pm
Reply with quote

Yes we can change. the constraints also. But the sequence of changing the constraints and changing the column length will be as follows:

ALTER TABLE <table-name> ALTER COLUMN <column-name> SET NOT NULL
ALTER TABLE <table-name> ALTER COLUMN <column-name>SET DATA TYPE BIGINT

Means first we shud change the constraint and then the length of the column.
Back to top
View user's profile Send private message
H Muraleedhar

New User


Joined: 03 Mar 2009
Posts: 12
Location: Chennai

PostPosted: Wed Mar 04, 2009 4:42 pm
Reply with quote

Hi This first syntaz dosent seems to be working . I am getting SQLCODE = -104, ERROR: ILLEGAL SYMBOL "NOT". SOME SYMBOLS THAT MIGH
BE LEGAL ARE: CYCLE, NOCYCLE, ORDER, NOCACHE, NOORDER, NOMINVALUE,
NOMAXVALUE
Back to top
View user's profile Send private message
H Muraleedhar

New User


Joined: 03 Mar 2009
Posts: 12
Location: Chennai

PostPosted: Wed Mar 04, 2009 5:16 pm
Reply with quote

Adding to the above point , I tried changing data type also . It only support if both the data types are compatible. That means you can change from CHAR to VARCHAR and DEC to NUMERIC, not from DEC to CHAR or NUMERIC to VARCHAR. Even constraints are not able to change in any of these case.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Wed Mar 04, 2009 9:38 pm
Reply with quote

Hello,

Suggest alter not be used for anything that "matters". There should be a common central repository of the ddl to create tables (as well as everything else) that should always be complete. If people are permitted to alter things whenever they want, it will not take long for the environment to become out of control and not maintainable.
Back to top
View user's profile Send private message
H Muraleedhar

New User


Joined: 03 Mar 2009
Posts: 12
Location: Chennai

PostPosted: Thu Mar 05, 2009 1:39 pm
Reply with quote

Hi Dick,

I agree with you in case that 'matters' . But some times we need this type of changes at least in our testing evvironment.
Back to top
View user's profile Send private message
aishwarya_20

New User


Joined: 19 Nov 2008
Posts: 57
Location: pune

PostPosted: Fri Mar 06, 2009 9:52 am
Reply with quote

Hi Muraleedhar,

Please have a look into the below mentioned link. You will get answers for most of your queries:-

publib.boulder.ibm.com/infocenter/iwedhelp/v6r0/index.jsp?topic=/com.ibm.db2e.doc/dbealter_table.html

Check the Alter command.
Back to top
View user's profile Send private message
H Muraleedhar

New User


Joined: 03 Mar 2009
Posts: 12
Location: Chennai

PostPosted: Fri Mar 06, 2009 2:09 pm
Reply with quote

Thank you aishwarya . But unfortunately from here I will not be able to access any IBM urls .
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1248
Location: Richfield, MN, USA

PostPosted: Fri Mar 06, 2009 11:12 pm
Reply with quote

dick scherrer wrote:
Hello,

Suggest alter not be used for anything that "matters". There should be a common central repository of the ddl to create tables (as well as everything else) that should always be complete. If people are permitted to alter things whenever they want, it will not take long for the environment to become out of control and not maintainable.

Agree completely. "Quick and dirty" is quick but also dirty. Nice for temporary solutions but will be confusing for others who have to maintain what you've done.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts db2 vs static data COBOL Programming 1
No new posts External data queue (changes?) CLIST & REXX 0
No new posts Pull data using date difference betwe... DB2 6
No new posts fast data scrambling PL/I & Assembler 10
No new posts ISPF Table to add a new column TSO/ISPF 1
Search our Forums:

Back to Top