Feedback
Did this article resolve your question/issue?

   

Article

Considerations when using proutil tablemove with the database online

Information

 
TitleConsiderations when using proutil tablemove with the database online
URL NameP19094
Article Number000173842
EnvironmentProduct: OpenEdge
Version: 10.x, 11.x, 12.x
OS: All supported platforms
Question/Problem Description
Considerations when using tablemove with the database online
How does PROUTIL TABLEMOVE affect performance
Does the ROWID change during a tablemove?
Why must indexes be active for a tablemove operation?
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
The PROUTIL TABLEMOVE qualifier is used to move a table and optionally its associated indexes from one Storage Area to another. This operation can be run while the database remains online.  The following outlines points for consideration when using tablemove online:

1.  Moving records of a table from one Area to another invalidates all the ROWIDs and indexes of the table.
  • The rowid for these records change with the tablemove operation
  • For each index, an existing entry that points to the source row has to be deleted and a new one that points to the new row in the target Area has to be inserted.
  • Indexes are rebuilt automatically by the utility, whether they are moved at the same time as the table or not and therefore have to be active for the operation.
2.  Moving indexes with tablemove or not.
 
a. While it's unusual to leave the index in the existing Area and only move the table, indexes will remain in their current storage area when the "Index Area Name" is not included in the tablemove command line and will be rebuilt in the current area as the records are moved to the new area.
$   proutil dbname -C tablemove <tablename> <DataAreaName>

b. The PROUTIL IDXMOVE qualifier can be used instead, when only the indexes of a table need to be moved to a separate application data Area. Each index is moved in a separate command line. This is useful when indexes need to be separated into distinct Storage Areas, for example when the database design is such that the Primary Index is housed in the same Storage Area as the table and other indexes in a separate Storage Area.
$   proutil dbname -C indexmove <tablename>.<index-name> <IndexAreaName>
 
c. All of a table's indexes can be moved to the same or another application data Area other than the one to which the table is being moved, by specifying the related index area in the tablemove command line. Another IDXMOVE operation will be needed if for example when  the database structure designs a specific storage area for all primary indexes.
$   proutil dbname -C tablemove <tablename> <DataAreaName> < IndexAreaName> 
    
Why moving a table’s indexes with the TABLEMOVE qualifier is better?

It is more efficient to move the indexes at the same time, than moving a table separately and then moving the indexes with the IDXMOVE utility.
  • Moving a table an indexes in separate operations causes the indexes to be rebuilt twice, which wastes more disk space and takes longer.
  • When the indexes are moved with the table these records are already in shared memory, which means that they only need to be read in from disk once and are built as the table's records are moved.
  • When only the indexes are moved they need to be re-built in the new area. In order to do this the associated table's records first need to be read to accomplish this. 
  • When only the table is moved, all the indexes need to be rebuilt in their existing location as the rowid for these records has changed. An additional 64 database blocks are initially needed to accommodate the rebuilding of these indexes in their current location.
  • All the table's indexes are moved at the same time as the table's records, unlike idxmove which works on one index at a time.
3.  Tablemove acquires an EXCLUSIVE lock on the table while it is moving records 
  • As a consequence, there is the possibility that the utility will have to wait for all the necessary locks to be available before it can start. This might take some time in a high transaction environment.
  • An application that reads the table with an explicit NO-LOCK might be able to read records, but in some cases may get the wrong results returned since the table move operation makes changes to the indexes.
  • No other Administrative operation on any index of the moved table is allowed during the table move.
  • When tables are moved online with tablemove, no access to the table or its indexes is recommended during the move. We recommend that the tablemove operation is run during a period when the system is relatively idle, or when users are doing work that does not access that table.
4. Although PROUTIL TABLEMOVE operates in phases, it moves a table and its indexes in a single transaction 
  • To allow a full recovery if the transaction is interrupted, every move and delete of each individual record is logged.
  • As a result, moving a table requires the BI Recovery Area to be several times larger than the combined size of the table and its indexes.
  • Before moving a table, determine if available disk capacity is sufficient to support BI extent space that could grow to more than three times the size of the table and its indexes.  For further information refer to Article:
    000020613, Interaction between PROUTIL -C tablemove, before-image and after-imaging   
5.  Since indexes need to be active for a tablemove operation, another approach is to effectively reduce the transaction scope of the tablemove by dropping indexes outlined as follows:
  • Database Administrator: 
Dump the table.df to edit later.
Delete all indexes except the smallest index. For large tables an index on a logical field would result in the least index build activity if the order of the records is not important.
eg: DROP INDEX "Name" ON "Customer" 
  • Run tablemove including the remaining active index to their new Area. 
$   proutil dbname -C tablemove Customer "Cust_DataNew" "Cust_IndexNew" 
  • Database Administrator: Load the indexes back INACTIVE. 
ADD INDEX "Name" ON "Customer" 
AREA "New Index Area" 
INACTIVE 
INDEX-FIELD "Name" ASCENDING
  • Database Dictionary: Re-set the Primary Index. 
6. Other TABLEMOVE Considerations
  • Running "PROUTIL -C IDXCOMPACT" for the associated index(es) prior to the tablemove to the designed data area is recommended.
  • After using tablemove, the high-water-mark of this area remains. While this means there's more space for the remaining tables' record data before the area needs to be extended again, it also means you're going to need more backup media space. If all object have been moved out, area extents can be removed with PROSTRCT REMOVE (offline). These steps are outlined in Article 000021821, How to restructure the database using TABLEMOVE and PROSTRCT REMOVE  
  • Area location is not part of the CRC calculation, therefore tables and indices locations can be changed without having to recompile the application.
  • When a tablemove strategy is used to re-organise a Type I storage area that is fast approaching it's maxarea limit, move the indexes at the same time as the tables otherwise it may fail if there's not enough room to extend the Schema Area to rebuild the remaining indexes  
     000045381, TABLEMOVE from Type I Storage Area fails with error 13517  
  • LOB objects associated with the table are not moved as part of the operation, they will remain in their current location. Caution should be exercised before truncating the area as these LOBs will no longer be available but the moved records will still have reference to them.
    000011270, How to move a LOB to another Storage Area?  
7. Space and time requirements during the various stages that tablemove goes through need to be taken into account to determine if this is the right methodology for the operation.

Alternate approaches are traditional dump and load methodologies, however these are not necessarily 100% online. For example, 
If loading all content of an existing area to a new Storage area(s) in an existing database:
a. Binary dump the data.
b. Add the new Storage Area(s)
c. Truncate the current Storage Area offline (instead of having to wait using online methods to delete this data)
d. Index build (fast, there's no data to build indexes against)
e. Tablemove with indexes (fast, there's no data to build and saves editing .df files)
f. Binary load (which will load to the new storage area) then IDXBUILD. 
g. Remove the area with PROSTRCT REMOVE
Workaround
Notes
Progress Articles:

000021842, How to use the tablemove command.  
000010408, Best Practices involving the Schema Area.  
 
Last Modified Date4/1/2020 8:25 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.