Results 1 to 8 of 8

Thread: Alter column

  1. #1
    Join Date
    Dec 2011
    Posts
    30

    Unanswered: Alter column

    version db2 9.7 on AIX 6.4

    I had done alter table to increase the size of column
    from charecter(5) to charecter(20)

    after that i did reorg and runstats

    As a result It degrades the performance..

    for loading the data into tha same table takes 1 hour.

    but prior to the changes it was taking only 5 mins.

    Does anyone have any idea why is it so???

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What does the rest of the table look like? What are the indexes? How are you loading the data?

    Andy

  3. #3
    Join Date
    Dec 2011
    Posts
    30
    thanks for the quick response

    the table contain the four column of type(int ,char,char,char)
    .
    on three column including the alter column,i have an index.But i have drop that index before the alter operation and then recreate it.

    And i am doing load from the flat file by using load replace and nonrecoverable option


    Thanks,
    Nana

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Can you post the DDL for the table and indexes? How many rows does the table normally have and how many are you loading?

    Andy

  5. #5
    Join Date
    Dec 2011
    Posts
    30
    CREATE TABLE ABC.GEO_STG (
    "CUST_ID" INTEGER NOT NULL,
    "PREF" INTEGER NOT NULL,
    "ISO2" CHARACTER(64) NOT NULL,
    "STATE" CHARACTER(20) NOT NULL,
    "GOV" CHARACTER(12) NOT NULL,
    "CA_CCSD_CD" CHARACTER(12) NOT NULL,
    "POSTAL_CD" CHARACTER(44) NOT NULL,
    )

    Before this i have alter the four columnes ISO2,STATE
    GOV,CA_CCSD_CD,POSTAL_CD.

    also drop the index. and recreate after alter operation

    ABC.IDX on ABC.GEO_STG (
    CUST_ID ASC,
    ISO2 ASC,
    STATE ASC,
    GOV ASC,
    CA_CCSD_CD ASC
    )

    Then do the load by using following command

    db2 load client from /data/prcss of del modified by keepblanks messages /data/load.msg replace into ABC.geo_stg nonrecoverable;

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    1. Why are you using keepblanks?
    2. Some of the columns should be VARCHAR, such as POSTAL_CD and ISO2 and STATE.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are the datatypes of those five columns on before/after alteration?
    Code:
    column_name | before        | after         |
    ------------+---------------+---------------+
    ISO2        | CHARACTER(??) | CHARACTER(64) |
    STATE       | CHARACTER(??) | CHARACTER(20) |
    GOV         | CHARACTER(??) | CHARACTER(12) |
    CA_CCSD_CD  | CHARACTER(??) | CHARACTER(12) |
    POSTAL_CD   | CHARACTER(??) | CHARACTER(44) |
    ------------+---------------+---------------+

  8. #8
    Join Date
    Nov 2011
    Posts
    334
    DId you check the message file :/data/load.msg?
    Maybe too many warnings are generated because of the change of column length

Posting Permissions

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