ALTER TABLE ALTER COLUMN

Toad for IBM DB2
1

RENAME COLUMN col_old_name TO col_new_name;

However, when I try to run this DDL, I get this message "Lookup Error - DB2 Database Error: ERROR [42601] [IBM][DB2/NT64] SQL0104N An unexpected token “RENAME” was found following “tablename”. Expected tokens may include: “ALTER”.

How do I fix this?

I understand that ALTER COLUMN is not available in DB2 LUW V9.5. How do I force Toad to treat the DB as 9.5 instead of a 9.7 DB?

Thanks,
Bob T

I’m trying to rename a column in a version 9.5.7 DB2 LUW database. I’m using ‘ALTER TABLE’ and then highlighting the column to change it to a new name. The DDL created by Toad looks like this:

ALTER TABLE tablename

2

Bob,
What version/edition of Toad DB2 are you using?
thanks
Jeff

3

I’m using 5.0.3.718

4

I have a DB2 9.5.0 LUW database and cannot recreate your issue using the same release of Toad DB2 you have…
Perhaps some additional information will assist me in recreating your issue:
Are you only altering the name of a single column?
Is there data in that Table?
Can you post the DDL of that table?
Can you post the contents of Database - System General Tab (as shown in the attached).
Thanks
Jeff
SysGen.jpeg

5

“REQ_ETY_MSR_I” INTEGER NOT NULL,“ETY_NM” VARCHAR(

“ETY_KEY_I” INTEGER NOT NULL,“PCS_Q” DECIMAL(

“PCS_BAS_DSC” VARCHAR(30),“WGT_Q” DECIMAL(

“WGT_BAS_DSC” VARCHAR(30),“VOL_Q” DECIMAL(

“VOL_BAS_DSC” VARCHAR(30),“HGT_Q” DECIMAL(

“HGT_BAS_DSC” VARCHAR(30),“LNG_Q” DECIMAL(

“LNG_BAS_DSC” VARCHAR(30),“WID_Q” DECIMAL(

“WID_BAS_DSC” VARCHAR(30),“PNT_Q” DECIMAL(

“REC_STT” CHARACTER(1) NOT NULL DEFAULT ‘A’,“CRT_S”

“CRT_UID” CHARACTER(8) NOT NULL,“CRT_PGM_C” VARCHAR(

“LST_UPD_S” TIMESTAMP NOT NULL,“LST_UPD_UID” CHARACTER(

“HAN_UNT_Q” INTEGER,

)

IN "DW01581"DISTRIBUTE

COMPRESS YES;

DATA CAPTURE NONE

PCTFREE 0

LOCKSIZE ROW

APPEND OFF

NOT VOLATILE;

ADD PRIMARY KEY

(“REQ_ETY_MSR_I”, “ETY_KEY_I”);

Yes, I’m altering the name of a single column. There are about 14 million rows in the table.

The content of DB - System General Tab is attached.

Here is the DDL of the table:
CREATE TABLE “CUSREQ”.“REQ_ETY_MSR” (30) NOT NULL,11, 4),11, 4),11, 4),11, 4),11, 4),11, 4),11, 4),TIMESTAMP NOT NULL DEFAULT ,30) NOT NULL DEFAULT ,8) NOT NULL,“LST_UPD_PGM_C” VARCHAR(30) NOT NULL,“HAN_UNT_BAS_DCS” VARCHAR(30)BY HASH ( “ETY_KEY_I” )
ALTER TABLE “CUSREQ”.“REQ_ETY_MSR”
ALTER TABLE “CUSREQ”.“REQ_ETY_MSR”
Thanks,
Bob T
Toad issue 1.jpeg

6

Thanks for the information.
I have opened CR 102638 to track this.

7

This issue has been fixed in Toad for DB2 v5.1.
We are currently targeting to release that version on or about August 28, 2012.
We will definitely post to this forum when that version is available!
Thanks,
Jeff

8

Okay, Thanks Jeff.