View previous topic :: View next topic
|
Author |
Message |
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? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Many of my sites drop the column and re add it. |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
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. |
|
Back to top |
|
|
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 . |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
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. |
|
Back to top |
|
|
aishwarya_20
New User
Joined: 19 Nov 2008 Posts: 57 Location: pune
|
|
Back to top |
|
|
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 . |
|
Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008 Posts: 1248 Location: Richfield, MN, USA
|
|
|
|
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 |
|
|
|