How to move tables faster with tablemove or D&L

How to move tables faster with tablemove?
How to speed up tablemove?
How to reduce bi growth during tablemove?
Can tablemove be run against a database that only has a df loaded?
Moving tables with D&L instead of tablemove
Alternative tableorg to reduce fragmentation after data purges

To improve tablemove performance:

Reduce the transaction scope of the tablemove operation. 

1. As Database Administrator, dump the .df for the table then delete all indexes except the smallest index (ideally for a large table, an index on a logical field would be perfect):

DROP INDEX "Name" ON "Customer"

2. Move the table/indexes to a new Area with:TABLEMOVE 

$ proutil sports -C tablemove Customer "Cust_Data" "Cust_Index"

3. Load the deleted indexes back as INACTIVE:

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

Considerations:

• Twice the disk space for the table’s storage in order to move it. 
• During the table move, the table is not accessible for OLTP activity.

Alternate method: Dump and Load with tablemove

In this example with sports database, tables Invoice, Customer, Item, Salesrep, State, Local-Default, and Ref-Call will be moved from the "Info Area" into a new area, and their associated indexes will be moved into a separate index area.

Caution must be exercised when dumping/loading a served database to ensure that no other users will try to update those tables at the same time.

Please ensure a valid database backup before proceeding

1.  Create a test database and add new storage areas

$   prodb sports sports
$   prostrct add sports add.st

/* add.st */

d "New Area":9,32 ./sports_9.d1
d "Index Area":10,32 ./sports_10.d1

2. Update the structure file (.st) from the Control Area

$   prostrct list sports sports.st

3. Start the database then binary dump required tables using multiple sessions:

• It is avdisable to check the integrity of the index used before binary dump (default Primary Index)
• If the table is currently in a TYPE II Area, consider dumping with a full table scan, i.e. without the need for index, using: -index 0

$   proserve sports
$   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

4.  Shutdown the database and remove data by simply resetting the HWM of the area

• This will effectively 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:

$   proshut sports -by
$   proutil sports -C truncate area "Info Area"

5. Rebuild the Primary Index on each table, for the subsequent tablemove operation:

• In this specific case, there's no need for additional performant parameters, the idxbuild operation will simply re-activate the index. There are no longer record key-field data to build indexes.

6. Move the required tables and indexes to their new area(s):

• There are no associated data or indexes being built as a result. This tablemove operation is effectively saving having to update the application schema .df with the new area information.

$   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"

7. Start the database and binary load the data to their respective new storage areas with multiple sessions:

$  proserve sports
$  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

8. Rebuild the indexes for these tables

• Primary Indexes will need to be built with IDXBUILD (offline)
• Remaining indexes could be built online with IDXACTIVATE 

 

$  proshut sports -by
$  proutil sports -C [ idxbuild | idxactivate ]

12. The orignating storage area  can now be removed from the database structure:

$   proutil sports -C truncate area "Info Area"
$   prostrct remove sports d "Info Area"

Considerations:

• During the dump process, the table must not be used at all but there's nothing preventing it from being used
• Lessening downtime​ by ​​​​​​streamlining the process can be error prone and even result in data loss unless carefully planned
• An index rebuild of all indexes associated with the table is required before they can be used. 
• Once completed, replication targets must be re-based

New online features in OpenEdge 12:

• Delete entire record content from a table online, super fast with the TABLEMOVE truncate feature in 12.3
• Only the table's schema remains and can optionally be created in a new area. 
• It requires record and LOB data to already be in a Type II area, indexes can be in either Type I or Type II areas.

• The new TABLEREORG feature in 12.3, reorganizes the data associated with a table in the sort order of the index specified without taking the table offline.  
• Tables are re-organized in place (within the same Type II storage area) and re-uses space as a cluster's becomes available
• Record data can still be accessed for OLTP while it is in the process of being reorganized
• TABLEREORG can be interrupted and resumed later.

There are many other methods as there is really no one-fits-all and this approach.These typically require assistance from PSC or Consultants who specialise in this field to design it for site and application specific requirements, like the Progress Pro2 Dump and Load which is designed for re-purposing at each site. Online access to application data and managing large table moves considering space requirements are decisive factors in these engagements. For example:

• Historic data are usually part of very large tables, moved in batches over time with BUFFER-COPY programming techniques and write/delete triggers, which can be added online since OpenEdge 12.2. 
• Some tables can afford to be created without record data, where new records can be created right away. Then records from the current table copied to the new table on-demand over time and deleted.

Progress Articles:

How to speed up the tablemove process?
How to move a LOB to another Storage Area?