The following 4GL/ABL code monitors the growth of the Progress OpenEdge database BI file, database and transactions that have been open for one-half hour or more using Virtual System Tables (VST’s).
If running Progress 8.x, VST’s need to be explicitly enabled offline as follows:
$ proutil dbname -C enablevst
VST’s are enabled on Progress 9.x, OpenEdge 10.x and later databases by default, however they can be updated in Service Packs so it’s advisable to update these whenever a Service Pack is applied, or an earlier minor version is updated to a later minor version: proutil dbname -C updatevst
If migrating an existing database to Release 11.5.0, you must enable both PROUTIL ENABLENEWVSTTABLES and PROUTIL UPDATEVST on your database to activate these changes.
The VST’s used in the code example below however have not changed since Progress 8.
/* This program is provided as-is, with no implied warranty.
It will not be supported by Progress Software Corporation,
and the user assumes all risks of its use.
Program to monitor BI and database growth, and
look for transactions older than 1/2 hours.
*/
DEFINE VARIABLE sample_time AS INTEGER INIT 300.
DEFINE VARIABLE tbisize AS INTEGER INIT 0.
DEFINE VARIABLE tdbsize AS INTEGER INIT 0.
DEFINE VARIABLE tbiinc AS CHAR INIT "".
DEFINE VARIABLE tdbinc AS CHAR INIT "".
DEFINE VARIABLE areainc AS CHAR INIT "".
DEFINE VARIABLE areahwinc AS CHAR INIT "".
DEFINE VARIABLE numa AS INTEGER INIT 0.
DEFINE TEMP-TABLE areasize
FIELD areanum AS INTEGER
FIELD hiwater AS INTEGER init 0
FIELD total AS INTEGER init 0.
FOR EACH _areastatus:
numa = numa + 1.
CREATE areasize.
areanum = _areastatus-areanum.
END.
DEFINE FRAME area WITH numa DOWN.
UPDATE "Enter Sample Time: " sample_time WITH FRAME a.
REPEAT:
FIND FIRST _dbstatus.
DISPLAY STRING(today).
DISPLAY STRING(time, "HH:MM:SS").
/* the BI file grew */
IF tbisize <> _dbstatus-bisize THEN
tbiinc = STRING(_dbstatus-bisize - tbisize).
ELSE tbiinc = "".
tbisize = _dbstatus-bisize.
DISPLAY
tbisize LABEL "Bi #blocks"
tbiinc LABEL "(increase)"
_dbstatus-bisize LABEL "Used #blocks"
( tbisize * INTEGER( _dbstatus-biblksize / 1024) )
FORMAT ">>>,>>>,>>>,>>9" LABEL "Total size (Kb)"
WITH FRAME bi.
/* the database grew */
IF tdbsize <> _dbstatus-totalblks THEN
tdbinc = STRING(_dbstatus-totalblks - tdbsize).
ELSE tdbinc = "".
tdbsize = _dbstatus-totalblks.
DISPLAY
tdbsize LABEL "Db #blocks"
tdbinc LABEL "(increase)"
( tdbsize * INTEGER( _dbstatus-dbblksize / 1024) )
FORMAT ">>>,>>>,>>>,>>9" LABEL "Total size (Kb)"
_dbstatus-freeblks LABEL "Free #blocks"
_dbstatus-emptyblks LABEL "Empty #blocks"
WITH FRAME db.
/* the areas grew */
FOR EACH _areastatus:
find areasize where areanum = _areastatus-areanum.
IF hiwater <> _areastatus-hiwater THEN
areahwinc = STRING(_areastatus-hiwater - hiwater).
ELSE areahwinc = "".
hiwater = _areastatus-hiwater.
IF total <> _areastatus-totblocks THEN
areainc = STRING(_areastatus-totblocks - total).
ELSE areainc = "".
total = _areastatus-totblocks.
DISPLAY
_areastatus-areanum FORMAT ">>9" LABEL "Area"
_areastatus-areaname LABEL "Name"
total LABEL "#blocks"
areainc LABEL "(increase)"
hiwater LABEL "last used block"
areahwinc LABEL "(increase)"
( total * INTEGER( _dbstatus-dbblksize / 1024) )
FORMAT ">>>,>>>,>>>,>>9" LABEL "Total size (Kb)"
WITH FRAME area down.
END.
/* find all open transactions that have been open for 1/2 hour */
FOR EACH _trans where _trans-duration > 1800:
FIND FIRST _connect where _connect-usr = _trans-usrnum.
DISPLAY _connect-name _connect-usr _trans-duration
_trans-txtime WITH FRAME e.
END.
pause sample_time.
END.
