Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    448

    Unanswered: adding columns in the middle of the table

    version 7 on sun box os 2.8.
    I need to add new columns in the middle of the table. what is the best way to do it without restricting the user to access the table.the table is big around 3 billiion row.

    regards,

    mujeeb

  2. #2
    Join Date
    Mar 2004
    Posts
    448
    adding this.I have a small window, in which I can drop the table, but the new table defination with data should be up as early as possible.

    regards,

    mujeeb

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I believe you could do the following:

    1) rename the table (I do not remember if this is can be done in V7)
    2) add the columns to the end
    3) create a view with name the name of the old table that looks like what you want.

    HTH

    Andy

  4. #4
    Join Date
    Mar 2004
    Posts
    448
    No,I can't do that, we have specific requirements from the developers.

    what I am thinking of is to do these steps.

    1. db2look // get the ddl's
    2. export to <table_name>.del of del select * from <table_name>
    3. drop the table.
    4. recreate the table with the new defination.
    5. import from <table_name>.del insert into <new_table>(c1,c2,c3)
    6. set the grants and etc

    correct me if I am wrong

    regards,

    mujeeb

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes, that is the right way to do it. Cannot say how long it would take to export then import 3 billion rows. It sounded like you wanted to minimize down time. My suggestion would make that minimal.

    Andy

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    bmujeeb,

    1. LOAD
    Use load instread of import! Load is 100 times faster. I you have some foreign keys to this table then temporaly drop foreign key. After load re-create foreign key!

    Be careful, export, import and load can be very time consuming. It can last some hours maybe even days!!!


    2. INSERT WITH SELECT
    a) Rename original table.
    b) Create new table with original name with new column included.
    c) use sql: INSERT INTO TABLE2 SELECT COL1, COL2, etc FROM TABLE1

    Be careful this can be very time consuming.

    Hope this helps,
    Grofaty

  7. #7
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by grofaty
    2. INSERT WITH SELECT
    a) Rename original table.
    b) Create new table with original name with new column included.
    c) use sql: INSERT INTO TABLE2 SELECT COL1, COL2, etc FROM TABLE1

    Be careful this can be very time consuming.

    Hope this helps,
    Grofaty
    If he has 3 billion rows, (2) probably isn't an option, he'll fill up his logs most likely. There's always NOT LOGGED INITIALLY, but that's too dangerous in most cases.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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