Status: Verified
SYMPTOM(s):
Promon Database statistics for high water mark is not reliable in Progress 9.x
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
Promon "database blocks high water mark" does not give the real occupation of the database
In Progress 8.x, "database blocks high water mark" gave the real occupation of the database.
Number of Empty blocks using the R&D option of the PROMON utility can show a very high percentage of empty blocks
SYSTEM ERROR: Unable to extend database within area <pareaName>. (8897)
FACT(s) (Environment):
Progress 9.1A
Progress 9.1B
Progress 9.1C
All Supported Operating Systems
CAUSE:
With the introduction of Storage Areas in Progress Version 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.x and PROMON has not matured enough to account for these. Using the _AreaStatus Virtual System Tables (VST) is a far better way of looking at database statistics.
CAUSE:
Bug# 20010712-020
FIX:
This issue has been addressed in Progress 9.1D.
The normal promon -> 7. Database Status option will display a valid highwater mark, which may be used to determine whether additional extents are needed.
However, if checking extent size versus disk capacity, the promon R&D -> 1. Status Displays -> 5. Files should be used to check the allocated and empty/free space on a per file basis because areas may exist on different disks.
Prior to Progress 9.1D and post Progress 8.3E, the formula to determine the overall Database high-water mark would be:
Overall High-water Mark = SUM(highwater marks for each storage area excluding 1 and 3)
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.
Total Empty Blocks * Database Block Size (bytes) = physical remaining (bytes)
Comparing this to the O/S size of your database (by conversion of each to the same byte order of magnitude) and the remaining space left on your system aides forecast planning exercises and prevents databases terminating abnormally.
To calculate how much of the allocated blocks are used up and by inference, what the true size of the Database is to monitor growth? iow: What is the real occupation? An example is given below:
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 sa.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. */