Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    272

    Unanswered: Moving table from tablespace to another

    I am moving a table from one tablespace to another. As 9.7 has a new feature to do this online, I tried doing it with ADMIN_MOVE_TABLE procedure but its taking for ever to finish as the table has 20 GB of data and index pages. As I can get the outage from application to do this move offline, I decided to do it in old fashion. Recreating a table in the new tablespace and performing a cursor load from the old table.

    Can someone please tell me what are the steps required to do this ? What all dependencies of the old table needs to be dropped and recreated for the new table ?. Here is what I am planning to do.

    Take a db2look output of the old table
    db2look -d dbname -e -x -z schemaname -t tablename
    Drop the foreign keys from the old table
    Rename the older table
    modify the db2look output to reflect it to the new tablespace and commenting out the foreign key creation section
    run the modified db2look output
    Do a cursor load from the old renamed table to the new table created
    Recreate the foreign keys.

    Please let me know if I am missing something. Do I need to drop any more dependencies other than the foreign keys ?

    Any help would be greatly appreciated.
    Last edited by blazer789; 02-12-10 at 08:05.

  2. #2
    Join Date
    Jan 2010
    Posts
    335
    I would use -xd in the db2look. Maybe the created by another user. You would lose this permissions.

    The views are included in the db2look. What about views on the views (depending views)?

    Good to get some feedback on the ADMIN_MOVE_TABLE. Sounded like a nice feature, but it seems to be unusable. Reminds me on online table reorg.

  3. #3
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Sounds about right. Just before you do a RENAME, make sure you drop all of the dependent objects. Wait until you re done with the final load before creating them.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  4. #4
    Join Date
    Jun 2009
    Posts
    272
    Reply to nvk@vhv:
    Well, I don't say ADMIN_MOVE_TABLE is unusable. I was actually trying to do that on a test database whose host is an capped LPAR with 0.4 Entitled Capacity [CPU]. Small tables which were 1.3 GB in size took about 8 minutes. But because I had to drop the foregin keys, I had to do it in an outage window. As anyways it is an outage I thought its better to go with cursor load which is much faster.

  5. #5
    Join Date
    Jan 2010
    Posts
    335


    Planed to do some testings with it.

  6. #6
    Join Date
    Jun 2009
    Posts
    272
    REPLY TO COUGAR800

    All the dependent object I have are
    1. Indexes
    2. Packages

    Packages :- I guess I don't need to drop the packages. After renaming the table, the packages get invalidated and once I rebind that package I believe it becomes the dependent of the new table -- Correct me if I am wrong

    Indexes :- Can someone please tell me which way is faster ? Create the new table, create the indexes and load the data OR Just create the new table without indexes, load the data and then recreate the indexes

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    packages - you are correct.

    Indexes - I would build indexes first. Advantage is not worth it. At least in cases where I was involved in.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    About packages, yes, technically you are right ... But, I would recommend doing it manually as your apps need not wait for the rebind to happen and in worst case scenario, if the rebind fails, it will not be when the apps are online.

    As cogur8000 says, it is best to create indexes beforehand. db2 does data loads and sorting for indexes simaltaneously in the LOAD phase. also, multiple indexes are built at the same time in the BUILD phase. the exception will be when your IO system cannot handle such parallel operations.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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