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
A code example is provided in Artice
ABL Code to search rfutil aimage scan verbose output to see who modified records within a specific block(s) within an area.
