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.

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.

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.

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.

Planed to do some testings with it.

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

packages - you are correct.

Indexes - I would build indexes first. Advantage is not worth it. At least in cases where I was involved in.

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.