Alter column definition

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

> 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


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,

Andreas Kannegiesser wrote:

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

-- 
Knut Stolze
DB2 z/OS Utilities Development