Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Alter Table Alter Column fails

    Hi Everybody,

    I am trying to ALTER TABLE to change the width of decimal(8,2) to decimal(15,2) but failing.

    Here is the error msg.:

    Code:
    C:\Program Files\IBM\SQLLIB\BIN>db2 ALTER TABLE transaction_totals ALTER COLUMN
    MONTHLY_TOTAL_AMT SET DATA TYPE decimal(15,2)
    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 "ALTER TABLE TRANSACTION_TOTALS ALTER COLUM" was
    found following "BEGIN-OF-STATEMENT".  Expected tokens may include:
    "<values>".  SQLSTATE=42601
    But I tried at CC to change the same column there was error No2:

    Code:
    [IBM][CLI Driver][DB2/NT64] SQL0443N  Routine "ALTOBJ" (specific 
    name "") has returned an error SQLSTATE with diagnostic text 
    "SQL0444  Reason code or token: 
    *_TBLNDEP|DB2LK_TBLNDEP|...QLLI".  SQLSTATE=38553
    
    
    
    
    Explanation: 
    
    An SQLSTATE was returned to DB2 by routine "<routine-name>" 
    (specific name "<specific-name>"), along with message text 
    "<text>".  The routine could be a user-defined function or a 
    user-defined method.  
    
    User Response: 
    
    The user will need to understand the meaning of the error.  See 
    your Database Administrator, or the author of the routine.  
    
     Errors that are detected by the IBM supplied functions in the 
    SYSFUN schema all return the SQLSTATE 38552.  The message text 
    portion of the message is of the form: 
    
      
      SYSFUN:nn
     
    
     where nn is a reason code meaning: 
     
    
     01 Numeric value out of range 
    
     02 Division by zero 
    
     03 Arithmetic overflow or underflow 
    
     04 Invalid date format 
    
     05 Invalid time format 
    
     06 Invalid timestamp format 
    
     07 Invalid character representation of a timestamp duration 
    
     08 Invalid interval type (must be one of 1, 2, 4, 8, 16, 32, 64, 
    128, 256) 
    
     09 String too long 
    
     10 Length or position in string function out of range 
    
     11 Invalid character representation of a floating point number 
    
     12 Out of memory 
    
     13 Unexpected error 
    
     Errors that are detected by the IBM supplied routines in the 
    SYSIBM or SYSPROC schemas and IBM supplied procedures in the 
    SYSFUN schema all return the SQLSTATE 38553.  The message text 
    portion of the message contains a message number that may be an 
    SQLCODE, (for example, SQL0572N), a DBA error message (for 
    example, DBA4747), or some other indication from the routine as 
    to what error was encountered. Note that if the message for the 
    message number would normally contain tokens, these token values 
    are only available in the db2diag.log file.  
    
     sqlcode :  -443 
    
     sqlstate :  (the SQLSTATE returned by the routine).
    by the way the SQL in CC was :
    Code:
    CALL SYSPROC.ALTOBJ ( 'APPLY_CONTINUE_ON_ERROR', 'CREATE TABLE DB2ADMIN.TRANSACTION_TOTALS 
    ( TRANS_TYPE CHARACTER (2)  NOT NULL , BANK_ID CHARACTER (2)  NOT NULL , CARD_TYPE CHARACTER (2)  NOT NULL ,
     TOTAL BIGINT  WITH DEFAULT 0 , TIMESTAMP TIMESTAMP , ACTIVE CHARACTER (1)  NOT NULL  WITH DEFAULT 'Y' , 
    WEEKLY_TOTAL INTEGER  NOT NULL  WITH DEFAULT 0 , MONTHLY_TOTAL INTEGER  NOT NULL  WITH DEFAULT 0 , 
    DAILY_TOTAL_AMT DECIMAL (8, 2)  NOT NULL  WITH DEFAULT 0 ,
     WEEKLY_TOTAL_AMT DECIMAL (8, 2)  NOT NULL  WITH DEFAULT 0 ,
     MONTHLY_TOTAL_AMT DECIMAL (15, 2)  NOT NULL  WITH DEFAULT 0 ,
     PROCESSOR_GROUP VARCHAR (20)  NOT NULL  WITH DEFAULT '' ,
     TOTAL_ATTEMPTS INTEGER  WITH DEFAULT 0   ) IN TSD_SA ', -1, ? );
    ;

    So at the moment I have two problems

    1) Alter Table command not working V8.2 FP 11

    2) Changing Column width works on mirror but not production database.

    Please Help ( we cannot lock Prod databse for long time)

    Thanks in Advance

    DBFinder
    Last edited by DBFinder; 10-23-08 at 13:11.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You cannot directly alter a column from decimal(8,2) to decimal(15,2) in DB2 version 8.2. Did you bother to read the SQL Reference to see if that is an option? The only column alter permitted is for increasing the size of a varchar.

    The Stored Procedure to alter the column will export the data, drop the table, recreate the table, and reload the data. I am not sure why it failed, but I would look at the token carefully to see if you can figure it out. I would also try the Control Center again to see if you can get it to work.
    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
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    For the google-challenged amongst us: http://publib.boulder.ibm.com/infoce...61%62%6c%22%20

    Then search for "column-alteration".
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Marcus,

    yes I looked and found that v9 can change decimal field not v8.

    The stored procedure command was copied from CC and I used best of my brain to resolve it. Similar dito copy on other 4 servers work fine.

    Stolze,

    Thanks for shortcut. I keep posting everything that I find strange.

    For this problem I did manual export-Recreate- Import where I had to insert one row manually - - which was logical to our developers for some reason.

    Thanks everyone
    DBFinder

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Feel free to post here. But consulting the manuals first is always a good idea. That's what they are there for.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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