Results 1 to 7 of 7

Thread: Alter Table

  1. #1
    Join Date
    Jul 2013
    Posts
    9

    Unhappy Unanswered: Alter Table

    Hi All,

    I have column in my table with
    NET_QUANTITY_KPACK DECIMAL(9 , 2) NOT NULL
    WITH DEFAULT
    table name T_brad_temp.

    I want to alter the data type of NET_QUANTITY_KPACK DECIMAL(11 , 2).
    I tried this
    -------------------------------
    ALTER TABLE SETT.T_BRAD_TEMP ALTER
    NET_QUANTITY_KPACK SET DATA TYPE DECIMAL(11 , 2) NOT NULL
    WITH DEFAULT ;
    ---------------------------
    But this is not working:

    RESULT OF SQL STATEMENT:
    DSNT413I SQLCODE = -4700 INVALID SQLCODE
    DSNT418I SQLSTATE = 56038 SQLSTATE RETURN CODE
    DSNT415I SQLERRP = DSNHSM6C SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD = 507 0 0 -1 67 0 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD = X'000001FB' X'00000000' X'00000000' X'FFFFFFFF'
    INFORMATION

    Please help me.

    Thanks in advance
    Suman

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS are you using?

    Andy

  3. #3
    Join Date
    Mar 2010
    Posts
    32
    I tried this on db2 v97 fp 5 on aix it failed when i give syntax not null with default.


    create table trial (Quantity decimal(9,2) not null with default)
    DB20000I The SQL command completed successfully.

    alter table trial alter Quantity set data type decimal(11,2) not null with default

    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "null with default" was found following "pe
    decimal(11,2) not". Expected tokens may include: "<space>". SQLSTATE=42601


    alter table trial alter Quantity set data type decimal(11,2)
    DB20000I The SQL command completed successfully.

  4. #4
    Join Date
    Jul 2013
    Posts
    9
    hi andy,
    it is zos. . db2V8R1M0
    regards
    Suman

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Check the manual for the correct ALTER TABLE statement syntax.

    IBM DB2 for z/OS - Technical Resources - United States
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Jul 2013
    Posts
    9

    Thumbs down

    Quote Originally Posted by lazydev View Post
    I tried this on db2 v97 fp 5 on aix it failed when i give syntax not null with default.
    Hey,

    I tried this one,
    -----------------------
    ALTER TABLE SETT.T_CAO_BHUV_TEMP ALTER
    SUPPLIER_NUM SET DATA TYPE DECIMAL(14 , 0);
    ----------------------
    SQLERROR ON ALTER COMMAND, PREPARE FUNCTION
    RESULT OF SQL STATEMENT:
    DSNT413I SQLCODE = -4700 INVALID SQLCODE

    Regards
    Suman

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    You should be able to increase varchar length. The following works on v10:

    create table t1 (c1 int, c2 dec (9,2) not null with default);
    alter table t1 alter c2 set data type dec (11,2);
    ---------+---------+---------+---------+---------+---------+---------+---------
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
    ---------+---------+---------+---------+---------+---------+---------+---------


    Decreasing it returns:

    alter table t1 alter c2 set data type dec (9,2);
    ---------+---------+---------+---------+---------+---------+---------+-------
    DSNT408I SQLCODE = -190, ERROR: THE ATTRIBUTES SPECIFIED FOR THE COLUMN C2
    ARE NOT COMPATIBLE WITH THE EXISTING COLUMN DEFINITION

Posting Permissions

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