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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ipy2006 - 06 Jul 2006 15:37 GMT

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

Reply to this Message

Gregor Kovač - 06 Jul 2006 15:51 GMT

> Hi All:
> In the table XYZ.ORD_DTL the column PDT_DESC is CHAR(35). I want to
[quoted text clipped - 13 lines]
>
> 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

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Reply to this Message

Shashi Mannepalli - 06 Jul 2006 15:53 GMT

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

> Hi All:
> In the table XYZ.ORD_DTL the column PDT_DESC is CHAR(35). I want to
[quoted text clipped - 13 lines]
>
> Thanks

Reply to this Message

Serge Rielau - 06 Jul 2006 16:11 GMT

> You cannot do it for CHAR column.

Note, this is supported in DB2 9.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

Reply to this Message

Shashi Mannepalli - 06 Jul 2006 19:24 GMT

Thanks Serge.

> > You cannot do it for CHAR column.
> Note, this is supported in DB2 9.
[quoted text clipped - 9 lines]
> IOD Conference
> http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

Gregor Kovač - 07 Jul 2006 07:09 GMT

>> 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

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Reply to this Message