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 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.
