Storage area migration
- Thread starter pinne65
- Start date
taqvia
Member
Yes it is possible to move ceratin tables out of the storage Area. We can follow the below steps :
Dump the DF of the tables involved.
Modify the DF to add the tables to the new Area.
Start dumping the tables in single user mode to maintain the data integrity.
Once the Dump is completed.
shutdown the db.
Add the new extent prostrct add <db> <add.st> containing description of area.
load a df to rename the existing table to old-tablename in existing area.
<dont delete the table now just rename it>
load the new df to load the tables in the new area.
load the .d's previoulsy dumped.
rebuild indexes.
Bring up the database and release it to business users.If everything works fine then when you have downtime next time get rid of the tables renamed as oldtablename.
If possible do a multithreaded 4gl dump and load this will improve the overall time.
Arshad
Dump the DF of the tables involved.
Modify the DF to add the tables to the new Area.
Start dumping the tables in single user mode to maintain the data integrity.
Once the Dump is completed.
shutdown the db.
Add the new extent prostrct add <db> <add.st> containing description of area.
load a df to rename the existing table to old-tablename in existing area.
<dont delete the table now just rename it>
load the new df to load the tables in the new area.
load the .d's previoulsy dumped.
rebuild indexes.
Bring up the database and release it to business users.If everything works fine then when you have downtime next time get rid of the tables renamed as oldtablename.
If possible do a multithreaded 4gl dump and load this will improve the overall time.
Arshad
What version of Progress are you using?
"Is it possible to create storage areas and move tables into them using dump & load and just do a couple of tables at a time? Or do you have to do all the tables at the same time? "
yes but then you have to use proutil -C tablemove. This causes lots of bi activity and is rather awkward if you are dealing with larger tables.
How large is the database in question and how large is the biggest table?
Effectively a dump/load is much faster then doing the same thing with tablemove.
Casper.
"Is it possible to create storage areas and move tables into them using dump & load and just do a couple of tables at a time? Or do you have to do all the tables at the same time? "
yes but then you have to use proutil -C tablemove. This causes lots of bi activity and is rather awkward if you are dealing with larger tables.
How large is the database in question and how large is the biggest table?
Effectively a dump/load is much faster then doing the same thing with tablemove.
Casper.
We are using 10.1B03
We have two databases.
One holds 60GB of data. 15 tables are bigger than 1GB with the biggest one about 8GB.
The other one 35GB of data. With the biggest tables 15,10 & 9GB
I was looking for a solution without using table move because of the bi overhead.
Thanks for your input!
We have two databases.
One holds 60GB of data. 15 tables are bigger than 1GB with the biggest one about 8GB.
The other one 35GB of data. With the biggest tables 15,10 & 9GB
I was looking for a solution without using table move because of the bi overhead.
Thanks for your input!
KlausErichsen
New Member
Hello pinne65,
you can use the following method.
- create all new storage areas
- create a new table as a copy of the old one, e.g. "cust_new" for "cust" in the new area
- Do a for each loop with buffer copy (do 50 copies in one transaction)
- Drop the old table or rename them to "cust_old"
- Rename "cust_new" to "cust"
This can be done for multiple tables parallel and is fast depending on your db-server settings. The bi-clustersize should be large (e.g. 100000) and biw/apw/aiw should be started.
Regards
Klaus
you can use the following method.
- create all new storage areas
- create a new table as a copy of the old one, e.g. "cust_new" for "cust" in the new area
- Do a for each loop with buffer copy (do 50 copies in one transaction)
- Drop the old table or rename them to "cust_old"
- Rename "cust_new" to "cust"
This can be done for multiple tables parallel and is fast depending on your db-server settings. The bi-clustersize should be large (e.g. 100000) and biw/apw/aiw should be started.
Regards
Klaus
TomBascom
Curmudgeon
Replication across a WAN sounds like an entirely new thread. You might want to open a topic for that.
As far as the migration of storage areas goes... as has been mentioned there are many ways to do it. All have their pros and cons. But by far the largest consideration is making sure that your new storage area design is well thought out and designed properly. Getting the proper objects into appropriately configured areas (db block size, type1 vs type2, blocks per cluster, rows per block...) is very, very, very important. Do it well and you will be rewarded otherwise you are likely to be disappointed.
As far as the migration of storage areas goes... as has been mentioned there are many ways to do it. All have their pros and cons. But by far the largest consideration is making sure that your new storage area design is well thought out and designed properly. Getting the proper objects into appropriately configured areas (db block size, type1 vs type2, blocks per cluster, rows per block...) is very, very, very important. Do it well and you will be rewarded otherwise you are likely to be disappointed.