Is there any way to see what table the transaction is against?”
16 January, 2010
Is there any way to see what table the transaction is against? How to relate the _Lock, _Trans and _File table to list the database transactions with the associated users and tables?
Facts:
All Supported Operating Systems Progress 9.x OpenEdge 10.x OpenEdge Category: Language (4GL/ABL)
Fixes:
The following procedure lists all active transaction Ids along with their associated user and table numbers and names. The procedure starts by scanning the _Lock table to populate a TEMP-TABLE is with only the _Lock records of interest. It then ties this TEMP-TABLE with the _Trans table to access the transaction IDs. Finally, it ties the TEMP-TABLE with the _File table to obtain the table name.
The TEMP-TABLE is used here to enhance the performance of the code and limit the processing of the _Lock table to those records that are of interest to us:
IMPORTANT: This procedure will not capture any transactions that do not involve locked tables. For example, the active transaction started by executing the SQL query: "SELECT PUB.NextCustNum.NEXTVAL FROM PUB.Customer" from an SQL client will not be captured by this 4GL/ABL procedure because there are no locked tables involved:
DEFINE TEMP-TABLE ttLock
FIELD LockId LIKE _Lock._Lock-Id
FIELD LockUsr LIKE _Lock._Lock-Usr
FIELD LockName LIKE _Lock._Lock-Name
FIELD LockTable LIKE _Lock._Lock-Table
INDEX LockIdx IS PRIMARY UNIQUE LockId.
FOR EACH _Lock NO-LOCK:
IF _Lock._Lock-Table = ? OR _Lock._Lock-Usr = ? THEN LEAVE.
CREATE ttLock.
ASSIGN
LockId = _Lock._Lock-Id
LockUsr = _Lock._Lock-Usr
LockName = _Lock._Lock-Name
LockTable = _Lock._Lock-Table.
END.
FOR EACH ttlock:
FIND _Trans NO-LOCK WHERE _Trans._Trans-Usrnum = ttLock.LockUsr NO-ERROR.
IF AVAILABLE _Trans THEN DO:
FIND _File NO-LOCK WHERE _File-Number = ttLock.LockTable.
IF AVAILABLE _File THEN
MESSAGE
"Transaction Id:~t" _Trans._Trans-Id "~n"
"User Number:~t" ttLock.LockUsr "~n"
"User Name~t" ttLock.LockName "~n"
"Table Number:~t" ttLock.LockTable "~n"
"Table Name:~t" _File-Name
VIEW-AS ALERT-BOX INFO BUTTONS OK.
END.
END.
Facts:
All Supported Operating Systems Progress 9.x OpenEdge 10.x OpenEdge Category: Language (4GL/ABL)
Fixes:
The following procedure lists all active transaction Ids along with their associated user and table numbers and names. The procedure starts by scanning the _Lock table to populate a TEMP-TABLE is with only the _Lock records of interest. It then ties this TEMP-TABLE with the _Trans table to access the transaction IDs. Finally, it ties the TEMP-TABLE with the _File table to obtain the table name.
The TEMP-TABLE is used here to enhance the performance of the code and limit the processing of the _Lock table to those records that are of interest to us:
IMPORTANT: This procedure will not capture any transactions that do not involve locked tables. For example, the active transaction started by executing the SQL query: "SELECT PUB.NextCustNum.NEXTVAL FROM PUB.Customer" from an SQL client will not be captured by this 4GL/ABL procedure because there are no locked tables involved:
DEFINE TEMP-TABLE ttLock
FIELD LockId LIKE _Lock._Lock-Id
FIELD LockUsr LIKE _Lock._Lock-Usr
FIELD LockName LIKE _Lock._Lock-Name
FIELD LockTable LIKE _Lock._Lock-Table
INDEX LockIdx IS PRIMARY UNIQUE LockId.
FOR EACH _Lock NO-LOCK:
IF _Lock._Lock-Table = ? OR _Lock._Lock-Usr = ? THEN LEAVE.
CREATE ttLock.
ASSIGN
LockId = _Lock._Lock-Id
LockUsr = _Lock._Lock-Usr
LockName = _Lock._Lock-Name
LockTable = _Lock._Lock-Table.
END.
FOR EACH ttlock:
FIND _Trans NO-LOCK WHERE _Trans._Trans-Usrnum = ttLock.LockUsr NO-ERROR.
IF AVAILABLE _Trans THEN DO:
FIND _File NO-LOCK WHERE _File-Number = ttLock.LockTable.
IF AVAILABLE _File THEN
MESSAGE
"Transaction Id:~t" _Trans._Trans-Id "~n"
"User Number:~t" ttLock.LockUsr "~n"
"User Name~t" ttLock.LockName "~n"
"Table Number:~t" ttLock.LockTable "~n"
"Table Name:~t" _File-Name
VIEW-AS ALERT-BOX INFO BUTTONS OK.
END.
END.