Feedback
Did this article resolve your question/issue?

   

Article

Truncate the Schema Area with PROUTIL mvsch

« Go Back

Information

 
TitleTruncate the Schema Area with PROUTIL mvsch
URL NameP4794
Article Number000138866
EnvironmentProduct: Progress
Version: 9.1D, 9.1E
Product: OpenEdge
Version: 10.x, 11.x
OS: All supported platforms
Other: mvsch
Question/Problem Description
How to use PROUTIL -C mvsch ?
How to truncate the Schema Area?
How to free disk space the Schema Area occupies?
Why can the "Schema Area" not be truncated like other Storage Areas?
When to use the Schema Mover?
How to quickly get application objects out of the "Schema Area"
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
While all Type I Storage Area feature restrictions apply, certain management operations can specifically not be done on the Schema Area. A good example of this is: Area truncation (PROUTIL -C truncate Area), which can only be carried out against user data Areas, it cannot be run against the Schema Area.

Since Progress 9.1D the offline Schema Mover utility ( mvsch ) was made available, in order to effectively truncate the Schema Area i.e. make the Schema Area as small as possible. For earlier releases, the database will need to be dumped and loaded.
 
The Schema Mover utility was typically used in the past for all databases resulting from a "PROUTIL -C conv89", as all application database objects were in the Schema Area. The Schema Mover utility is rarely used in later OpenEdge releases, but is still very useful to move unexpected objects (default indexes, LOB objects) out of the Schema Area without wanting to execute a full database dump and load.

To truncate the Schema Area with the mvschema feature:

0.  Take a verified backup of the database beforehand (ideally) with PROBKUP otherwise the backup procedure preferred.

1.  Truncate the BI prior to running "mvsch", otherwise you will be instructed to do so:
" Use proutil to truncate bi file before conversion. (1286)"
$   proutil <dbname> -C truncate bi

2.  Run the Schema move.
$   proutil <dbname> -C mvsch

Prior to the schema moving executing; a confirmation reminds the importance of having a backup. The database will not be recoverable if any problem occurs during the schema move operation.
You must have your database backed up before running the conversion. (1024)
Have you done this (y/n) ?
Y
Schema move has successfully completed. (9845)

3. Once the Schema move has completed, update the current database st file to reflect the new Control Area information with:
$   prostrct list dbname dbname.st

The Schema move has changed the database structure:
  • All the extents of the previous "Schema Area" structure have been renamed and now belong the "Old Default Area", which uses the first available data Area (_Area-num) slot and is still a Type I Storage Area with the same number of records per block. 
d "Old Default Area":13,32;1 dbname_7.d1
  • A new "Schema Area" (Area 6) has been built with the database metaschema data only. 
d "Schema Area":6,32;1 dbname.d1

After mvsch has completed 

1. Schema Area indexes must be checked for integrity. For further information refer to Article: 2. Ideally, tables and indexes should now be moved out of the "Old Default Area" to a better designed Storage Area structure with: PROUTIL -C tablemove.  
 
 
 
Once there are no remaining database objects in the "Old Default Area",  space can be re-claimed by removing all its extents:
$   prostrct remove dbname d "Old Default Area"

If there were database objects that are no longer needed by the Application environment, the "Old Default Area" can be truncated where all the data will be effectively lost before dropping these from the schema and finally removing the area itself:
$   proutil dbname -C truncate area "Old Default Area"
The advantage is that this method is much faster than having to delete this data through ABL or the Data Dictionary. Once the "Old Default Area" is truncated, object schema definitions then need to be removed through the Data Dictionary once the primary indexes are rebuilt. This operation will be fast as there are no longer related data to delete. Finally all the extents can be removed as outlined above with PROSTRCT REMOVE.

Cautionary Notes prior to running mvschema:

0. Do not run the mvschema utility without having a fully verified database backing instate. This is a non-recoverable operation.

1. Don't forget about LOB objects, these are not moved when the tables (and indexes) are moved with tablemove, but will be included if tables with BLOB/CLOB fields are dumped and loaded to their new area(s).

2. Prior to OpenEdge 11.6.3, 11.7, any application schema tables with associated LOB objects in the Schema Area will need to either be moved with dump and load or ABL buffer-copy methods prior to mvsch being run. Refer to Articles: 3.  Prior to OpenEdge 11.6.4. 11.7.1, if the database is enabled for OpenEdge Transparent Data Encryption (TDE), Auditing or Key Events, do not run mvschema before reviewing Article:
Workaround
Notes
References to Other Documentation: 

OpenEdge Data Management: Database Administration, Database Basics, Creating and Deleting Databases, Using the Schema Mover

Progress Articles:

 Best Practices involving the Schema Area.  
 How to use the tablemove command.   
 What does the truncate area utility do?   
 
Last Modified Date10/5/2020 1: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.