Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2004
    Location
    Dunedin, New Zealand
    Posts
    8

    Question Unanswered: How do I move a table to a different table space

    Hello,

    I'm new to using DB2 (and this forum) and need a little help.

    I'm trying to move some tables from one table space to another.

    Environment:

    Windows 2000 Server SP4
    DB2 8.1 with Fixpack 4.

    The reason behind me even trying to do this is that we need to be able to restore indivitdual tables from multiple schemas that reside in a single database.

    If anyone knows of a way to structure DB2 to allow this then please let me know.

    Thanks in advance.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: How do I move a table to a different table space

    I don't think there is any way other than drop/create of the table

    If you come to know of an alternative please let us know

    Cheers
    Sathyaram

    Originally posted by Bill Hopgood
    Hello,

    I'm new to using DB2 (and this forum) and need a little help.

    I'm trying to move some tables from one table space to another.

    Environment:

    Windows 2000 Server SP4
    DB2 8.1 with Fixpack 4.

    The reason behind me even trying to do this is that we need to be able to restore indivitdual tables from multiple schemas that reside in a single database.

    If anyone knows of a way to structure DB2 to allow this then please let me know.

    Thanks in advance.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2004
    Location
    San Antonio, Texas
    Posts
    47

    Talking Re: How do I move a table to a different table space

    I'm note sure this will help. I'm on Unix but maybe it's the same since you are using V8.1 You can do a redirected restore and specify a new tablespace. The catch is IBM says they have table level recovery but they don't really. It is tablespace recovery. If you have one table per TBLSP then you get table recovery. :-0

    As much as I hate Command Center it should allow you to do this (I no longer use it but kind of remember something). If not, let me know and I'll give you the Unix syntax which you can use to determine the syntax for Windows. I'm pretty sure they are very similar for this.

    If the tables are small it might be best to write a script to get the schema from db2look, unload the tables every night (like a backup) and use that to recreate them if you need to.

    Regards,
    AC

    Originally posted by Bill Hopgood
    Hello,

    I'm new to using DB2 (and this forum) and need a little help.

    I'm trying to move some tables from one table space to another.

    Environment:

    Windows 2000 Server SP4
    DB2 8.1 with Fixpack 4.

    The reason behind me even trying to do this is that we need to be able to restore indivitdual tables from multiple schemas that reside in a single database.

    If anyone knows of a way to structure DB2 to allow this then please let me know.

    Thanks in advance.

  4. #4
    Join Date
    Mar 2003
    Posts
    343

    Re: How do I move a table to a different table space

    Are you trying to move data between tablespaces within the same database or are you trying to move tables in a tablespace in a database to another database?

  5. #5
    Join Date
    Jan 2004
    Location
    Dunedin, New Zealand
    Posts
    8

    Re: How do I move a table to a different table space

    Originally posted by cchattoraj
    Are you trying to move data between tablespaces within the same database or are you trying to move tables in a tablespace in a database to another database?
    Hello, The same database.

    Thanks.

  6. #6
    Join Date
    Nov 2003
    Location
    Jakarta, Indonesia
    Posts
    32

    Temp Table

    How about if you create a new table with same structure in the target tablespace and do a insert select and then drop the old table in the old tablespace and rename the new table name to your old table name

    Regards,
    Adam AC

  7. #7
    Join Date
    Dec 2003
    Posts
    39

    Re: Temp Table

    Hello


    First export the table in . ixf format ,And import in the DATABASEwhere u want to move it using the create clause of import

    ex: import from abc.ixf of ixf create into tablename

  8. #8
    Join Date
    Mar 2003
    Posts
    343
    Do what adam_ac says - just be sure to put the table in the same schema as the source table since you cannot change a table's schema after it has been created. If logging is an issue then create the table not logged initially and then insert the data after activating NLI.

  9. #9
    Join Date
    Jan 2004
    Location
    Dunedin, New Zealand
    Posts
    8

    Re: Temp Table

    Originally posted by adam_ac
    How about if you create a new table with same structure in the target tablespace and do a insert select and then drop the old table in the old tablespace and rename the new table name to your old table name

    Regards,
    Adam AC
    Thanks, I did look at that option as it looks like one of only two to try according to the Information Center.

    Trouble is that I'm using an application that has DB2 as it's back-end database and there are a load of tables to create - can be upto 300.

    I'll probably void the support agreement for the app as we are not supposed to modify the tables in any way.

    This application has utilites that load data into the database and activate the tables but there is no way to choose which table space to use.

  10. #10
    Join Date
    Jan 2004
    Location
    Dunedin, New Zealand
    Posts
    8

    Re: Temp Table

    Originally posted by ashaq
    Hello


    First export the table in . ixf format ,And import in the DATABASEwhere u want to move it using the create clause of import

    ex: import from abc.ixf of ixf create into tablename
    I'll look into that option but I'm guessing a table has to exist before the import? Or am I just getting confussed?

  11. #11
    Join Date
    Jan 2004
    Location
    Dunedin, New Zealand
    Posts
    8
    I'm testing the Quest Database Central for DB2 and have managed to use it to move the tables to the different table space. This has worked well. Some other things like database restore have not worked as well as I expected but I guess that is because of me not the software.

    Thanks for all those that have helped or tried to help me.

  12. #12
    Join Date
    Nov 2002
    Location
    Delaware
    Posts
    186

    Re: How do I move a table to a different table space

    we do table moves all the time, nothing should be using the table, make sure you do a db2look against it to get all the info you need, export the data, create the new table like new_oldtablename. import data, compate counts, if okay, drop original table, rename new table to orginal table name, create indexes and view which shoould be in you db2look output. and you will be fine. Just make sure when you create the new table, you do a connect as the user so the shema name is correct.

  13. #13
    Join Date
    Mar 2003
    Posts
    343
    About Quest Central - how do you feel about it?

  14. #14
    Join Date
    Sep 2002
    Posts
    456
    I have used Quest Central and would recommend it highly. Ofcourse you need to dish out some money but it's good.

    dollar

    Originally posted by cchattoraj
    About Quest Central - how do you feel about it?

Posting Permissions

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