Changing column size from decimal(7,4) to decimal (19,8)

venky5436 · #1 (**permalink**) 05-23-06, 10:21

Hi,

Can anyone please tell me a better and quicker way to change the size of the datatype from decimal (7,4) to decimal (19,8) other than the usual export and import as they have data in it i canot drop and recreate them.AIX/V8.2.2

Thanks
venky

Marcus_A · #2 (**permalink**) 05-23-06, 11:51

There is no easy way to do this. You can use the Control Center, which executes stored procedures to unload the data, drop the table, re-create the table with the new column defintion, and reload the table.

venky5436 · #3 (**permalink**) 05-23-06, 13:44

Thanks for your advice and clarifying my doubt.
I thought this command would work
ALTER table tbname ALTER column clmname SET datatype decimal(19,8).
I saw it does not work.

I also had posted another question but have not received a reply for it yet where I wanted to change a not null to null not dropping the table or data.Can it be done.

Marcus_A · #4 (**permalink**) 05-23-06, 14:22

To see what can be altered, you should check out the ALTER TABLE statement in the SQL Reference Vol 2. You can download a PDF version of the manual from the IBM Website.

Peter.Vanroose · #5 (**permalink**) 05-30-06, 05:33

v8 for LUW only supports ALTER SET DATA TYPE for CHAR and variants, not for numeric. Only v8 for z/OS and v9 support your DECIMAL conversion.

grofaty · #6 (**permalink**) 05-31-06, 03:29

Hi,
these is one EXCELENT!!! solution in http://dbforums.com/showthread.php?p...=1#post4548226

Try this out:

Code:

CREATE TABLE SCHEM1.TB_TEST1 (COL1 SMALLINT NOT NULL, COL2 DEC(7,4));

DESCRIBE TABLE SCHEM1.TB_TEST1;

CALL SYSPROC.ALTOBJ ('APPLY_CONTINUE_ON_ERROR', 'CREATE TABLE SCHEM1.TB_TEST1 (COL1 SMALLINT NOT NULL, COL2 DEC(19,8) NOT NULL)', -1, ? );

DESCRIBE TABLE SCHEM1.TB_TEST1;

Note: character ";" is command delimiter.

So just repeat the whole table definition from create table and pass this as argument to system store procedure - in my case in bold text. What is the biggest beauty: the data does NOT need to be exported!!!

My system: DB2 v8.1 fixpack 9 (which is identical to db2 v8.2.2) on Windows.
Hope this helps,
Grofaty

Internet Services

Web Development

Digital Marketing

Consumer Tech