how to increase char(35) to char(100)

[ipy2006]

Hi All:
In the table XYZ.ORD_DTL the column PDT_DESC is CHAR(35). I want to
increase it to CHAR(100). However, I am getting the following error.
Please help!

db2 =alter table XYZ.ORD_DTL alter column PDT_DESC SET DATA TYPE
CHAR(100)

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0190N ALTER TABLE "OMS.ORD_DTL" specified attributes for column
"PDT_DESC"
that are not compatible with the existing column. SQLSTATE=42837

Thanks

[Gregor Kova�]

ipy2006 wrote:

Hi All:
In the table XYZ.ORD_DTL the column PDT_DESC is CHAR(35). I want to
increase it to CHAR(100). However, I am getting the following error.
Please help!

db2 =alter table XYZ.ORD_DTL alter column PDT_DESC SET DATA TYPE
CHAR(100)

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0190N ALTER TABLE "OMS.ORD_DTL" specified attributes for column
"PDT_DESC"
that are not compatible with the existing column. SQLSTATE=42837

Thanks

You can only change VARCHAR data type directly with ALTER TABLE, but you can
do this:
CREATE TABLE TMP_ORD_DTL LIKE OMS.ORD_DTL;
INSERT INTO TMP_ORD_DTL SELECT * FROM OMS.ORD_DTL;
DROP TABLE OMS.ORD_DTL;
CREATE TABLE OMS.ORD_DTL
(
COLUMN1 CHAR(100),
.....
);
INSERT INTO OMS.ORD_DTL SELECT * FROM TMP_ORD_DTL;
DROP TABLE TMP_ORD_DTL;

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Shashi Mannepalli]

You cannot do it for CHAR column.

You can modify the characteristics of a column by increasing the length
of an existing VARCHAR or VARGRAPHIC 2 column. 2 The number of
characters may increase up to a value dependent on the page size used.

export/drop/re-create/load is one option.

Shashi Mannepalli

ipy2006 wrote:

Hi All:
In the table XYZ.ORD_DTL the column PDT_DESC is CHAR(35). I want to
increase it to CHAR(100). However, I am getting the following error.
Please help!

db2 =alter table XYZ.ORD_DTL alter column PDT_DESC SET DATA TYPE
CHAR(100)

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0190N ALTER TABLE "OMS.ORD_DTL" specified attributes for column
"PDT_DESC"
that are not compatible with the existing column. SQLSTATE=42837

Thanks

[Serge Rielau]

Shashi Mannepalli wrote:

You cannot do it for CHAR column.

Note, this is supported in DB2 9.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Shashi Mannepalli]

Thanks Serge.

Serge Rielau wrote:

Shashi Mannepalli wrote:

You cannot do it for CHAR column.

Note, this is supported in DB2 9.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Gregor Kova�]

Serge Rielau wrote:

Shashi Mannepalli wrote:

>You cannot do it for CHAR column.

Note, this is supported in DB2 9.

Cheers
Serge

Yes and other data types too :)
I've converted a table with millions of rows from DECIMAL(18,2) to
DECIMAL(31, 15) in matter of minutes. :)

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-