Re: Altering fields

Craig Mullins

Re: Altering fields
(in response to Leonardo Kenji Shikida)
K:

Yes, it is true that you can not simply ALTER the data type of columns in
DB2 tables once they have been created. In order to change the data type you
must DROP and re-CREATE the object. You must be sure to save off all of the
data, and related objects (indexes, views, security, etc.) before dropping
it so that you can re-apply it all once the object has been re-created.
There are several vendor tools on the market that automate this
functionality making it simpler to perform, if you are interested.

Cheers,
Craig S. Mullins
Director, DB2 Technology Planning
BMC Software
http://www.bmc.com/products/env_page.html?id=5009

-----Original Message-----
From: Leonardo Kenji Shikida [mailto:[login to unmask email]
Sent: Wednesday, October 06, 1999 10:43 AM
To: [login to unmask email]
Subject: Altering fields


Is that true that DB2 doesn't allow to modify fields type after a table is
created ?
I said, how can I chance for example a "name" field from character(10) to
varchar(30) for example ? Control Center doesn't allow me to do this,
neither
alter database SQL command.

thanks in advance

K

Eugenia Huang

Re: Altering fields
(in response to Craig Mullins)
It is true. You'll need to drop and recreate the table that you want to
change its column characteristics. If you want to keep the "old" data on
that table, you'll need to unload it before you drop the table, and load it
to the new format after you've recreated the table.

Hope this helps...

-----Original Message-----
From: Leonardo Kenji Shikida [mailto:[login to unmask email]
Sent: Wednesday, October 06, 1999 11:43 AM
To: [login to unmask email]
Subject: Altering fields


Is that true that DB2 doesn't allow to modify fields type after a table is
created ?
I said, how can I chance for example a "name" field from character(10) to
varchar(30) for example ? Control Center doesn't allow me to do this,
neither
alter database SQL command.

thanks in advance

K

Leonardo Kenji Shikida

Altering fields
Is that true that DB2 doesn't allow to modify fields type after a table is
created ?
I said, how can I chance for example a "name" field from character(10) to
varchar(30) for example ? Control Center doesn't allow me to do this, neither
alter database SQL command.

thanks in advance

K

Leslie Pendlebury-Bowe

Re: Altering fields
(in response to Eugenia Huang)
Hi Craig
Good to see you back on-line ..
Les


______________________________ Reply Separator _________________________________
Subject: Re: Altering fields
Author: "Mullins; Craig" <[login to unmask email]> at Internet
Date: 10/6/99 9:53 AM


K:

Yes, it is true that you can not simply ALTER the data type of columns in
DB2 tables once they have been created. In order to change the data type you
must DROP and re-CREATE the object. You must be sure to save off all of the
data, and related objects (indexes, views, security, etc.) before dropping
it so that you can re-apply it all once the object has been re-created.
There are several vendor tools on the market that automate this
functionality making it simpler to perform, if you are interested.

Cheers,
Craig S. Mullins
Director, DB2 Technology Planning
BMC Software
http://www.bmc.com/products/env_page.html?id=5009

-----Original Message-----
From: Leonardo Kenji Shikida [mailto:[login to unmask email]
Sent: Wednesday, October 06, 1999 10:43 AM
To: [login to unmask email]
Subject: Altering fields


Is that true that DB2 doesn't allow to modify fields type after a table is
created ?
I said, how can I chance for example a "name" field from character(10) to
varchar(30) for example ? Control Center doesn't allow me to do this,
neither
alter database SQL command.

thanks in advance

K

Ruediger Kurtz

Re: Altering fields
(in response to Leslie Pendlebury-Bowe)
Craig,

>I presume we're talking about UDB, so what are those vendors you're mentioning, apart from embarcadero and BMC; we're currently looking desperately for such a tool since we're about to launch our first application and are awaiting the first changes to structures.

TIA Ruediger Kurtz




Mullins, Craig wrote:
>
> K:
>
> Yes, it is true that you can not simply ALTER the data type of columns in
> DB2 tables once they have been created. In order to change the data type you
> must DROP and re-CREATE the object. You must be sure to save off all of the
> data, and related objects (indexes, views, security, etc.) before dropping
> it so that you can re-apply it all once the object has been re-created.
> There are several vendor tools on the market that automate this
> functionality making it simpler to perform, if you are interested.
>
> Cheers,
> Craig S. Mullins
> Director, DB2 Technology Planning
> BMC Software
> http://www.bmc.com/products/env_page.html?id=5009
>
> -----Original Message-----
> From: Leonardo Kenji Shikida [mailto:[login to unmask email]
> Sent: Wednesday, October 06, 1999 10:43 AM
> To: [login to unmask email]
> Subject: Altering fields
>
> Is that true that DB2 doesn't allow to modify fields type after a table is
> created ?
> I said, how can I chance for example a "name" field from character(10) to
> varchar(30) for example ? Control Center doesn't allow me to do this,
> neither
> alter database SQL command.
>
> thanks in advance
>
> K

--

Richard A Yevich

Re: Altering fields
(in response to Ruediger Kurtz)
There IS ONE ALTER that is being added to DB2 and retrofitted to V5 and that
is to increase the length of a VARCHAR.

Richard Yevich
+=====+======+
Information Technology Consulting, Data Modeling, Advanced Education
RYC® Inc. USA: 1-800-664-2421 Int'l: 1-305-361-8585 Fax: 1-512-476-3930
Web: < http://www.ryci.com > Email: [login to unmask email] Offices: USA and Europe
DB2® Family and Oracle® Specialists - Parallel Technologies
VLDB and Data Sharing Technologies (specialties SAP®, Peoplesoft®)
Authors of "DB2 Answers" by Osborne-McGraw Hill, May, 1999


> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
> Leonardo Kenji Shikida
> Sent: Wednesday, October 06, 1999 11:43 AM
> To: [login to unmask email]
> Subject: Altering fields
>
>
> Is that true that DB2 doesn't allow to modify fields type after a table is
> created ?
> I said, how can I chance for example a "name" field from character(10) to
> varchar(30) for example ? Control Center doesn't allow me to do
> this, neither
> alter database SQL command.
>
> thanks in advance
>
> K
>