Feedback
Did this article resolve your question/issue?

   

Article

When does the 2 billion rowid limit per Storage Area apply?

Information

 
TitleWhen does the 2 billion rowid limit per Storage Area apply?
URL Name000044869
Article Number000165553
EnvironmentProduct: OpenEdge
Product: Progress
Version: 9.x, 10.x, 11.x
OS: All supported versions
Question/Problem Description
When does the 2 billion rowid limit per Storage Area apply?
When was the 2 billion row maximum per Storage Area changed?
Is there still a 2.1 billion rowid limit with records in Type II Areas?
What database structure supports large storage areas?
How to calculate the maxarea size of a Type II Storage Area?
How many rowids are available in a Type II Storage Area compared to a Type I Storage area with the same records per block and database blocksize?
How must the database be structured to accommodate large tables?
Does the 2^31 rowid limit still apply to the Schema Area?
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
Prior to OpenEdge 10.1B, before the introduction of 64-bit rowids when used in conjunction with Type II Storage Areas, the Progress Database has a two billion rowid maximum per database Storage Area.
 
The maximum rowids per area has practically been removed since OpenEdge 10.1B with the advent of 64-bit rowids and Type II storage areas. The number of rowids per Type II Storage Area is governed by the maximum area size rather than the addressability of 64-bit database keys which is the 32-bit database key hard limit on Type I Storage areas of 2^31 addressable rows per Area (and Type II Storage Areas prior to OpenEdge 10.1B).
 
Database Limits that apply: 

Please refer to the OpenEdge Documentation for Database limits that apply to the version in use.
 
For OpenEdge 10.1B databases with Type I OR Type II Storage Areas:
  • The maximum Storage Area extent size is 1TB,
  • The maximum number of extents per Storage Area is 1024.
However only for Type II storage areas, the increase in addressable rows (64-bit) comes into play, otherwise you're still limited to 2^(32-1) rowids and therefore a limit on the maxarea size on Type I Storage Areas.
 
Example:
 
For an 8KB database blocksize Type I Storage Area Structure:
 
d "Data Area":8,128;1 /db/dbname_8.d1 f 2097024
 
Where each area extent is defined as 2GB
 
2,097,024 x 1024 is the "Data Area" maximum total size 2047.8GB
That will never be reached because the maxarea rowid limit is 2^31 available keys that are distributed as 128 records per database block.
Thereby limiting the "Data Area" maximum total size to 128GB (2,147,483,648/128 x 8),
64 x 2GB extents for the "Data Area" and 2^31 rowids available for records.

 
For an 8KB database blocksize Type II Storage Area Structure:
 
Where each area extent is defined as 2GB
 
d "Data Area":8,128;64 /db/dbname_8.d1 f 2097024
 
2,097,024 x 1024 is the "Data Area" maximum total size 2047.8GB
And 3.28E+10 rowids available for records in the "Data Area" (8KB blocksize and 128 RPB)
Total Area size is limited by the fixed extent size and the maximum number of extents per Storage Area rather than the available rowids.
If the fixed extent size were larger (and largefiles enabled against the database), then the Total Area Size would also be larger as there are sufficient 2^63 rowids to accommodate.
 
In Progress 9.1E04, OpenEdge 10.0B05, 10.1x and above, Progress reserves 1 GB of space for areas that have a database block size of (1024 KB or 2048 KB) and large numbers for records per block (128 and 256).  For all other combinations 5 GB reserved space is reserved per Storage Area irrespective of it being a Type I or Type II structure. This space is reserved in each Storage Area to guarantee space for REDO crash recovery handling. This will reduce the total available blocks and rowids below those numbers calculated in the examples above. See Article 000012254 that further describes Area reserved space.

The database "Schema Area" has the 2 billion rowid limit across all versions because it cannot be converted from a Type I to a Type II Storage area

 
In summary:
 
The 2^31 rowid limit still exists for all current OpenEdge versions,
Unless OpenEdge 10.1B or later versions are in use AND the database structure using Type II Storage Areas.
 
Example:
If you're using OpenEdge 10.1A and Type II Storage areas, 2^31 rowid limit still applies. 
If you're using OpenEdge 11.7.4 and Type I Storage areas, 2^31 maxarea limit still applies. 
 
When using OpenEdge 10.1B or later, in order to take advantage of larger Storage Area sizes, the database needs to have a Type II Storage Area structure. for further information refer to Article 000022229.
Workaround
Notes
References to Other Documentation:

Database Limits are documented:
  • For OpenEdge 10, in the “OpenEdge Data Management: Database Design Guide” in the Chapter titled “OpenEdge RDBMS Limits”.
  • For Progress 9, in the “Database Administration Guide and Reference” Manual, in the Chapter titled “Database Limits”.
Progress Articles:
 
000022229, How to create a Type II Storage Area   
000012254, Reserved space used for recovery limits user addressable maximum storage area size.   
000013241, What to do when you hit the record limit for a Type I area?    
 
Last Modified Date1/7/2019 1:37 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.