Feedback
Did this article resolve your question/issue?

   

Article

How to move tables faster with tablemove or D&L

« Go Back

Information

 
TitleHow to move tables faster with tablemove or D&L
URL NameP77017
Article Number000153178
EnvironmentProduct: Progress
Version: 9.1D, 9.1E
Product: OpenEdge
Version: 10.x, 11.x
OS: All Supported Operating Systems
Other: RDBMS
Question/Problem Description

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

Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution

To improve tablemove performance:

Reduce the transaction scope of the tablemove operation. 

1. As Database Administrator, 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. Run tablemove command:

$ 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

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.

Note: 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 you do have a valid database backup. 

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. Command prostrct list sports will show that those areas above 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, i.e. without the need for index.

6. proshut sports -by

7. 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:

proutil sports -C truncate area "Info Area"

8. Rebuild indexes as required, so that the tablemove command for all the required tables can be carried out. For this example, only index "default" needs to be reactivated:

proutil sports -C idxbuild

With the above command, rebuild the "default" index from table "Local-Default"

9. Move the required 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. Start your database: 

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. Perform an idxbuild on those tables:

proutil sports -C idxbuild

12. The "Info Area" can be truncated and removed from the database, if required:

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

To this end, new online features in OpenEdge 12:

Delete entire record content from a table online, super fast.
  • Since 12.3, 'tablemove' is enhanced with the parameter "truncate". Only 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.
De-fragment Tables online
  • Since 12.4, a new 'tablereorg' feature, 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
  • It 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.​​​​​​​
Workaround
Notes
References to Other Documentation:
Progress Version 9: Database Administration Guide and Reference.

Progress Article(s):
Article 000020975: How to speed up the tablemove process?
Article 000011270: How to move a LOB to another Storage Area?
 
Last Modified Date2/18/2021 5:15 PM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.