Feedback
Did this article resolve your question/issue?

   

Article

How to use an After Image file to find the user running a long transaction

Information

 
TitleHow to use an After Image file to find the user running a long transaction
URL NameP140697
Article Number000139217
EnvironmentProduct: Progress OpenEdge
Version: All supported versions
OS: All supported platforms
Question/Problem Description
How to use an (AI) After Image file to find a long running transaction that may not have completed?
How to scan an ai file to identify transaction duration?
Can an ai file be used to identify which user was running a long standing transaction that may have caused the bi file to grow?
Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution
The RFUTIL command with the aimage scan verbose Option can be used to view the transaction content recorded in the binary AI file. For further information on how to extract this output refer to Article
The ai file records transaction notes, it does not contain any information associated with what application programs were running at the time. It is not possible to extract actual data from the AI files alone. The AIMAGE SCAN options displays metadata about the changes not the actual data changes. A backup with subsequent AI files in the correct sequence are needed in order to roll the database forward to a point in time. Only then can this data be retrieved from the database; not the directly from the AI files. 
The ai file, in unison with client logging may help to give better insight from an ABL perspective. For further information on how to configure client logging refer to Article

To review the contents of an ai file to find a long running transaction:

Reviewing the contents of an ai file contains an abundance of information including:
The transaction number, the username, when a transaction was started and ended, and what DBkeys in which Storage Area were modified within a transaction. 
 
The following is an example of an ai file where there is an active transaction is still in process.  
There is no transaction end note for the 1st transaction which will have locked the bi cluster chain. This active transaction could be resulting in excessive bi growth especially when it spans ai files.
    
$   prodb dummy empty
$   echo a . > addai.st
$   prostrct add dummy addai.st
$   rfutil dummy -C aimage truncate -aiblocksize 16384
$   rfutil dummy -C aimage scan verbose -a dbname.a1 > aioutput.txt

 
After-image dates for this after-image file: (1633)
    Last AIMAGE BEGIN Tue Oct 01 11:46:52 2020 (1640)
    This is aimage file number 1 since the last AIMAGE BEGIN. (1642)
    This file was last opened for output on Tue Oct 10 11:46:56 2009. (1643)

Trid: 0 code = RL_INMEM version = 3 (12528)
Trid: 0  dbkey = 0  update counter = 0 (12530)
Trid: 0 code = RL_LSTMOD version = 2 (12528)
Trid: 0 area = 6   dbkey = 32   update counter = 107 (12529)
Trid: 1481 Tue Oct 10 11:47:44 2009. (2598)                                # When the 1st transaction was started [ 1481 ]
Trid: 1481 User Id: root (12531)                                                   # The user that started the 1st transaction [ 1481 ]
Trid: 1481 code = RL_TBGN version = 1 (12528)                            # The beginning of the 1st transaction [ 1481 ]
Trid: 1481  dbkey = 0  update counter = 0 (12530)
Trid: 1481 code = RL_SEINC version = 1 (12528)
Trid: 1481 area = 6   dbkey = 96   update counter = 40 (12529)
Trid: 1481 code = RL_TMSAVE version = 2 (12528)
Trid: 1481  dbkey = 0  update counter = 0 (12530)
Trid: 1481 code = RL_RMCR version = 2 (12528)
Trid: 1481 area = 9   dbkey = 1344   update counter = 22 (12529)
Trid: 1481 code = RL_CXINS version = 2 (12528)
Trid: 1481 area = 10   dbkey = 640   update counter = 220 (12529)
Trid: 1481 code = RL_IXDEL version = 2 (12528)
Trid: 1481  dbkey = 0  update counter = 0 (12530)
Trid: 1481 code = RL_BKREPL version = 1 (12528)
Trid: 1481 area = 10   dbkey = 640   update counter = 221 (12529)
Trid: 1481 code = RL_CXINS version = 2 (12528)
Trid: 1481 area = 10   dbkey = 640   update counter = 222 (12529)
Trid: 1481 code = RL_CXINS version = 2 (12528)
Trid: 1481 area = 10   dbkey = 544   update counter = 52 (12529)
Trid: 1481 code = RL_CXINS version = 2 (12528)
Trid: 1481 area = 10   dbkey = 224   update counter = 1120 (12529)
Trid: 1482 Tue Oct 10 11:47:50 2009. (2598)                                   # When the 2nd transaction was started [ 1482 ]
Trid: 1482 User Id: endofday (12531)                                              # The user that started the 2nd transaction [ 1482 ]
Trid: 1482 code = RL_TBGN version = 1 (12528)                              # Beginning of the 2nd transaction [ 1482 ]
Trid: 1482  dbkey = 0  update counter = 0 (12530)
Trid: 1482 code = RL_TMSAVE version = 2 (12528)
Trid: 1482  dbkey = 0  update counter = 0 (12530)
Trid: 1482 code = RL_CXREM version = 2 (12528)
Trid: 1482 area = 10   dbkey = 256   update counter = 224 (12529)
Trid: 1482 code = RL_CXINS version = 2 (12528)
Trid: 1482 area = 10   dbkey = 512   update counter = 29 (12529)
Trid: 1482 code = RL_RMCHG version = 2 (12528)
Trid: 1482 area = 9   dbkey = 96   update counter = 29 (12529)
Trid: 1482 Tue Oct 10 11:47:50 2009. (2598)                                  # The 2nd transaction was ended [ 1482 ]
Trid: 1482 code = RL_TEND version = 1 (12528)                              # The end of the 2nd transaction [ 1482 ]
Trid: 1482  dbkey = 0  update counter = 0 (12530)

18 notes were processed. (1634)
0 in-flight transactions. (3785)
2 transactions were started. (1635)  # [ 1481 ] & [ 1482 ]
1 transactions were completed. (11138) # [ 1482 ]
At the end of the .ai file, 1 transactions were still active. (1636)  # Number of active transactions at the end of this AI file [ 1481 ] 

 
Parsing the Transaction Identifier [TRID] begin [ RL_TBGN ] and end [ RL_TEND ] notes from aimage scan verbose output helps to track a transaction's activity and the transaction duration, across ai files. Once transaction identifiers of interest have been isolated, the user running the transaction and the specific transaction operations they carried out can be further analysed. Bear in mind that the Transaction id is reused after a transaction ends.
$   cat aioutput.txt  | egrep "RL_TBGN|RL_TEND"

Where:
  • RL_TEND = Logical and Physical transaction end  
  • RL_TBGN = Logical and Physical transaction begin
AISCAN VERBOSE information can be further analysed by sorting by the transaction number and outputting unique transaction numbers
 
cat aioutput.txt  | egrep "RL_TBGN|RL_TEND" | awk '{ print $2 }' | sort | uniq -u
  • A transaction should have a begin(RL_TBGN) and an end(RL_TEND) pair in the same AI file or within a short period of time.
  • If a transaction does not have one of these in the same AI file, the command above will show it.
  • By running the same command against previous AI files in the sequence one can have a better understanding which AI file a transaction started. 
  • If a transaction was open throughout multiple AI files, the associated notes may have spanned through multiple BI clusters
  • Specially, if the timeframe is larger than what your environment expects a transaction to last. However this is not an exact science, it is one of many traces to check for.
As another example record create, deletes and updates can be parsed
cat aioutput.txt | grep "\<Trid: 1482\>" | egrep "RL_RMCR|RL_RMDEL|RL_RMCHG" | wc -l
   
Workaround
Notes
Last Modified Date11/20/2020 6:50 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.