Feedback
Did this article resolve your question/issue?

   

Article

Interaction between PROUTIL -C tablemove, before-image and after-imaging

Information

 
TitleInteraction between PROUTIL -C tablemove, before-image and after-imaging
URL NameP3586
Article Number000145297
EnvironmentProduct: Progress
Version: 9.1D, 9.1E
Product: OpenEdge
Version: 10.x, 11.x, 12.x
OS: All Supported Operating Systems
Other: Database, TABLEMOVE
Question/Problem Description
Interaction between PROUTIL -C tablemove and after-imaging.
Interaction between PROUTIL -C tablemove and before-image growth.
Why does tablemove cause the BI file to grow larger than the size of the table and it's indexes?
How much will the BI file grow during a TABLEMOVE?
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
When a table is moved from one Storage Area to another it does so in a single transaction, so that if the tablemove operation is interrupted the database can be restored into a consistent state through the bi recovery mechanism.
 
During a table move:
  • All the records from the source table are read, equivalent records are created in the destination Area,
  • The primary index is built followed by the secondary indexes (because the ROWID changes)
  • Finally the source records are deleted, the old indexes are removed and
  • The _StorageObject records of the indexes and the table are updated. 
These operations take place irrespective of whether the indexes are moved as part of the tablemove operation or not.
 
Aside from these, there are also space allocation/deallocation operations which need to be logged. All these operations are logged in the bi and (if enabled) ai transaction notes.
 
As a consequence the before-image file will grow very large, a number of times larger than the space taken by the table and it's indexes, as will ai files which in addition can cause a high number of after-image extent switches.  Allow enough disk space for the before-image; if after-imaging is enabled, ensure that the after-image extents can be backed up, stored and marked as empty quick enough to prevent the operation from filling all after-image extents and stalling and/or exiting.
 
While the tablemove operation can be monitored with the _UserStatus VST, currently there is no easy way to calculate in advance how much space will be required.
 
The simplest means (but not necessarily convenient) is to make a separate database that contains a copy of a subset of the table to be moved, do the move and see how much log data are generated. Then scale that number up by the size of the sample in comparison to the actual table.  If the sample is 10% of the actual table then moving it should be 10 times what moving the sample did, it depends on how representative the sample is to the real data.
 
Example: A rough estimation of  how much BI growth could be expected and would reflect in the AI usage:
 
Consider a table of 300 Megabytes, which contains 100 byte rows (to keep the math simpler). Thus there are 3,000,000 rows.
  • To create a row in the new Area requires a bi note of approximately 200 bytes. 
  • To delete a row in the source Area requires a bi note of approximately 200 bytes plus creating a placeholder note of approximately 100 bytes.
  • In addition, space allocation notes and notes to update the RM chain will also be needed. Assume these consume another 100 bytes per row (probably the real number is less).
That all adds up to 600 bytes of bi notes per record that is moved from one Storage Area to the other.
Which would be 600 * 3,000,000 = 1,800,000,000 bytes for this table example.
 
This does not include index entries:
  • The number of notes required for index notes is dependent on the number of indexes. 
  • 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. 
  • Each index has 3,000,000 entries. Assume 100 bytes of bi to delete an entry and 100 more to insert a new one. That's 200 * 3,000,000 or 600,000,000 bytes more per index.
  • Deactivating and dropping non-essential indexes, would effectively reduce the transaction scope of the tablemove operation and therefore it's associated transaction notes.
These numbers are just rough estimates, but they are probably in the right ballpark.
Workaround
Notes
References to Other Documentation: 

OpenEdge Data Management: Database Administration, Maintaining and Monitoring your Database, Maintaining Database Structure, Maintaining indexes and tables, Moving tables.

Progress Articles: 

 Considerations when using proutil tablemove with the database online   
How to speed up the tablemove process?   




 
Last Modified Date11/20/2020 7:33 AM
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.