Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025

 


Article

Best Practices involving the Schema Area.

Information

 
Article Number000010408
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?
 
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
Progress strongly recommends only 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 quick way of separating schema and user data. 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 imposed on the Schema Area are detailed below.

Limitations when Application data is left in the Schema Area:
  • 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 blocksizes respectively.
  • While all Type I Storage Area feature restrictions apply certain management operations specifically cannot be done on the Schema Area. A good example of this is area truncation (PROUTIL -C truncate area). This operation can only be carried out against user data areas, it cannot be run against the Schema Area.
  • The power of Storage Areas comes from addressing physical disk to a particular Storage Area. This allows one to target very specific I/O paths to an area. When the mount points are different physical devices, either striped sets or single disk drives, one can guarantee that 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 significant increases 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 will allow for 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. 
  • Database "Schema Area" limits apply to all 32 bit and 64 bit versions of Progress.
  • The Schema Area is limited to a maxarea size of 256 GB (actually less with the reserved space, 256 - 5 = 251 GB) because:
  • It is a Type I Storage Area which have the 2^31 rowid limit and 
  • It has hard limits of 32 rpb for 4 KB and 64 rpb for 8 KB database blocksize and 
  • It cannot be changed to a Type II Storage Area (introduced in OpenEdge 10).
 
Workaround
Notes
Attachment 
Last Modified Date12/20/2018 3:00 PM