Seeking Solution After Bi Alert

Hello Everyone,

I have few below mentioned quires regarding BI files. I have shared these after going through couple of articles/discussions.

· Is it possible to add bi extent online if last bi extent is variable or fixed length without -bithold –bistall startup parameter? Or can we do it when -bithold –bistall are defined.

· What is the limit of variable BI extent when large files are enable, is it 5GB or complete drive space where bi extent is mounted.

· Our database has -Bithold 5GB and -Bistall defined and on reaching of its 90% we got BI alert. After that we increase bi threshold value to 6GB from 5GB (via proquiet). I want to know that after updating this threshold value, what all can we do as a solution of this bi increase so that our database shouldn’t shutdown.

Kindly suggest!!!

Regards
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
What is the limit of variable BI extent when large files are enable, is it 5GB or complete drive space where bi extent is mounted.
Large variable extents can grow to 1 TB. But in practical terms you never want any extent, especially BI, to get anywhere near that large.

Is it possible to add bi extent online if last bi extent is variable or fixed length without -bithold –bistall startup parameter? Or can we do it when -bithold –bistall are defined.
You can add a BI extent online via the prostrct addonline command, assuming your (unstated) release permits that. If your last extent is variable, adding another extent converts it to fixed at its present size. As -bithold and -bistall are startup parameters, they are defined where you configure your database startup, e.g. in a .pf file, a shell script, or in conmgr.properties (OEE/OEM). Setting them is unrelated to extent management.

I want to know that after updating this threshold value, what all can we do as a solution of this bi increase so that our database shouldn’t shutdown.
In simple terms, a cluster is added to the BI cluster ring when all clusters contain BI notes and the oldest cluster still contains notes for an active transaction. So in a busy system a client with a long-running transaction can be responsible for BI growth, even if that client themselves is responsible for very few BI writes. Check promon R&D|1|4|3 (active transactions) or ProTop for the clients with the longest-running transactions. The likely root cause is an application design problem; a poorly-scoped transaction, or a transaction that includes blocking statements like UI interactions.

E.g. user begins transaction, makes a few changes (which are written to the current BI cluster), gets to a prompt, and then goes out for lunch, or for the day, or on vacation. Then everyone else keeps using the system. Even if they are running well-designed code, their changes eventually grow the BI file (logically, and perhaps also physically) because that first user's changes "pinned" what was then the current BI cluster and prevented it from being reused for new BI notes.
 

cj_brandt

Active Member
usually the large bi growth is caused by an older character based application that allows a user to start a transaction while sitting in a screen. The user leaves that transaction open for hours, causing the BI file to grow. Check the active transaction screen in promon - R&D -> 1 -> 4 -> 3

we haven't used bithold and bistall since version 8 when the bi file couldn't grow beyond 2gb. Instead we monitor the transaction times.
 
Thanks a lot @Rob Fitzpatrick, @cj_brandt for your valuable inputs!!!


We have couple of entries in PROMON R&D|1|4|3 option like:


Code:
User Name    Type        Login          Time     Tx start      time              Trans ID           State

41          Self/ABL     12/22/16      05:08    12/22/16       06:46              1931657613       Active

43          Self/ABL     12/22/16      05:08    --                                1931657613       Begin


Identification of long running transaction (from above entries) will either depend on login date or on Tx start date, what is the difference between Active and Begin state of transaction. Is this above mentioned user name is same as user number that we use to disconnect a user via “proshut <db-name> -C disconnect <user-num>” command. Is disconnecting victim user from the database will be the appropriate solution of this problem?


Kindly suggest.


Regards
 
Last edited:

Rob Fitzpatrick

ProgressTalk.com Sponsor
A transaction in "Begin" state won't cause BI growth. Your issue in this case is user 41. As long as that user's transaction continues, database changes will eventually cause BI growth, if they aren't already.
 
Proshut <db-name> -C disconnect 41, is it the right way solving this problem or we need to do something else?

Can we reach to the victim program (.p) that creates long transaction?

Kindly suggest.

Regards
 
Last edited:
Thanks @TheMadDBA, shared articles were useful to me.

We can contact to the user but contacting only via user-number (user information so far) is not possible. Can we get user’s system IP or any other unique user’s identification so that we can contact the user directly.

Please suggest.

Regards
 

TheMadDBA

Active Member
Depending on your version and how the clients connect there are several ways to track down a user.

I would suggest looking at the _Connect VST (Virtual System Table)... since it looks like you are doing self service local connections you can just get the PID and find out which user is associated with that PID. Assuming you have distinct Unix logins for the users.

It will also have information for remote connections (IP address or host name).
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
If you authenticate via _User records, there may be additional identifying information there, e.g. user's full name, phone number, e-mail address, etc., if your application populates those fields. But as TheMadDBA said, probably PID and DB/OS user name is enough.
 
Top