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.

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

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

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.

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

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

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.

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.

I'll look into that option but I'm guessing a table has to exist before the import? Or am I just getting confussed?

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.

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.

About Quest Central - how do you feel about it?

I have used Quest Central and would recommend it highly. Ofcourse you need to dish out some money but it's good.

dollar