Change data types of a column in DB2 8.1

H Muraleedhar · New User Joined: 03 Mar 2009 Posts: 12 Location: Chennai

Is there any way to change data types of a column in DB2 8.1?

dick scherrer · Posted: Wed Mar 04, 2009 11:13 am

Hello,

Many of my sites drop the column and re add it.

H Muraleedhar · New User Joined: 03 Mar 2009 Posts: 12 Location: Chennai

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.

dick scherrer · Posted: Wed Mar 04, 2009 11:46 am

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.

aishwarya_20 · New User Joined: 19 Nov 2008 Posts: 57 Location: pune

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.

H Muraleedhar · New User Joined: 03 Mar 2009 Posts: 12 Location: Chennai

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 .

aishwarya_20 · New User Joined: 19 Nov 2008 Posts: 57 Location: pune

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.

H Muraleedhar · New User Joined: 03 Mar 2009 Posts: 12 Location: Chennai

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

H Muraleedhar · New User Joined: 03 Mar 2009 Posts: 12 Location: Chennai

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.

dick scherrer · Posted: Wed Mar 04, 2009 9:38 pm

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.

H Muraleedhar · New User Joined: 03 Mar 2009 Posts: 12 Location: Chennai

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.

aishwarya_20 · New User Joined: 19 Nov 2008 Posts: 57 Location: pune

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.

H Muraleedhar · New User Joined: 03 Mar 2009 Posts: 12 Location: Chennai

Thank you aishwarya . But unfortunately from here I will not be able to access any IBM urls .

Terry Heinze · Posted: Fri Mar 06, 2009 11:12 pm