Kbase P77017: How to move tables faster with tablemove
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
7/10/2008 |
|
Status: Verified
GOAL:
How to move tables faster with tablemove
GOAL:
How to speed up tablemove
GOAL:
How to reduce bi growth during tablemove
GOAL:
Can tablemove be run against a database that only has a df loaded?
FACT(s) (Environment):
Progress 9.1D
All Supported Operating Systems
Progress 9.1E
OpenEdge 10.x
FIX:
The following methods may be faster than using the tablemove utility.
Method#1:
In this example using sports, the tables Invoice, Customer, Item, Salesrep, State, Local-Default, and Ref-Call from will be moved from the Info Area into a new area, and their associated indexes will be moved into a separate index area.
Tested with 9.1D08 on UNIX.
1. prodb sports sports
2. prostrct add sports add.st
/* add.st */
d "New Area":9,32 ./sports_9.d1
d "Index Area":10,32 ./sports_10.d1
3. prostrct list sports shows that the areas have been added.
4. proserve sports
5. The required tables can now be binary dumped from multiple sessions -
proutil sports -C dump invoice $PWD
proutil sports -C dump customer $PWD
proutil sports -C dump item $PWD
proutil sports -C dump salesrep $PWD
proutil sports -C dump state $PWD
proutil sports -C dump local-default $PWD
proutil sports -C dump ref-call $PWD
Note: If using OpenEdge 10.X database, consider using -index 0 if this is a Type II area to enforce a full table scan ie w/o need for index
6. proshut sports -by
7. proutil sports -C truncate area "Info Area"
This will remove all data from the area and you will be prompted as to whether or not you do want to truncate the area. Ensure that you do have a valid backup.
8. Rebuild indexes as required so that the tablemove of all the required tables can be carried out. For this example only index "default" needs to be reactivated -
proutil sports -C idxbuild
Then rebuild the "default" index from table "Local-Default"
9. Move the tables and indexes -
proutil sports -C tablemove Invoice "New Area" "Index Area"
proutil sports -C tablemove Customer "New Area" "Index Area"
proutil sports -C tablemove Item "New Area" "Index Area"
proutil sports -C tablemove Salesrep "New Area" "Index Area"
proutil sports -C tablemove State "New Area" "Index Area"
proutil sports -C tablemove Local-Default "New Area" "Index Area"
proutil sports -C tablemove Ref-Call "New Area" "Index Area"
10. proserve sports
11. The required tables can now be binary loaded from multiple sessions -
proutil sports -C load invoice.bd
proutil sports -C load customer.bd
proutil sports -C load item.bd
proutil sports -C load salesrep.bd
proutil sports -C load state.bd
proutil sports -C load local-default.bd
proutil sports -C load ref-call.bd
11. Carry out an idxbuild on those tables.
Note that caution must be exercised when dumping/loading a served database to ensure that no other users try to update the tables at the same time.
12. The Info Area could now be truncated -
proutil sports -C truncate area
13. and removed from the database if necessary -
prostrct remove sports d "Info Area"
METHOD#2: effectively reducing the transaction scope of the tablemove operation
Tested with 10.1B03 on UNIX.
1. through Database Administrator, delete all indexes except the smallest index (ideally for a large tables an index on a logical field would be perfect):
eg: DROP INDEX "Name" ON "Customer"
2. run tablemove
$ proutil sports -C tablemove Customer "Cust_Data" "Cust_Index"
3. load the indexes back INACTIVE, through Database Administrator:
ADD INDEX "Name" ON "Customer"
AREA "Index Area"
INACTIVE
INDEX-FIELD "Name" ASCENDING
4. run idxactivate online or idxbuild offline
$ proutil sports -C idxactivate customer.name recs 10000 refresh 30