Active Transaction

What makes you think promon is incorrect?

Promon VS ABL

My ABL program does not match the number of active transactions in Promon ( I know it is a moving target), however, should be close.

Snapshot

Promon = 111 active trans

ABL        = 13 active trans

here is my code.

FOR EACH _trans WHERE _Trans-State EQ "ACTIVE" BY _Trans._Trans-Duration

DESCENDING:

 cnt = cnt + 1.

 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-State

      _Trans._Trans-Usrnum

      _Trans._Trans-Txtime FORMAT "X(40)" WITH WIDTh 132 .

END.

DISPLAY cnt.

"Active trans" in promon/Activity: Summary means the number of transactions also reported by promon/Status: Active Transactions. "Trans State" on last screen can be of any type. These promon's screens were created when transactions had only one state: "active".

> When is it a active transaction? when the trans has a start time?

Will a transaction in the BEGIN or ALLOCATED state cause bi growth?
http://knowledgebase.progress.com/articles/Article/P98099

Why does the client hold the Transaction State 'Begin' for more than a minute?
http://knowledgebase.progress.com/articles/Article/P17119

Explanation of the Transaction State values of Begin, Active, or Phase 2 as seen under promon:R&D, #1 Status, #4 Processes/Clients, #3 Active Transactions
http://knowledgebase.progress.com/articles/Article/P65988

Thanks, that helps!  I had a user today sitting in an update field, doing  nothing, however, it had a start time and my bi is now over 5GB.  

there are three possible transaction start situations (v9 and later):

0) when the transaction starts in the (4GL) client. But here it is delayed as long as possible so the database is not told about it right away even though the application may be executing inside a "do transaction" block. the datbase does not know the clinet wants a transaction, or the transaction may be started in one connected database and not in another. promon does notshow a transaction for the client.

1) the 4GL has told the database it wants to start a transaction but the transaction start is being postponed until the clinet performs a database operation that makes a change to the database. the database knows about the transaction and is lying to the client about it. promon show a transactionstate of "Begin" and is lying to you.

2) the transaction bein note has been written o the transaction log in the database. promon shows the transaction as "Active".

of the three, the only one that matters is the last.

show us the data

> I had a user today sitting in an update field, doing  nothing, however, it had a start time and my bi is now over 5GB.

If you have AI files then you can find all details about the user's transaction.

> sitting in an update field, doing  nothing

Example:

CREATE Customer.

UPDATE Customer.Name.

This code create an active transaction but only because of the CREATE trigger defined for the "Customer" table. The trigger hiddenly updates the "cust-num" field.

yes AI is running, I know what the user was doing, I double vision into the user screen -  the user login at 6:12 AM and sat in a update field, and at 2:20 PM, I F4 the user out of the screen and the BI stop growing.  Poor application, should have a timeout feature.  I monitor loooong running trans - the BI threshold is 12 GB and I have BISTALL.  It just wild that one user can cause this kind of BI growth.  

Thanks!!!

> Poor application, should have a timeout feature.

Many applications uses a service that disconnects the sessions with long open transactions. It's simple.

yeah, I was thinking about adding a script or ABL code to disconnect users running transaction longer then 90 minutes.  

I would recommend for a "transaction watchdog" to start couting the table's/index' updates/creates/deletes for /any/ sessions that have the active transactions - no matter how long are their transactions. So when a transaction will stay opened for a long time you will approximately know how large it is and does the session still activily update a database (in other words if the total number of UCD is still growing).

Good point.  I will do that.  Most of the time a user is just sitting in a line item in Order entry, or non-menu screen.

Thanks for the suggestions!

> Most of the time a user is just sitting in a line item in Order entry, or non-menu screen.

Even the batch processes that create the large transactions used be idle 90% of transaction time. At least it's what I saw in AI scans that the customers of ours sent me. Most likely they spend the time just for reading data.

A bit off-topic:

> FIND FIRST _Connect WHERE _Connect._Connect-Usr = _Trans._Trans-Usrnum.

The faster:

FIND FIRST _Connect WHERE _Connect._Connect-Id = _Trans._Trans-Usrnum + 1.

Original code will read all _Connect records until it finds a record you need.

The code with _Connect-Id will read only one record.

The similar tricks are especially important when you deal with _UserTableStat/_UserIndexStat.

BTW, it would be a good idea to accumulate the tricks for reading VSTs in one document. I thought about making a presentation but the subject seems to be too small for it. A thread on Community will be lost in the time but it would allow to collect the ideas from the different authors.What do you think?

off the topic a little bit.  it appears to me that the database helper AIW writes to the BI file.

my production database.

usr    pid    time of login           user id     tty                  Limbo?

491  14601 Mon Apr 11 23:51:00 2016   BIW         batch                no

493  14609 Mon Apr 11 23:51:00 2016   AIW         batch                no

ABL program output

       Usr Name                    BI Write

         0 root                       23386

       491 root                      986297

       493 root                      846009

> it appears to me that the database helper AIW writes to the BI file.

You're correct and it's OK.