How to determine if the value returned by the RECID function is about to reach 2 billion

How to determine if the value returned by the RECID function is about to reach 2 billion.
How to determine if the value returned by the RECID function is about to reach the maximum value of the INTEGER datatype.

The RECID value is stored in a database INTEGER field or an ABL INTEGER variable.
A Type II database area allow the RECID value to be larger than 2 billion (2^31).
Type I database areas so not accommodate RECID's larger than 2 billion as this is the maxarea limit of the Type I Storage Area architecture.

When storing a RECID value larger than 2 billion (2^31) into an INTEGER field or ABL INTEGER variable, the following error is returned:

A RECID is composed of two parts:

1. The Block Number in the Area
2. The Row Number within the Block.

Suppose the maximum-rows-per-block for a database storage area is set to 64:

• The row number part can take on values from 0 to 63 and requires 6 bits (2^6 = 64).
• A database field of type INTEGER or ABL variable is at most 32 bits long.
• RECIDs cannot be negative, if the RECID is more than 31 bits, then it will not fit in an INTEGER.
• Since the row number uses 6 bits, that leaves (31 - 6) or 25 bits for the block number.
• The biggest number that fits in 25 bits is (2^25 - 1) or 33,554,431.
• Block number 0 is not used. When the area has 33,554,430 blocks or less, the RECIDs of records in that area will fit into an INTEGER.

Continuing the example above:

128 records per block

• Requires 7 bits (2^7 = 128), which leaves (31 - 7) or 24 bits for the block number.
• The biggest number that fits 24 bits is (2^24 - 1) or 16,777,215 
• Block number 0 is not used. When the area has 16,777,214 blocks or less, the RECIDs of records in that area will fit into an INTEGER.

 256 records per block

• Requires 8 bits (2^8 = 256), which leaves (31 - 8) or 23 bits for the block number.
• The biggest number that fits 23 bits is (2^23 - 1) or 8,388,607
• Block number 0 is not used. When the area has 8,388,606 blocks or less, the RECIDs of records in that area will fit into an INTEGER.

How to determine if the value returned by the RECID function is about to reach 2 billion

The database area's High Water Mark can be used to determine how close the recids are to the maximum value of the INTEGER datatype.

1.  Use PROSTRCT STATISTICS to determine if the 64 bit boundary for block identifiers has been crossed:

Run the PROSTRCT STATISTICS command against the database, reports the Active Blocks (High Water Mark) for each database Type II Area. 
Using historical data, the Area's growth rate can be calculated and estimate when the (2^31-1) or 2 billion limit will be reached.

1. Review the records per block defined for all Type II Areas. 
2. Determine how many blocks are in the Type II Area.  

If the area has more blocks than (2 ^ 31 records / <records per /block>) , RECID values will be larger than 2 billion (2^31)

Example: Run a prostrct statistics report (online or offline) and review the active blocks and records/block listed for the area.

Database Block Usage for Area: DailyData

  Active blocks: 19863639
    Data blocks: 19863635
    Free blocks: 4
   Empty blocks: 121
   Total blocks: 19863760
  Extent blocks: 83
  Records/Block: 256
   Cluster size: 8

 

Maximum # of blocks (for 2 Billion records) =  2,147,483,648 records / (256 records / block)  =   8,388,608 blocks
Active blocks: 19,863,639  > 8,388,608 blocks

2. Use VST's to determine if the 64 bit boundary for block identifiers has been crossed

The following ABL code can be customised to requirement to identify vulnerable bit boundary Type II Storage Areas:

DEFINE VARIABLE Vulnerable AS LOGICAL NO-UNDO FORMAT "*/ ". 
DEFINE VARIABLE RowsPerBlock AS INTEGER NO-UNDO. 
DEFINE VARIABLE Limit AS INT64 NO-UNDO. 
DEFINE VARIABLE TwoBillion AS INT64 NO-UNDO. 

ASSIGN TwoBillion = EXP(2, 31). 

FOR EACH _AreaStatus NO-LOCK WHERE _AreaStatus._AreaStatus-Areanum > 6 
AND NOT _AreaStatus._AreaStatus-Areaname BEGINS "After Image",
/* For mixed Type I + Type II structures, 
report all Type II Areas with tables for 64-bit RECID values */
FIRST _Area WHERE _Area._Area-number =_AreaStatus._AreaStatus-Areanum AND _Area._Area-clustersize <> 1, 
FIRST _storageobject OF _area WHERE _object-type = 1 NO-LOCK
BREAK BY _AreaStatus._Areastatus-Areanum. 

ASSIGN 
RowsPerBlock = EXP(2, _area-recbits) 
Limit = (TwoBillion / RowsPerBlock) 
Vulnerable = (_areastatus-HiWater >= Limit). 

DISPLAY _area-name RowsPerBlock label "r/block" _areastatus-HiWater Vulnerable LABEL "Vuln". 

END.

3.  Find the highest RECID in a Table

If the table is in its own storage area, the following code can be useful to monitor the recid() value.
If the table shares the storage area with other database objects, this code is only useful in determining the current highest recid() value of the Table. The methods outlined above would be better employed.
 

DEFINE VARIABLE r1 AS RECID NO-UNDO.
DEFINE VARIABLE r2 AS RECID NO-UNDO.

FOR EACH <tablename> NO-LOCK:
    r2 = RECID(<tablename>).
        IF (r2 > r1) OR (r1 = ?) THEN
        r1 = r2.
END.

MESSAGE r1 VIEW-AS ALERT-BOX.

 

Progress Articles:

4GL/ABL: What are the size limits of the Progress INTEGER data types?
What's the quickest way to convert an INTEGER field to INT64 ?   
 How to change a RECID Data Type to INT64  
Can PROSTRCT STATISTICS be run against a live database?
 High Water Mark versus Total Database Blocks in PROMON