Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2005
    Posts
    47

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

    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
    Last edited by venky5436; 05-23-06 at 11:43.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2005
    Posts
    47
    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.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    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
    Last edited by grofaty; 05-31-06 at 04:37.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •