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
 

 

Change data types of a column in DB2 8.1

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Change data types of a column in DB2 8.1
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

Site Director


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

PostPosted: Wed Mar 04, 2009 11:13 am    Post subject:
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    Post subject: Reply to: changing data types
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

Site Director


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

PostPosted: Wed Mar 04, 2009 11:46 am    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Wed Mar 04, 2009 9:38 pm    Post subject:
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    Post subject:
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    Post subject:
Reply with quote

Hi Muraleedhar,

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

http://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    Post subject:
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: 1238
Location: Richfield, MN, USA

PostPosted: Fri Mar 06, 2009 11:12 pm    Post subject:
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    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 ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Can sending 5 MB data between cobol p... Kevin Vaz CICS 12 Tue Oct 18, 2016 4:50 pm
No new posts output data in CSV Format janmejay COBOL Programming 8 Sat Oct 15, 2016 2:20 pm
This topic is locked: you cannot edit posts or make replies. output data in CSV Format janmejay DFSORT/ICETOOL 3 Sat Oct 15, 2016 2:16 pm


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