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) ?
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: