Status: Verified
GOAL:
Suggestions for investigating unexpected bi growth
GOAL:
Investigating why bi files are reaching the 2 GB limit
GOAL:
How to narrow down the cause of why a bi file is growing exponentially large ?
GOAL:
How do I find out what client process is causing the BI file to grow unexpectedly?
FACT(s) (Environment):
Progress 8.2x
Progress 8.3x
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
FIX:
After recovering the database from a situation where the 2GB file size limit was breached, the next actions that should be taken are preventative and corrective.
Transaction backout are achieved by reading the Before-image (BI) file - a BI file that is created by transactions during run-time. What causes the .bi to reach the 2GB limit can be manyfold, but typically once reaching System/ Progress limits, as reported by a 9452 error message for example, where the next bi note would take the clusters over the 2GB limit, it is due to long running transactions.
Corrective actions are directed towards finding the transaction(s) that cause the .bi growth and addressing these. There are also parameters -bistall and -bithold which could be considered, that will alert an administrator in future and prevent this happening again.
In case of excessive Bi growth, a promon investigation for example will assist in detecting long transaction duration:
start: promon dbname -> R&D -> 1(Status display -> 4(Processes/client) -> 3(Active transaction).
A list of users with open active transactions will be displayed, pay special attention to client's start times. A transaction time of over 10 minutes would be considered unusual and should be investigated by contacting those users and finding out what they're doing. It may be that some piece of code will be revealed as poorly scoped for transactions. It is this poorly scoped transaction that is locking clusters of the bi preventing them from being re-used.
This can also be checked by interrogating the _Trans VST table.
example: the transaction at the top of the list will be the longest running one
FOR EACH _trans WHERE _Trans-State <> ? BY _Trans._Trans-Duration DESCENDING:
FIND FIRST _Connect WHERE _Connect._Connect-Usr = _Trans._Trans-Usrnum.
DISP STRING(TIME,"HH:MM:SS") LABEL "TIME NOW"
_Connect._Connect-Name
_Connect._Connect-PID FORMAT 99999
_Trans._Trans-Usrnum
_Trans._Trans-Txtime FORMAT "X(40)".
END.
Alternatively, each transaction that has been running longer than x-time:
DEFINE VARIABLE iTimeInSeconds AS INTEGER NO-UNDO INITIAL 120.
FOR EACH _Trans WHERE _Trans._Trans-State EQ "Active" AND
_Trans._Trans-Duration GT iTimeInSeconds NO-LOCK:
FIND FIRST _Connect WHERE _Connect._Connect-Usr = _Trans._Trans-UsrNum NO-LOCK NO-ERROR.
DISPLAY _Trans-Num _Trans-UsrNum _Trans-Duration _Connect._Connect-Name.
END.
So from the VST _Trans together with the _Connect we can determine the transaction number that has been long running. The only way to match that transaction to the application code is to find out from the user what they were running at the time.
Again, as it is not necessarily "a single procedure" that needs identifying, it is one or more transaction(s) that are causing the .bi file to grow. When we find the code that is being run/responsible, we need to address the transaction scope in that code. The COMPILE with "Listing File" helps to identify where the transaction blocks start and end. So another starting point would be to compile the Application code in the Application Compiler with the listing file:
Select OPTIONS -> compiler -> "Listing File" = (say) "listing.txt" + check "append".
From the output of this action, a map of the code will be created. By analysing this map, where the blocks start and end will be apparent and from this detail investigation of the transaction scoping of the applicat.ion. Any large transactions that are identified are likely candidates.
Consider implementing the "use statistics with cross reference (-yx)" startup parameter for a period of time which will give a fair idea of where the busiest code is in terms of long execution times and large number of calls (which imply the necessity of revisiting code, considering internal procedures and include files), re-reads (where increasing the -mmax client memory could help).
The 2GB limit applies to the SUM of bi extents in Progress 8.x, more extents can be added to split the bi extents across discs and to improve performance for example, but it will not help in overcoming this limit in Progress 8.x. Where in Progress 9.x, the 2Gb limit applies to individual extents when enablelargefiles has not been implemented (introduced in 9.1C for specific platforms).
If you are in a situation where you are currently observing unexpected BI growth, you can also look at the I/O Operations by Process screen in promon:
start: promon dbname -> R&D -> 3. Other Displays -> 2. I/O Operations by Process.
This screen will list out the users currently connected to the database and the number of reads and writes to the
database, BI and AI files for each user.
By entering 'R' at the "Enter <return>, R, U, P, T, or X (? for help): " prompt, the values will be refreshed,
and by repeatedly refreshing the screen, you should be able to see which users have larger than
expected growth in the number of writes to the BI file(s).
This information can also be obtained by querying the _UserIO VST table.
For example:
for each _UserIO where _UserIO-usr <> ?:
display _UserIO-Usr _UserIO-Name _UserIO-BiWrite.
end.
If you are able to identify a 4GL client process that is causing more BI writes than expected, and if you are running OpenEdge 10.1C or later,
it can be possible to find out what 4GL program(s) that client process is currently running by executing the proGetStack command on the same
machine where the client process is running:
proGetStack <pid>
This information can also be obtained on the database server by using the Client Database-Request Statement Cache option in promon.
start: promon dbname -> R&D -> 1. Status Displays -> 18 Client Database-Request Statement Cache .