Feedback
Did this article resolve your question/issue?

   

Article

Best Practices involving the Schema Area.

Information

 
TitleBest Practices involving the Schema Area.
URL NameP106007
Article Number000164080
EnvironmentProduct: Progress
Version: 9.x
Product: OpenEdge
Version: 10.x, 11.x
OS: All supported platforms
Question/Problem Description
Best Practices involving the database Schema Area
What is the maximum size of the Schema Area?
What restrictions are there on having application data in the Schema Area?
Why it is important to move application data out of the Schema Area?
Considerations when reorganising the "Schema Area"
 
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
Progress strongly recommends only the database schema data (metaschema information) are located in the Schema Area.  Many OpenEdge performance improvements and Database Features are dependent on data being in a Type II area which benefit from the way Type II areas arrange clusters of blocks which relate only to specific objects (tables, indices, LOBs). 

Databases which were created prior to Progress 9 or involved in convXY database migration projects will by default have all data located in the Schema Area. The following Article provides a very quick method of separating the database schema from the application schema. Please read the Cautionary Notes prior to running mvschema: While there is no technical reason why more than the database metaschema information cannot be placed in the Schema Area and there is no requirement that prevents application data from being placed in the Schema Area, understanding the limits imposed by programmatic rules on the Schema Area are detailed below.

Limitations when Application data is left in the Schema Area:
  • Database "Schema Area limits" apply to all OpenEdge 32 bit and 64 bit versions.
  • The number of records per block in the Schema Area cannot be adjusted to use the blocksize space more efficiently. The Schema Area has hard limits of 32 rpb for 4 KB and 64 rpb for 8KB database block sizes respectively.
  • While all Type I Storage Area database feature restrictions apply, certain management operations cannot be carried out on the Schema Area specifically. 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 to regain space but an alternative approach can be designed with the mvschema utility 
  • The power of Storage Areas is in addressing the physical disk where a particular Storage Area is housed. It facilitates being able to target specific I/O paths to the area's extents. When the mount points are different physical devices, either striped sets or single disk drives, one can guarantee writes that affect both an index entry and a table entry will happen in parallel, which is an aggregate cost of a single I/O.
  • Storage Areas provide a significant increase in physical database size limits. The two most common requests for increased database limits are for the sizes of individual tables and numbers of records per block. Having multiple areas and tuning the records per block (rpb) appropriately will allow for making available the largest possible table size for the data being stored. This was not possible until Type I Storage Areas were introduced in Progress 9 and further improved by the introduction of Type II Storage Areas in OpenEdge 10 and 64-bit keys in OpenEdge 10.1B. 
  • The Schema Area is limited to a maxarea size of 256 GB because:
    • It is a Type I area structure and cannot be changed to a Type II Storage Area (introduced in OpenEdge 10).
    • The hard limits of 32 rpb for 4 KB and 64 rpb for 8 KB database blocksize limit the maxarea size imposed by the 2^31 rowid limit. For further information refer to Article 000044869, When does the 2 billion rowid limit per Storage Area apply?  
    • The physical bytes are 256 GB less a small margin of (16 x blocksize)
    • The available data space is less with the 5GB reserved area space, 256 - 5 = 251 GB for recovery
When the Schema Area approaches it's maxarea limit the mvschema method described above can be used as the first step in re-organising the database from the "Old Default Area" to a better Type II area structure.
 
Workaround
Notes
Last Modified Date4/1/2020 8:31 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.