PROMON Database statistics for high water mark are not reliable in Progress 9

PROMON Database statistics for high water mark are not reliable in Progress 9
PROMON: Database Status "database blocks high water mark" does not give the real occupation of the database
Number of Empty blocks using the R&D option of PROMON can show a very high percentage of empty blocks
In Progress 8.x, "database blocks high water mark" gave the real occupation of the database.
Overall Database Storage Area high-water mark (hiwater) calculation
_AreaStatus VST storage area high-water mark
Virtual System Table (VST) used to calculate database occupation

With the introduction of Storage Areas in Progress 9, the high-water mark for each area must be monitored to ensure that sufficient space is available to extend the database on a per area basis. Disregard for the high-water mark on a per area basis could result in abnormal shutdown of the database due to inability to extend within an area (8897).

The reason that PROMON is not reporting the "real occupation of the database" is because there are now multiple high water marks within Storage Areas in Progress V9 and later. PROMON does not account for these.  Database-wide metrics were valuable in the days when we had single extent/storage area databases.  With the introduction (and use) of Storage Areas, this information is no longer useful except perhaps for estimating the PROBKUP volume size. Using the _AreaStatus Virtual System Tables (VST) or PROSTRCT STATISTICS reports are a better way of looking at database statistics.

This issue has been addressed in Progress 9.1D.

PROMON -> 7. Database Status option will display a valid database-wide highwater mark

To understand how much of the allocated blocks are used up and by inference, what the true size of the Database is to monitor growth, the following example can be customised to requirements.
 

define variable v-prcnt_full as decimal format ">>9.99" label "% Full" no-undo.
define variable v-empty_blocks as decimal format ">>,>>>,>>9" label "Empty" no-undo.
define variable v-hiwater as decimal format ">>,>>>,>>9" label "Hiwater" no-undo.
define variable v-mb_used as decimal format ">>>,>>9.99" label "MB Used" no-undo.
define variable v-mb_avail as decimal format ">>>,>>9.99" label "MB Avail" no-undo.
define variable v-mb_tused as decimal format ">>>,>>9.99" label "Total MB used" initial 0.0 no-undo.
define variable v-mb_tavail as decimal format ">>>,>>9.99" label "Total MB avail" initial 0.0 no-undo.

/* OUTPUT TO storgeareasizes.out. */

FOR EACH _Area NO-LOCK:
 FIND _Areastatus WHERE _Areastatus-Areanum = _Area._Area-number NO-LOCK.

 v-hiwater = _AreaStatus-Hiwater.
 if v-hiwater = ? then v-hiwater = 0.0.

 v-empty_blocks = _AreaStatus-Totblocks - v-hiwater - _AreaStatus-Extents.

 v-prcnt_full = (1.0 - (v-empty_blocks / _AreaStatus-Totblocks)) * 100.0.
 
 v-mb_avail = v-empty_blocks / 1048576 * _Area-BlockSize.
 v-mb_tavail = v-mb_tavail + v-mb_avail.
     
 v-mb_used = v-hiwater / 1048576 * _Area-BlockSize.
 v-mb_tused = v-mb_tused + v-mb_used.

 DISPLAY
   _Area-name LABEL 'Name' format "x(21)"
     _Area-blocksize LABEL 'DBBlockSize'
  _AreaStatus-Extents LABEL '#Extents' format ">>9" SKIP
  _AreaStatus-Lastextent LABEL "HWM extent" SKIP
  v-hiwater
  v-empty_blocks
  _AreaStatus-Totblocks - _AreaStatus-Extents LABEL 'T.Blocks' FORMAT ">>,>>>,>>9"
  v-prcnt_full SKIP
  v-mb_used
  v-mb_avail.

END.

display v-mb_tused
    v-mb_tavail.

/* OUTPUT close. */

 

Query VST information.

The formula used to determine the overall Database high-water mark is:  

FOR EACH _Areastatus WHERE _AREASTATUS-AREANUM >= 6 NO-LOCK:
    DISPLAY _AREASTATUS._AREASTATUS-AREANAME _AREASTATUS._AREASTATUS-HIWATER(SUM) SKIP.
END.

To calculate how much physical space is remaining:

The difference between Total Database blocks and the Overall High-water Mark, is then Total Empty Blocks.
Comparing this to the O/S size of the database (by conversion of each to the same byte order of magnitude) and the remaining space left on the filesystem aides forecast planning exercises and prevents databases terminating abnormally.

PROMON R&D -> 1. Status Displays -> 5. Files
Is useful for checking extent size versus disk capacity, to verify the allocated and empty/free space on a per file basis especially when areas/extents exist on different disks.