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

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 | Gregor.Kovac@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

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

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/

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/

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 | Gregor.Kovac@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-