Alter column definition

[g***@gmail.com]

I'm working with DB2 ver. 8.x and I'm trying to change the precision of decimal(10) to decimal(20). My script is:

ALTER TABLE xyz
ALTER COLUMN campo SET DATA TYPE DECIMAL(20, 0);

but I got this error:

DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: ALTER TABLE xyz
ALTER COLUM;BEGIN-OF-STATEMENT;<values> [SQL State=42601, DB Errorcode=-104]

Please help me to understand what's wrong and how can I change the precision of my field.
Thanks

goyosito

[Andreas Kannegiesser]

Post by g***@gmail.com
I'm working with DB2 ver. 8.x and I'm trying to
change the precision of decimal(10) to decimal(20).
ALTER TABLE xyz
ALTER COLUMN campo SET DATA TYPE DECIMAL(20, 0);
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601,
SQLERRMC: ALTER TABLE xyz
ALTER COLUM;BEGIN-OF-STATEMENT;<values> [SQL
State=42601, DB Errorcode=-104]
Please help me to understand what's wrong and how can
I change the precision of my field.
Thanks
goyosito

Hi,

as you can see from the syntax diagram for "ALTER TABLE" at

http://publib.boulder.ibm.com/infocenter/db2luw/v8//index.jsp

the rules for phrase "column-alteration" only allow "VARCHAR", "CHARACTER VARYING", "CHAR VARYING" or "VARGRAPHIC" for the clause "SET DATA TYPE"...

To alter your table you would have to save your data (EXPORT utility), DROP and then re-CREATE your table (with new definition) and fill in the saved data (LOAD or IMPORT utility).
Alternatively you can use the wizard from ControlCenter to generate a SQL script for you, which you can take as a template.

Good luck,
aka.

[Knut Stolze]

Post by Andreas Kannegiesser

Post by g***@gmail.com
I'm working with DB2 ver. 8.x and I'm trying to
change the precision of decimal(10) to decimal(20).
ALTER TABLE xyz
ALTER COLUMN campo SET DATA TYPE DECIMAL(20, 0);
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601,
SQLERRMC: ALTER TABLE xyz
ALTER COLUM;BEGIN-OF-STATEMENT;<values> [SQL
State=42601, DB Errorcode=-104]

as you can see from the syntax diagram for "ALTER TABLE" at
http://publib.boulder.ibm.com/infocenter/db2luw/v8//index.jsp
the rules for phrase "column-alteration" only allow "VARCHAR", "CHARACTER
VARYING", "CHAR VARYING" or "VARGRAPHIC" for the clause "SET DATA TYPE"...
To alter your table you would have to save your data (EXPORT utility),
DROP and then re-CREATE your table (with new definition) and fill in the
saved data (LOAD or IMPORT utility). Alternatively you can use the wizard
from ControlCenter to generate a SQL script for you, which you can take as
a template.

Yet another alternative is to move to DB2 V9.